From PHP to MySQL bind_param() bool error

Question

I cannot fint the error nor could find ideas from the internet. The database has key, userIP, and date. the code segment is:

$last = $conn->query("SELECT LAST_INSERT_ID();");
if (strcmp($last, "<empty string>") == 0) {
    $index = 0;
} else {
    $index = $last + 1;
}

$stmt = $conn->prepare("INSERT INTO Users (key, userIP, date) VALUES (?, ?, ?)");
$stmt->bind_param("iss", $key, $ip, $date);

$key = $index;
$ip = $_SERVER['REMOTE_ADDR'];
$date = date('Y-m-d H:i:s');

The idea is that I save the last "key" and add 1 to it. Tho it doesn't seems to work if the db is empty. I was looking over it for hours so I have ran out on ideas.

@Barmar I know the LAST_INSERT_ID() will get the last inserted ID but why do you need to do it that way when sql already have an auto-increment feature — user3585659, Mar 12 '20 at 03:52
**it doesn't seems to work if the db is empty** It returns the auto increment ID that was assigned during the most recent `INSERT` query on the same connection. It will be empty if you haven't done a previous `INSERT`. — Barmar, Mar 12 '20 at 03:56
If you have a table with auto increment, you don't need to add 1 in your own code. The database will do this automatically, that's the whole point. — Barmar, Mar 12 '20 at 03:57
And if the table doesn't have auto increment, you can't use `LAST_INSERT_ID()`. What you probably want is `SELECT MAX(key) FROM Users` — Barmar, Mar 12 '20 at 03:58

Barmar · Accepted Answer · 2020-03-12T04:08:08.727

0

You need to fetch the results of the query.

$result = $conn->query("SELECT LAST_INSERT_ID();");
$row = $result->fetch_row();
$last = $row[0];
if ($last == "") {
    $index = 0;
} else {
    $index = $last + 1;
}

But you don't need to perform a query for this, there's a built-in function for it:

$last = $conn->insert_id;

Another problem is that key is a reserved word, so you need to quote it with backticks.

$stmt = $conn->prepare("INSERT INTO Users (`key`, userIP, date) VALUES (?, ?, ?)");

edited Mar 12 '20 at 04:08

answered Mar 12 '20 at 03:52

Barmar

741,623
53
500
612

Thank you this is very useful :) but its the same error.Uncaught Error: Call to a member function bind_param() on bool in ... – Davoda 1 Mar 12 '20 at 04:05
Add error checking: https://stackoverflow.com/questions/22662488/how-to-get-mysqli-error-in-different-environments – Barmar Mar 12 '20 at 04:07
That's totally unrelated to getting the last insert ID, it means you have an error when trying to prepare the second query. – Barmar Mar 12 '20 at 04:08
`key` is a reserved word in MySQL, you need to put backticks around it. – Barmar Mar 12 '20 at 04:09
it was the `key` now its all fine and I did not sleep today. – Davoda 1 Mar 12 '20 at 04:15

From PHP to MySQL bind_param() bool error

1 Answers1