0

I have this exact same issue, but that answer is old, and I've read other places the use of mysql_insert_id() is depreciated and should be avoided in new code.

The database is InnoDB and the primary key for this table is set to auto_increment.

Can anyone see what I'm doing wrong here? I should note I'm a beginner and learning on my own, so I apologize is this is glaringly obvious.

$query  = "INSERT INTO VENUES (province,city,venue)" . "VALUES " . "('$province','$city','$venue')";
$result = $db->query($query);
if ($result) {
    echo "$result row(s) sucessfully inserted.<br/>";
} else {
    echo "$db->error<br/>";
}

$result = $db->query("select last_insert_id()");
echo $result->num_rows //returns 1
echo $result; //nothing beyond this line happens

UPDATE: I am using mysqli.

Community
  • 1
  • 1
skkevinperson
  • 57
  • 1
  • 8
  • you can retrieve it by using **order by** also.. if u don't want to use mysql_insert_id() or any function. – Kalpit Sep 04 '13 at 06:57
  • @kalpit: That is not a good solution since it requires another round trip to/from the DB along with all the overhead of a query. – Ayush Sep 04 '13 at 06:58
  • $xbonez he don't want to use mysql_insert_id() function then i don't think there is another way to retrieve id using mysql_* – Kalpit Sep 04 '13 at 07:03
  • update: $db is a mysqli object. Sorry for the omission. – skkevinperson Sep 04 '13 at 07:03
  • @skkevinperson update this things in post instead of making comment here. so everybody can note it. – Kalpit Sep 04 '13 at 07:06

1 Answers1

6

The correct way to get the last inserted id, when using mysql_* is to make a call to mysql_insert_id().

What you are reading is partially correct - mysql_insert_id is in the process of being deprecated, but it's not just that function. All mysql_* functions are being deprecated. Instead you should use either PDO or mysqli_*.

In your code snippet, it is unclear which database access library you are using. Since your calls seem to be bound to an object, it is likely you are using PDO or mysqli.

For PDO you can use PDO::lastInsertId.
For mysqli, use mysqli->insert_id.

Ayush
  • 41,754
  • 51
  • 164
  • 239
  • Thanks for the polite and informative answer. $db->insert_id works, my textbook was very brief on the resources available in the mysqli object. Next stop, the online manual to read more myself. – skkevinperson Sep 04 '13 at 07:10
  • @skkevinperson: You're welcome. The PHP manual is definitely very helpful when getting started. – Ayush Sep 04 '13 at 07:13