My Adventures in sql dumping and database migration

My latest problem project presented itself as a phpbb conversion. The client was having some issues with the old versions of phpbb so we elected to move them to the new version. While this is a great idea on the surface I did not realize how big the database was. I started out doing an export using phpMyAdmin not knowing the file sizes I was dealing with. When that failed I decided to do a mysqldump from the shell. Imagine the look of shock and dumbfoundedness on my face when I finished the dump to discover a 300 meg + sql file.

Why is this a problem? Well most ISPs have upload limits usually under 20meg for import uploads using phpMyAdmin. My default install of Xampp limit is 16 meg. I decided to try to gzip the file with a 100meg + result. At least now I could download the file to my local machine to try to figure out what to do.

First Trial PhpMyAdmin Local File upload

I edited my php.ini files and change the file_upload, and max_execution time to try and enable a way to upload the file.

max_execution_time = 560
max_input_time = 160
memory_limit = 60M
upload_max_filesize = 160M
 

After an hour of battling the settings to try and get one that would work, I had to read some phpMyadmin documentation and see if I could find a solution.

PhpMyAdmin Config Change
My first config setting change was a nice config setting called $cfg['UploadDir']. With this option set to a local server folder, you can select a direct data upload from a local drive. When you open phpMyAdmin it will give you a list of files to select from in the folder. You can then select the file and it will perform a direct import of the larger than limit sql file. However, this still didn't solve the timeout problems with a 100meg sql file. I had to look further.

SQL file splitter

After kicking around some work a rounds, I realized the only way to beat this, was to split the file into parts. I can either do this by hand, or find a solution.

Google is a wonderful tool for finding things and it didn't let me down this time either.

I found a blog the outlined exactly what I was looking for: Sql file splitter From reviewing his blog It appeared he had the exact same issues I had. The solution was a program called SQLDumpSplitter 2, by Philip Lehmann-Böhm (http://www.philiplb.de). The main links on the blog no longer worked, but a dutch website had left a blog comment with a direct link to the file. You can download from here: Sqldumpsplitter2 Hopefully that file link will stick around for a while.

I split the 300 meg file into 23 16-meg chunks. This worked very well, I was able to import the large 300 meg file, but it still took baby sitting and remembering what the last file I used was. In fact I spent several hours importing and then still experienced some time out issues.

I then decided to see if I could find another solution, so it was back to google.

php mysql large file importer

was the keyword search I did. What I found was this blog: How to import large mysql data files. This WAS the solution to all my problems!!! In this blog, there was a link to a site and a program called Bigdump.

Bigdump is a staggered mysql dump importer. What this means, is you start the file, and it will automatically import the large file till it is complete. That means, no file splitting. It is truly an amazing piece of 10K of code.

To import a large file you perform the following steps:

1 - Copy bigdump.php to the website where you want to import the file.
2 - If the file size is larger that the upload limit of the server, copy the
    file to the same foldere.
3 - Edit the bigdump.php file, set the database name, the username and password.
4 - If the file size is larger than the limit, enter the filename into the
    "filename" field.
5 - Execute the bigdump file by going to http://yourdomainlocation/bigdump.com
6 - Wait for the import to complete and be prepared for the shock and awe.
 

For my test I used the uncompressed 300 meg sql file. The documentation states, you can use the gzipped version also. It took approx 15 minutes or less, to import that file with the bigdump program. I still am amazed. Had I found this earlier today, this blog may not actually exist.

Hurrah for Bigdump!!!! I gladly sent a paypal donation for this code! It saved my life.

I hope that the users that are looking for a sql splitter will find this blog and save themselves the initial headaches I had.

WOOT!! I got a new phone! Only reason I did, was I broke my Treo 650 a second time. Same issue, I managed to short out the headphone jack. I don't know why they bother to put them on, if they don't give them more industrial strength. I checked with AT&T online and found I was eligible for a new phone.

Smart Phone Choices have grown with leap and bounds. The top ones are Iphone, Blackberry, Treo just to name a few. I really thought I might wait for a cheaper version of the Iphone, but I've been a Palm person for years, that and right now the Iphone is about $200 more than I wanted to spend.

After a little research I decided to go with the Palm Centro, most of the reviews compared it to the Treo 680, and since I was just leaving a 650 it sounded like the most economical choice. The Phone with a new contract is $199, but you can get a $100 rebate if you get it with a data plan. At $99 its gotta be one of the cheapest choices of smart phones on the market.

The Palm Centro is by far the smallest smart phone that I have seen. Its an amazing difference in size to my Treo 650. As you can see in the picture. It reminds me of my first small nokia phone, I can more easily put it in a shirt or pants pocket where with the treo, it was just very bulky.

One of the selling points for me was it has an optional micro SD port. Now I can move all the data, and media from my old phones SD fairly easily to the new phone. The pricing of SD and micro-SD has dropped significatly from when I bought my last 1gig. I had paid near a hundred dollars about 2 years ago. I found a 4gig micro SD for $27 shipped this more than compensated for the fact I had to buy a micro instead of a regular SD ram. I love ever changing technology. Makes my toys MUCH cheaper.

The Keyboard
While the keys are closer together, they actually still very easy to get to, either that or I have just gotten better at typing on small keyboards. One cool difference is they added a menu button. On the treo there wasn't one, and it was rather annoying when the app wouldn't let you easily get to the menus. I can quickly type and get to menu functions even one-handed.

Minor Issues
One of the few issues I have had is I could not get the SMS messages to work with my Agendus Mail. Not a real big deal, just couldn't figure out where my SMS messages were going when I received them. Once I removed and reinstalled Agendus Mail without the SMS option it fixed the problem.

The Camera
The camera has been upgraded to a 1.3 MP camera, pictures are fantastic compared to the Treo 640.

Data Package
Due to the discount requirements I had to buy the data package. My issues with it right now is I have a hard time paying $30/month for unlimited internet when its as slow as it is. Hopefully the speed will be taken care of over time, but when your spoiled with 10 MB Cable speeds, its hard to deal with at times. The Web browser works fairly well, I can use most of the google mobile web applications with very little problems. The built in IM software is currently working off and on I am unsure if thats due to the network or the software.

Overall I absolutely love the phone. It is light weight, small and fits in my pocket easily. Works better than my treo 650. Half the time, no one even knows its a smart phone. In the world where some think bigger is better this small phone is incredible for the price.

Dealing with date based data can be a mind numbing experience. Calculation date differences, doing date comparisons can really stretch you to the limits at times. There is a variety of ways to deal with date based data but the first step usually is to convert it into a standard format that you can easily deal with. That format is the unix timestamp.

The unix time stamp is a way to track time as a running total of seconds. This count starts at the Unix Epoch on January 1st, 1970. Therefore, the unix time stamp is merely the number of seconds between a particular date and the Unix Epoch.

Below is a code snippet for creating a timestamp from formatted date data. For this example the date_in variable contains a date in the format of YYYY-MM-DD and is passed from a previously executed form post.

$datearray=explode("-",$_POST['date_in']);
$year=$datearray[0];
$month=$datearray[1];
$day=$datearray[2];
$mytimestamp=mktime(0,0,0,$month,$day$year);

Recently I discovered an easier way to get a date converted to a timestamp. This just goes to show you that a programmer is always learning. There will always be functions that are overlooked and underused.

strtotime() function this function will take an english based date format and convert it to timestamp. The code below illustrates how much easier this is.

$mytimestamp=strtotime($_POST['date_in']);

One line of code! No exploding parts, just a quick and easy solution.

Calculate Number of days

Now that timestamps have been reviewed to show you some easy way to take two days and return a total number of days. The example below will take an arbitrary date of April 4, 2008 and May 12, 2008 (the current date I wrote the article)

$time_A = strtotime("April 4, 2008");
$time_B=strtotime(now);
$numdays=intval(($time_B-$time_A)/86400)+1;

The number of seconds in a day is 86400 and the calculations usually shorts you a day so thats why we add a 1. This is do the fact the now() returns the current time which is not a complete day.

Another example of using date comparisons is expiring accounts in a database. If you can let the database do the work for you, DO IT, as anytime you can save code calculations the easier it is on you. This code will take the current date and compare it to the user_expdate and delete those records that are less than or equal to 0.

 $current_date=date("Y-m-d G:i:s"); 
 
  $query="delete from {$db_prefix}users where user_expdate!='0000-00-00
 00:00:00' and datediff(user_expdate,'".$current_date."')<= 0 ";
  if (!$result=$db->execute($query)){
    $db->ErrorMsg();
  }

Date comparisons

Date comparisons are now simple a matter of comparing the timestamps. Given 2 timestamps you can compare them and know very simply which is greater. Note: in the example below we forced the today variable to midnight so only days before the two timestamps are selected.

$today=strtotime(date("Y-m-d")." 00:00:00");
$datecheck = strtotime($year."-".$month."-".$day);
if ($datecheck < $today){
     //find all dates before today and block them out
}

Date comparisons in mysql can be done similarly. However mysql give a variety of date tools to work with as you are calculating dates.

The following code snipplet shows a way to delete code based on a timestamp comparison. Note: The code uses ADODB Lite calls. The $db->qstr function add's the appropriate quote code.

$today=strtotime(date("Y-m-d")." 00:00:00");
 
 $query="delete from {$db_prefix}users where user_expdate!='0000-00-00
 00:00:00' and timestamp(user_expdate) < ".$db->qstr($today) ";
  if (!$result=$db->execute($query)){
    $db->ErrorMsg();
  }

While these are some basic ways to do this, there are many other ways. I always welcome comments, feel free to add your own two cents.

Just make sure you register as No Spammers are allowed.

I had just paid for several domains that I had planned to build websites on yet have not had time to do them. You know web entrepreneurs, they are always looking for that next great website.

While I am not one that has taken up the reigns of the domain name rush, I was around early enough that I could have grabbed just about anyone that I had wanted. It's too bad for me that I wasn't the greedy one or I might have got that million dollars I have always wanted. I have always said its better to be rich than famous.

Well anyways I decided to test domain name parking. I found that there are many places that you can park a domain. Some places require that you have 10 or more to park, others want you to pay a fee. I opted to use Name Drive because it was free, and the generated pages actually looked better than some parking sites.

The following domains are ones I have been paying on for years. I had intended to use them as I described them, I will see if parking can at least pay for the cost of ownership

maxhotels.com - Original plan was to create a site for people to book hotels based on the highest hotel ratings. It may not be that now, but at some point I hope I will have time to finish it
ozarksgolf.com - Local golf portal site. One place you could go to find local golf info
ozarksdining.com - Local Restaurant guide
ozarksmusic.com - Whats happening on the local music scene
sleepysearch.com - Another Hotel site
shoppingsearch.biz - Shopping portal

The true test will take a few months. The hard work begins by trying to get the domains to just show up in search engines. After that, its just a matter of time to see if this free parking works.

As time permits I may take one domain and populate it myself with google ads and see how well I can do on my own versus the free parking sites.

For those of you that think you want to try your hand at this, the first thing you will need to do is buy several domain names. Domain names can be bought all over the net, but do a poor programmer a favor and buy it from me at http://www.snethosting.com. While I don't make alot off of them, maybe I can buy myself lunch or something.

One of the main reasons I decided to have this blog is to create a reference place so I could easily find code or how to do things from time to time. Usually I have to sift through code trying to remember the last place I used something. From here on out I plan on posting code snippets as I run across them. Today I woke up to an email requesting if a signup form or member login form was opened, they wanted the cursor to automatically focus on the username or the first element of the form. To be honest, I didn't even know it could be done from a page open. So the first thing I did was open up Google and search until I find a way. I an not too ego minded to think I know everything. There is always someone that has found a better way.

Here is the JavaScript code:

Here is the XHTML part of the code:

 
<form>
<input id="element" />
<input />
<input />
</form>
 

For those that are a little new to javascript. The javascript should go in the document HEAD. The "element" can be changed to the id of the field you are working with.

There is one programing concept that always hurts my head, and that is the variable variables or dynamic variables. What dynamic let you do is access data from the value of the a variable you are viewing. (See I told you it would hurt your head)

 
$b =20;  // set the value of b
$c="b";  // set c to the name of the variable you want to view
$d=${$c}; // the variable variable  $d is now equal to 20
 

Many of you are probably asking yourself, " OK I understand that , but why do I need to do that?"
The number one place I use this, is in a web grid form. Any place you need to update/or delete data in that grid. Below is an example of what a grid of data may look like:

The issues you face in the above grid is how do I delete multiple items and change multiple elements of data in grid once it is submitted. The solution is you assign each field a row number and add that to the field name.

Example:

 
<input name="del_1" type="checkbox" />
<input name="id_1" type="hidden" value="117" />
<input name="del_2" type="checkbox" />
<input name="id_2" type="hidden" value="95" />

Note each field had its name attached with a _row number so you will see an id_1 as a field name. After this form is submitted you can use the below php code to get the values dynamically.


// Check for item delete
if ((isset($_POST['delete'])) or (isset($_POST['delete_x']))){
for ($i=0; $i<=$totrecs; $i++)
{
$tdel=$_POST['del_'.$i];
$tid=$_POST['id_'.$i];
// Another way to do this is this way
// $tempvar="id_".$i;
// $tid=$_POST[${$tempvar}];
if ($tdel=="on")
{
// Delete Item, the item ID is $tid
}
} // Loop
} // End Post Check


Template Lite And Dynamic Variables
Form Data grid is also the reason I used dynamic variables in templates. Once I have edited a data grid I need to set values in the grid, select drop downs and, check radio or check boxes. There is some extra steps you need to do to use them and it also depends on how you pass your data to template lite. The following is an example showing a more complex use of variable variables.

{foreach key=key2 value=item2 from=$jetarray }
{ assign var="dummyvar1" value="`$item2.jetski_id`_bookid"}
{ assign var="dummyvar2" value="`$item2.jetski_id`_owner"}
{ assign var="cellcolor" value="`$item2.jetski_id`_color"}
{ assign var="dummyvar3" value="`$item2.jetski_id`"}
 
 {if $item[$dummyvar1]!=""}
   {if $session.myid == $item[$dummyvar2]}
<input name="jetid_{$item2.jetski_id}_{$key}" type="hidden"
 value="{$item2.jetski_id}" />
   {/if}
{/if}
 
 {/foreach}

Note we assigned a variable $dummyvar1 with the value $item2.jetski_id and appended _bookid to that variable name. Jetski number 1 turns into 1_bookid. After that we pull the value out of the array $item[$dummyvar1] so what we are actually doing is viewing $item['1_bookid'].

While there are some other uses for dynamic variables these are how I most recently have used them. If it helps someone other than myself, cool. Otherwise this is a place I have my own personal reference library.

One of the greatest tools I have in my library set is Template Lite . Template lite is a light-weight version of another template engine known as Smarty. Once you start using template lite you will be pleasantly surprised by how powerful yet modular it is.

My whole point of this blog is to talk about modifiers. A modifier enables you to pass some php modifying code into a template lite variable.

Example:


{$mytlitevariable|ucfirst }
//This would capitalize the first letter of the variable.
What doesn't work

It is important to note that you cannot necessarily use this on all modify type php functions. Some php functions do not pass the main variable into the first option.

Using str_replace would not work as the passed variable is the third option in the command. {$mytlitevariable|str_replace:black:brown }
//is the equivalent to
str_replace($mytlitevariable,"black","brown")
//which will not work. You will have to use it via php:
{php} echo str_replace("black","brown",$mytlitevariable) {/php}

Modifiers Stacking

Modifier stacking allows you to make multiple changes to a variable by passing multiple modifiers to the template lite variable.

Example: {$myvar|ucfirst|truncate:40:"..."}

Creating your own Modifiers

Don't be afraid to create your own modifiers. When ever I run into something that I can't do I add it into template lite.
//Example: This is the source code for the indent modifier.
//Once you create the modifier and save it in the plugins folder with the name
//modifier.indent.php.
{$myvar|indent}
//Note there are 2 extra arguments you can use with it as well.

function tpl_modifier_indent($string,$chars=4,$char=" ")
{
return preg_replace('!^!m',str_repeat($char,$chars),$string);
}
This is the start of my experimentation with blogs. I am constantly forgetting how to do things with template lite and other things. I plan on adding these tips and tricks to my blog so I have one place to search for the info. Feel free to comment as I realize I don't know it all but am willing to share my experiences along the way.