即使语法正确，INSERT / UPDATE也无法正常工作？

Question

PROBLEM:

I've written and been running a PHP script that previously had been working, and today I realize that although it appeared to be functioning correctly, for the past few days no data was being inserted or updated (basically any write queries). This does not trigger any errors, and when the exact same query is copy and pasted into a mysql terminal, it inserts correctly.

CODE

$sql = "REPLACE INTO `history` (`owner`, `user`, `status`, `now_date`)
VALUES ('{$owner['username']}', '{$user}', '1', NOW())";

if (!mysqli_query($db, $sql)) { die('Error: ' . mysqli_error($db)); }

echo "Affected rows: " . mysqli_affected_rows($db);

RESPONSE

Affected rows: 1

So it appears to be working, does not trigger any error, and when the same query is input into a mysql terminal, it works, it's just via PHP that it doesn't. Permissions for the user connecting is fine.

This problem has just happened in the last week, prior to that it was working perfectly. I'm really confused.

Thanks, John.

Answer 1

You say that a column called id is the UNIQUE key of that table.

You are not using the id in that query, so how will it be able to know to do an INSERT or UPDATE(DELETE-INSERT) without the id field being there so it can make that decision.

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

See the manual