Mutiple INSERT's

PHP
1

I’m very new to php mysql
I want to use one sql query to insert data to two tables is this possible?

the code im using is below:

firstly this test code was run in phpmyadmin and it works fine.

INSERT INTO clients (id, seating, title, firstname, surname, birthdate, homenumber, worknumber, mobilenumber, address1, address2, city, postcode, email, nino, employment, occupation, accident_id) VALUES
(null, ‘’, ‘’, ‘Sam’, ‘ORANGE’, ‘1978-04-21’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘0’, ‘0’, NULL, NULL, NULL, NULL, 3);

INSERT INTO insurance (covertype, clients_id) VALUES
(‘Free’,LAST_INSERT_ID());

and then I tried to use the following code to do the same from php using a form:

$sql= "INSERT INTO clients SET
		title='$title',
		seating='$seating',
		firstname='$firstname',
		homenumber='$homenumber',
		surname='$surname',
		worknumber='$worknumber',
		birthdate='$birthdate',
		mobilenumber='$mobilenumber',
		address1='$address1',
		address2='$address2',
		city='$city',
		postcode='$postcode',
		accident_id='$caseid'";
		
		"INSERT INTO insurance SET
		covertype='$cover',
		clients_id= LAST_INSERT_ID()";

and it fails

any pointers or help would be much appreciated

2

the php interface to mysql can only handle one statement at a time

when phpmyadmin ran your two statements, it actually ran them one after the other for you

:slight_smile:

3

what would be the best solution for posting to two tables in this situation
while making use of the LAST_INSERT_ID()

4

There are two MySQL libraries for php.

  • MySQL
  • MySQLi

MySQLi is the “improved” library and replaces the MySQL library. MySQLi can handle multiple statements.

Nearly forgot, to mention, there is also the PDO MySQL library

5

I bet you saw this one coming:

How do i use mysqli or PDO to run two insert statements to achieve the above,
A simplified example would be great

6

You shouldnt need LAST_INSERT_ID() in your second query; if it’s an auto_incrementing field, it will automatically fill in; if you’re trying to use the result from the first query to fill in the second, take a look at [FPHP]mysql_insert_id[/FPHP] (or the mysqli and PDO equivilants: [FPHP]mysqli.insert_id[/FPHP] and [FPHP]pdo.lastinsertid[/FPHP])