mysql insert within a while loop?

no error message

[Code Sentinel]

I got a database with a lot of rows of basic data, what I did so far is create a new field manually then take all the data from an existing field and modify it using str_replace and then UPDATE'd the new field with the modified string. This worked perfectly BUT took very long (I think it's going over every row repeatedly and there's over 5000 rows).

So I tried to find a way to make it quicker and decided it might be better to eliminate the WHERE clause and just insert the data into an empty table or temporary table before sorting it using the INSERT..SELECT combo.

So I changed the UPDATE part of the mysql_query in the while loop into an INSERT query and rechecked it and tried different variations over and over while referring to the php and mysql manuals. NOTHING gets inserted into the new or temporary table and I don't recieve any notices or errors since the script goes all the way through and displays the Complete echo at the bottom.

I'm still learning php and mysql but I can't for the life of me figure out why UPDATE worked within the while loop but INSERT doesn't. I know my code is probably messy but it worked fine initially :)

This isn't a script to be used on a live server but just part of a script I use locally to prepare a SQL dump I can upload.

$result = mysql_query("SELECT record1,record2,record3,record4,record5,record6 FROM orig_table") or die (mysql_error());

while ($row = mysql_fetch_array($result)) {

$oldrecord = str_replace(" ", "-", $row[4];
$newrecord = strtolower($oldrecord);

$query = 'INSERT INTO temp_table VALUES ('.$row[0].','.$row[1].','.$row[2].','.$row[3].','.$oldrecord.','.$newrecord.')';
mysql_query($query);
}

That should be approx the main part in question, the rest of the script basically works fine.

Previously the part within the loop which WORKED was:

mysql_query("UPDATE orig_table SET record5 = '".$oldrecord."' WHERE record1 = '".$row[0]."'");

mysql_query("UPDATE orig_table SET record6 = '".$newrecord."' WHERE record1 = '".$row[0]."'");

Can anyone shed some light as to what I'm doing wrong?
Are mysql inserts allowed within a while loop which pulls data using mysql_query as well?

[Code Sentinel]

I still haven't got the insert within while working..but I'd like to add that after I posted I added an index to the WHERE column with the update query and it improved the speed dramatically.

I'd still like to know if there is somethign wrong with inserts within while loops though.

[ergophobe]

Hmmm. Have you tried quotes around your insert values like you had around your update values? Normally if that were causeing the problem, though, you would get an error.

$oldrecord = str_replace(" ", "-", $row[4];

You say you get no errors, so I assume the missing paren is a typo.

[Code Sentinel]

by quotes around insert values.. do you mean the part where it says:

INSERT.... VALUES ('.$row[0].','.$row[1].','.$row[2].','.$row[3].','.$oldrecord.','.$newrecord.')';

?

The part of the code you quoted is the one where I replace all spaces in a string with a hyphen, yes a typo :) Where would quotes go in that?

While looking over insert examples from SQL dumps and the mysql manual I didn't think I needed them. I tried all variations I could think of with the INSERT query (with field names, with that quote looking character next to the 1 key ` like in the complete SQL dumps, etc)

I'll have to give that a try and see what happens.. but I didn't get any error message or notices and I have all php error reporting on with my local installation. I had the script echo the variables I did string functions on to make sure it was actually being done.. and yes the echo spit out the values I expected.. they just didn't get inserted with the INSERT query where the UPDATE query used to be.

Just tried your suggestion of quotes around the values I'm inserting.. still nothing being inserted :/

[ergophobe]

- echo your query to the screen

- paste/type the result (i.e. the actual query being sent to the server) into your mysql client.

- does it do anything?

[Code Sentinel]

ah HA!

ERROR: Unknown Punctuation String @ 273

Some of the fields had parenthesis.. which command escapes those? mysql_escape_string or addslashes? Something else I'm not aware of?

I'll try a few more things.

edit:

nada.. tried all variations of the insert command, made sure fields matched values, tried without quotes for int values.

[ergophobe]

fields had parenthesis.. which command escapes those?

The only thing you should need to escape within *data* is the quote mark. Everything else is "escaped" by virtue of being within quote marks. So this is fine

VALUES('This isn\'t my data (or anyone else\'s)', 'This is my data')

This will cause an error

VALUES('This isn't my data (or anyone else\'s)', 'This is my data')

As far as SQL is concerned, the problem could be perceived as a missing quote, but also as missing commas, extra parens and so forth.

Tom

[Code Sentinel]

yeah guess it wasn't the parenthesis.. I went over the insert query with a fine toothed comb checking to make sure all fields were declared and values to match.

checked values with and without quotes, checked parenthesis, table name, field names, escaped data etc etc.. maybe I'll revisit it again later.. I got the old UPDATE query working very fast with the addition of an index to a single field.(5 min down to 2 seconds)