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.

Answer 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.

Answer 2

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.

Answer 3

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];

Answer 4

You forgot SELECT:

"SELECT LAST_INSERT_ID()"

Answer 5

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.

Answer 6

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.