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

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开发者_Go百科 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.

Perhaps UPDATE is objecting to updating an empty string. Have you tried it with a placeholder instead of ''?

Two loops: serial or parallel? If loops are independent then check whether the first is finished before the second.

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.

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.

PHP arrays counts from 0.

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

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

With MySQL, I always find the best way to locate an error like this is by trying it manually. do an on screen echo $sql line you're trying to run and then put it through PHPMyAdmin and see what it comes back with.

That should help you pinpoint the issue.

Not using a loop is bad, not using abstracted insertitemtotable()- type functions is bad, go back to designing your stuff correctly from the beginning and you will never see that kind of problem once your basic functions have been cleaned up.

Furthermore, this will greatly help in making sure all your input is correctly escaped, etc.

There surely is an issue in your code which you have hand-typed and copy/pasted, like updating two times the 9th or something like that.