[Resolved] dynamic INSERT loop hates me...

[vaska]

I'm trying to create a script that will allow clients to export from a database and easily insert it into another - I've never done this so I'm not really sure exactly how this is done, but this is what I have...

$filename = "$file";
$fd = fopen ($filename, "rb");
$stuff = fread ($fd, filesize ($filename));
fclose ($fd);
$row = explode("\n",$stuff);
for($i=0; $i<sizeof($row); $i++)
{ 
$query .= "$row[$i]";
}

To be sure, I have printed out my array (using print_r) and it's fully loaded just like I want it.

The "file" has data that looks like this...

("INSERT INTO table VALUES (79, '[e-mail]', 'Name', 'Last Name', '0', '1')");\n

If it were as simple as using phpMyAdmin this would be great, but it's not, the users are not allowed to touch it...I've dug through their file but that code is from another planet, I can't figure anything out in there...

Any ideas?....... . . ... . . v

[swr]

This:

$query .= "$row[$i]";

will give you problems, for a couple of reasons...

Using arrays in double-quotes doesn't work right. PHP thinks the [ and ] are part of the string, so it doesn't pull the element out of the array.The above will probably become Array[0] or some such thing. Remove the double quotes; you don't need them in this case. When you really do need to use an array in double-quotes, do it like this:
"blah blah {$row[$i]} blah blah". The curly brackets are important; it tells PHP that the contents are a variable, not a literal string.

Building one really long query using .= might make a query that is too large. MySQL usually has a limit on how large a single query it can take. If you exceed that, you'll get an error. Instead of appending all the inserts into one big insert to execute all at once, execute each insert one at a time.

The format of the data you've shown doesn't look right either. The query is surrounded by brackets and quotes. That'll cause problems if you try to feed it to MySQL that way. When you generate the dump, don't put brackets or quotes.

And yes, dumping a database to a bunch of SQL statements and then executing them is a reasonable way to backup and restore a database.

[vaska]

Thanks for the great answer, this was really helpful to me. After I read your reply I was able to get it working on the first try (a debate about how good this code is should be saved for another time).

This is how I got it working (just the important parts):

{ 
$query = mysql_query("$records[$i]");
}

And from the text file (this is exactly how phpMyAdmin exported this data):

INSERT INTO table VALUES (1, 'email', 'Name ', '0', '0', '1')|

Well, the '|' was my change from the way phpMyAdmin does things.

🙂