How do I get the last inserted id from a MySQL database table?

Question

I have a 'Users' table in my database and when someone makes a new account I want to, instead of doing an auto increment for the id, get the value of the last id inserted, add 1 to it, then insert it with the rest of the create user data. My existing code is:

<?php
mysql_connect("localhost","username","password");
mysql_select_db("db");

$fname = $_POST['fname'];
// ... get all the other info from create user form


$query = "INSERT INTO Users ('fname','lname','email','username','password') 
          VALUES 
          ('$fname','$lname','$email','$username','password')"

What I would want to do is get the 'id' from the Users table of the last user (row) in the table, then add 1 to it, and have the code be this:

$query = "INSERT INTO Users ('id','fname','lname','email','username','password') 
VALUES 
('$id','$fname','$lname','$email','$username','$password')";

How would I go about doing this?

EDIT: Thank you all for caring about me enough to remind me of SQL Injection. The code I provided is a minimal example of what I am doing. I didn't want to put all of it into my example. But thanks anyway

Answer 1

1. mysql_insert_id()

   mysql_query("INSERT QUERY ...");
   $id = mysql_insert_id();
   

2. Use mysqli instead (or PDO).

   $mysqli->query($query);
   $id = $mysqli->insert_id
   printf ("New Record has id %d.\n", $id);
   

3. Escape properly! Because you are vulnerable to SQL injection.

4. increment: $lastid = $lastid + 1; or just $id++.

   The auto-increment is there for a reason. Use it.

Answer 2

You can use mysql_insert_id(); function:

$query = "INSERT INTO Users ('fname','lname','email','username','password') 
          VALUES 
          ('$fname','$lname','$email','$username','password')"
$lastid=mysql_insert_id(); /* GET THE ID OF THE INSERT QUERY */
$lastid++; /* INCREMENT THE ID */

Some tips for your future: Use mysqli prepared statement instead of mysql. Read more about SQL injections here.