CODEWITHSUNDEEP
phpmysqlformsmysql-insert-idjunction
Jason Cooper
Jason Cooper

Reputation: 3

How to use mysql_insert_id with a junction table

I have a form and three tables: customer, phone_number, and junction (to create a many-to-many relationship).

I need the form to populate all three tables using the newly created auto-incremented ids from the customer and phone number tables to populate the customer_id and phone_id columns in the junction table.

I came up with the following, which will insert the newly created id from the customer table into the junction table, but I can't figure out how to get the phone number to do the same thing.

$query = "INSERT INTO customer (first_name, last_name) VALUES (%s, %s)",
GetSQLValueString($_POST['first_name'], "text"),
GetSQLValueString($_POST['last_name'], "text"));
$result = mysql_query();
$insertID = mysql_insert_id();

$query = "INSERT INTO junction (customer_id) VALUES ($insertID)";
mysql_select_db($database_hh, $hh);
$result = mysql_query($query, $hh) or die(mysql_error());

Upvotes: 0

Views: 1189

Answers (1)

peterm
peterm

Reputation: 92795

Here is pseudo code for you:

//Insert a customer
$query = "INSERT INTO `customer` ..."
...
//Get the newly inserted customer's ID
$customer_id = mysqli_insert_id();

//Insert a phone
$query = "INSERT INTO `phone` ..."
...
//Get the newly inserted phone's ID
$phone_id = mysqli_insert_id();
...
//Insert them in the junction table
$query = "INSERT INTO `junction` (customer_id, phone_id) VALUES ($customer_id, $phone_id)"

And since you are using mysql_* here is the comment that you might see a lot here at StackOveflow:

Please, don't use mysql_* functions for new code. They are no longer maintained and the community has begun the deprecation process. See the red box? Instead you should learn about prepared statements and use either PDO or MySQLi. If you can't decide, this article will help to choose. If you care to learn, here is good PDO tutorial.

Upvotes: 1

Related Questions