MySQL won't update the last cell of the last row in a table

Question

I'm using PHP and MySQL.

MySQL will not update the last cell in my table. I can't figure out why.

Basically, I do 10 INSERTs with a foreach() loop on an array. On each loop I use the following code:

$sql = "INSERT INTO table1 (name, address, phone, date_time, process_started, process_ended)
    VALUES
    ('$name', '$address', '$phone', NOW(), 'started', '')";
$result = @mysql_query($sql, $con) or die(mysql_error());

That works fine; all the info inserts into the table.

Then when I go back through to update the "process_ended" field it updates just fine for the first 9 rows. But it will not update the "process_ended" cell of the 10th row.

Here is the code for the update. It does not use a loop; I just typed it into the script 10 times.

$sql = "UPDATE table1 SET process_ended = 'ended' WHERE name = '$name' && address = '$address'";
$result = @mysql_query($sql, $con) or die(mysql_error());

I have checked to make sure the name and address cells match up to what is in the script; and they do. I have also triple checked to make sure there are no typo's in my scripts.

I think this is something to do with MySQL because when I switch the order of the updates it's always the last cell that does not update. I have deleted the table and remade it and I also hit the repair DB button in cPanel and it says it's ok.

Answer 1

Normally this is how I debug it.

1. Do not execute your queries
2. Echo the statements or save them to a file
3. Analyse it! In your case, I think the process_ended is omitted in the last statement.
4. If not, run the last SQL statement. Get the error message and post it here!

Good luck.

Answer 2

The '&&' in your update is not an logical 'AND' construct. The parser is building a expression different than what you expect because of the precedence of '&&' is different than '='.

What the MySQL is seeing, after parse and operator precedence: UPDATE table1 SET process_end = 'ended' WHERE name = (( '$name' && address ) = '$address' )

Your SQL should be: UPDATE table1 SET process_ended = 'ended' WHERE name = '$name' AND address = '$address'

Answer 3

I am not quite sure, but I had run into similar problems with php arrays. For me it looks like an end-of-line character in php, just trim() your last cell value before insert. This had resolved my issues.

Answer 4

PHP arrays counts from 0.

If your script starts counting from 1 somewhere, first or last item may disappear.

Answer 5

It wont be mySQL. billions of tables are updated every day this bug would have caused a huge issue if it was. change your update statement to a SELECT statement and view the results, check for duplicate rows being returned.

$sql = "SELECT FROM table1 WHERE name = '$name' && address = '$address'";
$result = @mysql_query($sql, $con) or die(mysql_error());

The problem is most likely in your WHERE clause, My guess is you will find you are running the same statement twice. therefore the update statement updates the same row twice.

It won't matter what order you run the update queries in. because its updating the same rows in the table each time and therefore when you list the rows it appears to be the same one not updating (it is). it just happened to be the last one it may have been the first or the seventh.

Probably a copy and paste error when creating the statements. I do it all the time :(

DC