MySQL- Why is LAST_INSERT_ID() not working for me?

Question

I have the following code:

  public function createNewGuide($userID,$guideName)
  {
    $sql =" INSERT INTO myTable(name, updated) 
            VALUES ('$guideName', 'NOW()')";

    //Process query
    $this->query($sql); // This inserts the new row
    $this->query('LAST_INSERT_ID()'); // This throws an error

    return $this->query_result;
  }

My query function looks like this:

  private function query($sql) 
  {
      $this->query_result = mysql_query($sql, $this->conn)
        or die("Unable to query local database <b>". mysql_error()."</b><br>$sql");   
  }

I get the following error:

MySQL Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LAST_INSERT_ID()'

I've googled and looked at similar problems, but not found an answer :(

I have not tried the PHP function mysql_insert_id(), as I really would like to do this using SQL.

You should consider using query parameters instead of inserting values directly into the SQL string. Your code may have an SQL injection vulnerability. — Mark Byers, Jan 09 '10 at 12:45
I know - using MySQLi right? I've tried, but I'm not able to prcess queries then. I only get errors :( So I'm handling it as good as I can in other ways. — Steven, Jan 09 '10 at 13:13
You also have an XSS vulnerability in the way you are displaying your database error messages. http://en.wikipedia.org/wiki/Cross-site_scripting — Mark Byers, Jan 09 '10 at 13:42
I did some testing and there is definitely a bug here still. `SELECT LAST_INSERT_ID()` returns 0. If you then do `SELECT LAST_INSERT_ID(column)` you get the right value. Then if you do it again without the ID, you get the right value. Insert another row, now try without the ID, it hasn't updated. SELECT with the id, now it works and it updates something so that without the ID works again, until you insert a new row. It's broken. — Mageician, Apr 21 '21 at 19:07

score 16 · Accepted Answer · answered Jan 09 '10 at 12:41

16

Why not just use PHP's mysql_insert_id?

Irrespective...

SELECT LAST_INSERT_ID()

...should work as long as you've an auto-increment column in the table.

answered Jan 09 '10 at 12:41

John Parker

54,048
11
129
129

Because I think it's safer to get the correct ID using the SQL statement? E.g. when multiple users inserts and updates data in the same table. – Steven Jan 09 '10 at 12:43
1

Unless you're using InnoDB and carrying out the above within a transaction, I don't think this will prove to be the case. That said, I believe that the mysql_insert_id is connection specific, hence what you're referring to won't be an issue unless you're sharing the same database connection. – John Parker Jan 09 '10 at 12:45
A am using InnobDB. My problem now with `LAST_INSERT_ID()`, is that is just returns 0. Have no idea why. – Steven Jan 09 '10 at 12:51
Just checking the basics, but does your table actually have an auto-increment column? – John Parker Jan 09 '10 at 12:54
ok, I ended up using mysql_insert_id() - since I can't figure out why `LAST_INSERT_ID()` only returns 0. – Steven Jan 09 '10 at 13:10
Basically, we need an auto-increment column. I been trying to get id from the composite key. – Harikrushna Patel Mar 10 '22 at 10:54
it's better to use in the procedure. – Harikrushna Patel Mar 10 '22 at 11:05

score 6 · Answer 2 · answered Jan 09 '10 at 12:40

6

You forgot SELECT:

"SELECT LAST_INSERT_ID()"

answered Jan 09 '10 at 12:40

Mark Byers

811,555
193
1,581
1,452

3

Thanks, that was it. But it returns 0 :( Why is that? – Steven Jan 09 '10 at 12:41

score 4 · Answer 3 · answered Aug 25 '16 at 09:41

4

If SELECT LAST_INSERT_ID(); returns 0;

Use this query :

SELECT LAST_INSERT_ID(Id) from table_name order by LAST_INSERT_ID(Id) desc limit 1;

it will give you the required result.

answered Aug 25 '16 at 09:41

manish Prasad

636
6
16

score 3 · Answer 4 · answered Jan 09 '10 at 12:43

3

That won't work without a SELECT:

SELECT LAST_INSERT_ID();

or just use mysql_insert_id, it's a php function which does the same on the php level. However, use the first method if your table ids are BIGINT.

answered Jan 09 '10 at 12:43

psaniko

1,180
16
17

score 2 · Answer 5 · edited May 31 '12 at 15:53

2

If you have multiple Database links into the same enviroment, you should always specify the Link Identifier.

In case of mysql_insert_id php function you should always call it using mysql_insert_id( $link_id );

In case you call it by SQL query using SELECT LAST_INSERT_ID( link_id ).

edited May 31 '12 at 15:53

Martijn Pieters

1,048,767
296
4,058
3,343

answered May 31 '12 at 15:47

Alessio Nobile

31
2

score 1 · Answer 6 · answered Jan 09 '10 at 12:40

1

As you are using the mysql_* functions, why not just use the mysql_insert_id function, instead of calling LAST_INSERT_ID() yourself ?

Still, the SQL error you are getting is probably because the SQL query you are sending to the server is this one :

LAST_INSERT_ID()

Instead of this one :

select LAST_INSERT_ID()

There should be a select, if you are doing an SQL query to... select... some data.

answered Jan 09 '10 at 12:40

Pascal MARTIN

395,085
80
655
663

yes, you are correct. I'm not using the PHP function, because I'm not sure if it will return the correct ID if multiple users at the same time ads or updates data in the same table. However, using `select LAST_INSERT_ID()` only returns 0.... – Steven Jan 09 '10 at 12:46
1

I'm pretty sure `mysql_last_id` will do exactly what you want, as it's working on the current connection. And it's what I've always used, actually ^^ – Pascal MARTIN Jan 09 '10 at 12:51

score 1 · Answer 7 · answered Jan 09 '10 at 13:21

1

The guys have already answered that you were missing the SELECT prefix.

By the way, you should watch your INSERT statement... it has a clear door for SQL injection if $guideName is not escaped.

answered Jan 09 '10 at 13:21

Clash

4,896
11
47
67

score 1 · Answer 8 · answered Jan 09 '10 at 16:38

1

LAST_INSERT_ID() returns zero if no row was inserted.

You should check that your INSERT actually succeeded. Always test the return value of mysql_query() and other functions, which is usually FALSE if an error occurred.

$sql =" INSERT INTO myTable(name, updated) 
        VALUES ('$guideName', 'NOW()')";

if ($this->query($sql) === FALSE) {
  die(mysql_error());
}

if (($result = $this->query("SELECT LAST_INSERT_ID()")) === FALSE) {
  die(mysql_error()); 
}

if (($row = mysql_fetch_array($result)) === FALSE) {
  die(mysql_error()); 
}

$id = $row[0];

answered Jan 09 '10 at 16:38

Bill Karwin

538,548
86
673
828

Thanks for your reply. I think I had forgotten to use the SELECT statement. Will test this next time I do some refactoring :) – Steven Feb 27 '12 at 18:42
Probably the INSERT failed because you put NOW() inside quotes, making it a string literal that is not a valid datetime literal. You want it to be a function call. – Bill Karwin Feb 27 '12 at 19:26

score 0 · Answer 9 · answered Jan 09 '10 at 12:43

0

SELECT LAST_INSERT_ID();

If I were you. I would get your insert/select last_insert_id to work from the command line or query browser first, before php. At minimum, this will at least confirm or deny correct sql syntax.

answered Jan 09 '10 at 12:43

Kris Krause

7,304
2
23
26

score 0 · Answer 10 · answered Feb 17 '11 at 14:46

I agree with whoever says you should use mysql_insert_id, but if you want to use LAST_INSERT_ID, you can use this:

function getLastInsertId($db_connection)
{
   $result = 0;

   if($query_result = mysql_query("SELECT LAST_INSERT_ID();", $db_connection))
   {
      $temp = mysql_fetch_row($query_result);
      $result = $temp[0];   
   }

   return $result;   
}

score 0 · Answer 11 · answered Apr 05 '21 at 04:16

0

I use

$Last_ID=$mysqli->insert_id;

Once i have problem. Insert_id was int(0) after running insert query. The matter was in additional "Select" query after "Insert" query before getting insert_id, which throw it, as i assume.

answered Apr 05 '21 at 04:16

VasMed

1

score 0 · Answer 12 · edited Oct 27 '12 at 21:28

0

I think your table has datetime/timestamp column and see your query has NOW() varchar value instead of datetime value, so your SQL query should have return false.

If the query return false you will not get last inserted id (always for current connection).

edited Oct 27 '12 at 21:28

Alex Essilfie

12,339
9
70
108

answered Feb 22 '12 at 09:35

Thangaraj

53
1
7

MySQL- Why is LAST_INSERT_ID() not working for me?

12 Answers12

Linked

Related