processing mysql dump file 1

[richclever]

Hi,
I have a mysql dump file that I need to get php to process.

Anybody know how?

Thanks

Richard

 

[DRJ478]

A dump file from MySQL is nothing else than a text file with SQL instructions. If you want to reinsert the data into another MySQL server you should probably use the mysql command line client or any GUI based MySQL administration tools.

As for PHP:
You would have to:
1. Connect to the MySQL server
2. Open the dump file - fopen()
3. Read the dump file (careful for size. Don't want to read a huge file inot memory)
4. Extract each line and send it to the MySQL server as a query
Done.

 

[richclever]

I know that the command line would be best, but I need to run a cron job that inserts the data into a database and then a few other bits and pieces. I'm best with php rather than bash etc. so wanted to do it in PHP and call it from cron.

 

[DRJ478]

So, then follow the steps outlined in the second half of the post:

1. Connect to MySQL
2. read file line by line:

<?php
$handle = fopen("/tmp/mysqldump.sql", "r");
while (!feof($handle)) {
   $buffer = fgets($handle, 4096);
   # processing code here
}
fclose($handle);
?>

Complications:
You will need to eliminate comments, blank lines.
CREATE statements span over multiple lines.

Best bet: only process lines that start with INSERT.
Let me know if you need more concrete help.

P.S.
mysql -h localhost -u update -p xxxxxx < mysqldump.sql
That's all needed on the command line and could easily be put into a cron.

 

[richclever]

Thanks a million.

I will get to it straight away. Fortunately I only have insert statements in the sql.

I know that the command line stuff would be nice and easy, but like I said I need to do various other bits and pieces too so thought it'd be better all in one file.

Thanks again,

Richard

 

[sleipnir214]

richclever:
You seem to be missing a hybrid solution. Have crond run your PHP script, have that PHP script use its execution functions to invoke the mysql application to dump the data into your database. The PHP script can also do whatever else you need.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[richclever]

Interesting,

I got DRJ478's solution working really easily - Except in some of my sql statements there seems to be a Carriage return (looking at it in crimson editor it has a small square symbol).

Whilst if I execute the script file through php myadmin everything is fine, when I read the file in the php script it messes up when it finds the CR (or LR, whichever it is!) and misses the rows off.

Is there anyway to get rid of the CR/LR characters. I have tried all the string replacement functions I can think of but without succes.

Richard

 

[DRJ478]

The file reading should just read in lines until it encounters the semicolon followed by the \n at the end of the line.
That should capture multi-line statements.
Taking out the CR/LF would impact the contents of the data, so I would not remove them.

 

[richclever]

That's what I thought too, but for some reason it doesn't want to take the data on the lines with the CR/LF on them.

No idea why, but that's whats happening. It's not actually important to have the CR/LF's there (infact may be better no too) so I'm definitely looking for a way to strip them.

Richard

 

[DRJ478]

Replace all \n or \r:
str_replace(array("\n","\r"),'',$str);

Then you'd have to start reading the entire file in - which is ok, as long as it is not too large. You'd also have to come up with an alternate splitting method to separate the individual lines.

Could you elaborate on this (and maybe post some code/data):

it doesn't want to take the data on the lines with the CR/LF on them.

 

[richclever]

No problems,

I will have to post the data as an image on a site as it won't copy pate properly. I will label it properly for you .

Look at ictechinformatique.com/cedit.jpg for the pic.

The data will be input if I use phpmyadmin, but not from the script below.(I have removed connection details!)

<?php
$mysql=mysql_connect($abserver,$abadmin,$abpass)
or die ('I cannot connect to the database because: ' . mysql_error());

$select=mysql_select_db($abbdd,$mysql)
or die ('I cannot select the database because: ' . mysql_error());

$handle = fopen("/home/ictechno/out/frenchprop/dbtext.txt", "r");
$count=1;
while (!feof($handle)) {
$buffer=fgets($handle, 4096);
$queryelement="$buffer";
//echo "$queryelement";
$result = mysql_query ($queryelement);
$count++;
};
fclose($handle);
?>


Only rows without the CR/LF get inserted.

Richard

 

[DRJ478]

Yep, just what I described above needs to be done:

while (!feof($handle)) {
  # another while here that checks out if the line contains
  # );
  $buffer = '';
  while(!strstr($buffer,');') && !feof($handle)){ 
    $buffer.= fgets($handle, 4096);
  }
 $queryelement="$buffer";
 $result = mysql_query ($queryelement);
  $count++;
};

 

[richclever]

Thanks heaps DR,

I am a bit confused as to what the other while you mention should be though.

Richard

 

[DRJ478]

The second while loop keeps reading chunks of the file until the buffer string contains the closing bracket with the semicolon. It's just collecting data lines until the statement end is reached.
Pseudo-op code:
Get a line. Look if it is complete. If not, keep getting lines until the statement is complete or the end of the file is reached.

You have no error checking on the mysql query part - if you had you'd seen that the SQL statements were wither truncated and then the next line syntax error etc.

 

[richclever]

What can I say,

Absolutely brilliant DRJ478. Works like a dream. Well it's certainly been a learning day today!

Many many thanks, you have really dug me out of a hole!!

Richard