12

I'm working on a table that has 4 columns and the first one is an auto incrementing integer called id.

If I'm going to insert into this table using mysqli prepared statements I keep having trouble inserting a query that works. Using phpMyAdmin It tells me to give it NULL. I've tried this:

$query = "INSERT INTO tbl (id, col2, col3, col4) VALUES ('NULL', ?, ?, ?)";
$stmt -> bind_param("ssi", $col2, $col3, $col4)

And this

$query = "INSERT INTO tbl (id, col2, col3, col4) VALUES (NULL, ?, ?, ?)";
$stmt -> bind_param("ssi", $col2, $col3, $col4)

And only give bind_param 3 arguments (the last 3). Neither of those work. I also tried this:

$null = NULL;
$query = "INSERT INTO tbl (id, col2, col3, col4) VALUES (?, ?, ?, ?)";
$stmt -> bind_param("issi", $null, $col2, $col3, $col4);

None of these work. Is there a standardized way of inserting into this type of table?

Dharman
  • 30,962
  • 25
  • 85
  • 135
fdfdsfsdfsdfds
  • 121
  • 1
  • 1
  • 3
  • do u also want to insert auto-incremtn field explicitly? – Sarfraz Feb 09 '10 at 06:27
  • "Keep having trouble" sounds like you should start checking for error messages. They might help you to find the error more easily than just fiddling around until it works – Nico Haase Nov 08 '19 at 08:02

4 Answers4

18

Just skip the id field, MySQL will fill it automatically:

$query = "INSERT INTO tbl (col2, col3, col4) VALUES (?, ?, ?)";
$stmt->bind_param("ssi", $col2, $col3, $col4)
Tatu Ulmanen
  • 123,288
  • 34
  • 187
  • 185
7

If the id field is the auto_increment, then just don't specify it in your insert query :

$query = "INSERT INTO tbl (col2, col3, col4) VALUES (?, ?, ?)";

And, of course, don't try to bind any parameter to it ;-)


As it's generated by MySQL, there is no need to pass that column.

Pascal MARTIN
  • 395,085
  • 80
  • 655
  • 663
5

This should work, because id is added automatically (incremented for this reason) by mysql:

 $query = "INSERT INTO tbl (col2, col3, col4) VALUES (?, ?, ?)";

In some cases, you have to insert auto_incremtnt field explicitly, if this is the case then you can use the INSERT IGNORE statement, see mysql manual for more info about it.

Sarfraz
  • 377,238
  • 77
  • 533
  • 578
3

This one

$query = "INSERT INTO tbl (id, col2, col3, col4) VALUES (NULL, ?, ?, ?)";
$stmt -> bind_param("ssi", $col2, $col3, $col4)

should surely work. What is the exact error you get?

Now I look better, you have $query and $stmt. What do you have in between? Probably you are missing some part.

It should be

$stmt = $mysqli->prepare("INSERT INTO tbl (id, col2, col3, col4) VALUES (NULL, ?, ?, ?)");
$stmt -> bind_param("ssi", $col2, $col3, $col4);
$stmt ->execute();
Peter Smit
  • 27,696
  • 33
  • 111
  • 170