INSERT into 2 tables?

[K0108788]

Hi All,

Is it possible to insert into 2 tables at once? At the moment I have 2 insert statements that I want combine into one insert statement, but am not sure on the syntax for inserting into 2 tables at once:

$query = "INSERT INTO student_project (project_ID, lecturer_role, LK_ID, SK_ID, project_name, project_desc) " .
                           "VALUES (NULL, '" . $_POST['role'] . "', '" . $_SESSION['user_logged'] . "', " .
                          " '" . $_POST['students'] . "', '" . $_POST['project_name'] . "', " .
                           " '" . $_POST['project_desc'] . "'); ";
	$result =	mysql_query($query)
		or die(mysql_error());

$query = 	"INSERT INTO project_assess (project_assess_ID, project_ID, LK_ID) 
					VALUES( '$assessid', '$projectid', '". $_SESSION['user_logged'] ."'); "; 
$result = mysql_query($query) 
	or die(mysql_error());

[rincewind456]

You can do

$query = "INSERT INTO student_project
(project_ID, lecturer_role, LK_ID, SK_ID, project_name, project_desc) " .
"VALUES (NULL, '" . $_POST['role'] . "', '" . $_SESSION['user_logged'] . "', " .
" '" . $_POST['students'] . "', '" . $_POST['project_name'] . "', " .
" '" . $_POST['project_desc'] . "');

INSERT INTO project_assess (project_assess_ID, project_ID, LK_ID)
VALUES( '$assessid', '$projectid', '". $_SESSION['user_logged'] ."');";
$result =    mysql_query($query)
or die(mysql_error());

or you could rename your queries uniquely and do

$query_SP = "INSERT INTO student_project (project_ID, lecturer_role, LK_ID, SK_ID, project_name, project_desc) " .
"VALUES (NULL, '" . $_POST['role'] . "', '" . $_SESSION['user_logged'] . "', " .
" '" . $_POST['students'] . "', '" . $_POST['project_name'] . "', " .
" '" . $_POST['project_desc'] . "'); ";


$query_PA =     "INSERT INTO project_assess (project_assess_ID, project_ID, LK_ID)
                    VALUES( '$assessid', '$projectid', '". $_SESSION['user_logged'] ."'); ";

$query = $query_SP.$query_PA;
$result = mysql_query($query)
or die(mysql_error());

Always making sure that there is a delimiting semi-colon between each insert.

[K0108788]

Thanks rincewind456.

I will try that out. 🙂

[K0108788]

Just a quick question:

$query = "INSERT INTO student_project 
	(project_ID, lecturer_role, LK_ID, SK_ID, project_name, project_desc) " . 
	"VALUES (NULL, '" . $_POST['role'] . "', '" . $_SESSION['user_logged'] . "', " . 
	" '" . $_POST['students'] . "', '" . $_POST['project_name'] . "', " . 
	" '" . $_POST['project_desc'] . "');  

INSERT INTO project_assess (project_assess_ID, project_ID, LK_ID) 
VALUES(NULL, '$projectid', '". $_SESSION['user_logged'] ."');"; 
$result =    mysql_query($query) 
or die(mysql_error()); 

When the project_ID is inserted in the first part of the query, I want that same value to be inserted in the second part as well. How can I assign this value to $projectid or make use of mysql_insert_id? Thanks for your help so far!

[rincewind456]

If you need to make use of the id created in the first insert then you'll have to run them as seperate queries.

[Sxooter]

Just a quick word of warning about using more than one statement inside a mysql_query().

If the first query generates a warning or error, you'll never know, cause you only get the warnings or errors from the last statement executed.

Sometimes this is ok, sometimes it's not. Just something you should know.

[K0108788]

$query_SP = 	"INSERT INTO student_project (project_ID, lecturer_role, LK_ID, SK_ID, project_name, project_desc) 
									VALUES ('$projectid', '" . $_POST['role'] . "', '" . $_SESSION['user_logged'] . "', 
									'" . $_POST['students'] . "', '" . $_POST['project_name'] . "',  

									'" . $_POST['project_desc'] . "'); "; 

	$query_PA =     "INSERT INTO project_assess (project_assess_ID, project_ID, LK_ID) 
                				VALUES('$assessid', '$projectid', '". $_SESSION['user_logged'] ."'); "; 

$query = $query_SP.$query_PA; 
$result = mysql_query($query) 
or die(mysql_error()); 

I am getting the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; INSERT INTO project_assess (project_assess_ID, project_ID, LK_ID) ' at line 4

Can anyone help out?