Reputation: 19455

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

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.

Upvotes: 4

Answers (14)

Fatih Aydın

Reputation: 1

Another issue. like i did.

I deleted a user and created new one. But doesnt give attention to right column of checkboxes.

Dont forget to check insert, update etc on cPanel -> MySQL user list -> privilege.

Upvotes: 0

Rahul

Reputation: 41

I just stumbled upon a solution that might help you with

If you are trying to get the ID of your newly inserted row, then check the result of insertion query,

   OkPacket {
          fieldCount: 0,
          affectedRows: 1,
   > > >  insertId: 14,
          serverStatus: 2,
          warningCount: 0,
          message: '',
          protocol41: true,
          changedRows: 0
         }

Upvotes: 0

VasMed

Reputation: 1

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.

Upvotes: 0

manish Prasad

Reputation: 676

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.

Upvotes: 4

Thangaraj

Reputation: 73

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

Upvotes: 0

Alessio Nobile

Reputation: 41

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

Upvotes: 3

Marco Demaio

Reputation: 34437

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

Upvotes: 0

Bill Karwin

Reputation: 562881

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

Upvotes: 1

Clash

Reputation: 5025

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.

Upvotes: 1

Kris Krause

Reputation: 7326

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.

Upvotes: 0