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

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.

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

Answer 3

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.

Answer 4

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

Answer 5

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