CODEWITHSUNDEEP
phpsqlinsertauto-increment
J.Doe
J.Doe

Reputation: 21

SQL, how to get the automated ID from the previous INSERT?

I am using PHP and SQL and trying to insert user data into two tables upon registration. First in the user_table and second into the character_table. I'm using an automatically generating user_id to link the tables and need to get the value of the user_id from the first INSERT (into user_table) then add it to a column in the character_table.

I tried a few methods and here is where I ended ($username, $email, $password and $character are defined above);

$sql = "INSERT INTO 
user_table (id, username, email, password)
VALUES ('NULL', '".$username."', '".$email."', '".$password."')

INSERT INTO
character_table (name, id)
VALUES ('".$character."', 'LAST_INSERT_ID()')";"

I want "id" from user_table to match with "id" inserted into character_table.

When I run the above, nothing seems to be happening. Previous attempts I always ended with id = 0. What is the correct way I can get the ID from the first INSERT?

Upvotes: 0

Views: 124

Answers (1)

JNevill
JNevill

Reputation: 50034

  1. Run your statements seperately. You run your insertion into your user_table, then grab the id then run your insertion into your character_table
  2. You can grab the id using mysql_insert_id after running the insert. Note that on the php webpage detailing the mysql_insert_id function that it is deprecated as is all mysql* functions. Which leads to...
  3. For the love of everything holy don't concatenate your variables directly to your INSERT statement. Switch to mysqli* functions or PDO if you haven't already and use prepared statements (parameterizing the query). If you build an application using mysql it means you are not parameterizing your queries which means you are at a huge risk for a sql injection attack.

If/when you switch over to mysqli or PDO functions you will find an equivalent mysqli_insert_id() (or PDO::lastInsertID()) function

Upvotes: 1

Related Questions