0

When inserting a new record into a table with an auto-incrementing ID column, it is normally enough to give the ID field the value NULL or omit it from the INSERT query, as explained at How to insert new auto increment ID

INSERT INTO  `database`.`table` (`id`, `user`, `result`) VALUES (NULL, 'Alice', 'green')");

or 

INSERT INTO  `database`.`table` (`user`, `result`) VALUES ('Alice', 'green')");

My question is - how do you do the same thing when using prepared statements. I have tried the following, using NULL:

$stmt = $db->prepare("INSERT INTO `db` (id, name, password, text) VALUES (NULL, ?, ?, ?)");
$stmt->bind_param('sss', $name, $password, $text);
$stmt->execute();

and the fowllowing, omitting the ID field:

$stmt = $db->prepare("INSERT INTO `test_db` (name, password, text) VALUES (?, ?, ?)");
$stmt->bind_param('sss', $name, $password, $text);
$stmt->execute();

When I run this I get nothing inserted and no error message in the browser. I think it is because it is trying to insert a duplicate value for the ID field (stackoverflow.com/questions/12179770/…) - but why it should do that when this seems equivalent to the non-prepared-statement way of inserting data, and then give no message, I'm not sure.

Any ideas most welcome!

Community
  • 1
  • 1
westernKid
  • 167
  • 1
  • 3
  • 12
  • 2
    What do you mean by "neither worked?" What happened? – Explosion Pills Jul 11 '13 at 11:49
  • 1
    prepared statements has nothing to do with literal NULLs in the query. Your problem is somewhere else – Your Common Sense Jul 11 '13 at 11:52
  • I get nothing inserted and no error message in the browser. I think it is because it is trying to insert a duplicate value for the ID field (http://stackoverflow.com/questions/12179770/mysql-1062-duplicate-entry-0-for-key-primary) - but why it should do that when this seems equivalent to the non-prepared-statement way of inserting data, and then give no message, I'm not sure. – westernKid Jul 11 '13 at 11:53
  • 1
    `no error message in the browser.` - You didn't ask for it. Here you are: http://stackoverflow.com/a/15447204/285587 – Your Common Sense Jul 11 '13 at 12:02
  • Thanks, Your Common Sense - the link led to ` ... or trigger_error($mysqli->error)`, which confirmed it was the duplicate primary key problem. The database structure had got altered so auto_increment no longer worked. After re-initializing the database, all is now well. – westernKid Jul 11 '13 at 14:29

0 Answers0