CODEWITHSUNDEEP
phpmysqldatabasemysqliinsert
Denis
Denis

Reputation: 103

How to GET id if it's not added to database yet? (mysql)

For example I have two databases:

customer table (id is auto increase)

---------------------------------------
----- id ----- name ----- surname -----
----- 1  ----- BOB  ----- test1   -----
----- 2  ----- Jhon ----- test2   -----
----- 3  ----- Nick ----- test3   -----
---------------------------------------

and product table (id is not null and you have to add it manually)

---------------------------------------------
----- id ----- product ----- date       -----
----- 1  ----- orange  ----- 10/10/2014 -----
----- 2  ----- apple   ----- 12/10/2014 -----
----- 3  ----- melon   ----- 26/10/2014 -----
---------------------------------------------

*EXAMPLE are made up i have much more complicated and bigger table.

Question is:

Using one add.php file I would like to add

Name = ____ Surname = _____

and after GET id from the field where name and surname (from table customer ) was added and using it add product and date (to product table) using same id.

I understand how to add information to multiple tables: HERE

But how add information to the tables 1 and after using ID from table1 add tables2 this is where I'm confuse.

Upvotes: 0

Views: 65

Answers (2)

O. Jones
O. Jones

Reputation: 108796

To insert your first row, use this query, as normal.

INSERT INTO customer (name, surname) 
              VALUES ('Marie', 'Antoinette')

It will set the LAST_INSERT_ID() value for your connection. Then to insert your next row use this query, which incorporates LAST_INSERT_ID().

INSERT INTO product (customer_id, product, date)
             VALUES (LAST_INSERT_ID(), 'Cake', '1770-04-18')

This lets you do the second insert without retrieving the LAST_INSERT_ID explicitly. But if you have multiple product rows for one customer, you need something slightly different in your SQL.

INSERT INTO customer (name, surname) 
              VALUES ('Marie', 'Antoinette');

SET @custid =: LAST_INSERT_ID();

INSERT INTO product (customer_id, product, date)
             VALUES (@custid, 'Cake', '1770-04-18')
INSERT INTO product (customer_id, product, date)
             VALUES (@custid, 'Guillotine', '1770-04-19')

This is a good way to do things because it works properly on a busy system where more than one database client might be inserting customers. LAST_INSERT_ID() is maintained by MySQL connection-by-connection. It's also good because you don't have to retrieve, and then send back, the last inserted id value.

Upvotes: 2

Aleksandar Vasić
Aleksandar Vasić

Reputation: 593

use mysql_insert_id(); which gives you id of last query

http://php.net/manual/en/function.mysql-insert-id.php

example from php.net

<?php
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
mysql_select_db('mydb');

mysql_query("INSERT INTO mytable (product) values ('kossu')");
printf("Last inserted record has id %d\n", mysql_insert_id());
?>

so basicly you execute first query, get id with mysql_insert_id() and write second query

Upvotes: 1

Related Questions