Insert Statement into Multiple Table at once

PHP
1

Hello People,
I have being trying to insert statement into multiple table at once on my database through php but it not working. I then shared the codes into different php scripts based on each table, but I now find out that I was not able to feel the “authorid” automatically by getting the signup.id from the signup code through the php script. Below is the script, please I need help, I have read the “PHP ANTHOLOGY” and the “Build you own database Driven website”, I also have the “simple sql”, my skill has advance allot but I still need help. Your help will be appreciated
contactperson.php


include 'magic_quotes.php';
$link = mysqli_connect('localhost', 'db', 'yesoyes');
if (!$link)
{
	$error = 'Sorry we are experiencing some difficulties in creating your account.<br> 
	Please call our customer care on ---------- to report this error.';
	echo $error;
	exit();
}

if (!mysqli_select_db($link, 'hifisms'))
{
	$error = 'Sorry we are experiencing some difficulties in creating your account.<br> 
	Please call our customer care on ----------- to report this complain.';
	echo $error;
	exit();
}
if (isset($_POST['name']))
if (isset($_POST['title']))
if (isset($_POST['gsm']))
if (isset($_POST['email']))
if (isset($_POST['residential']))

{
	$name = mysqli_real_escape_string($link, $_POST['name']);
	$title = mysqli_real_escape_string($link, $_POST['title']);
	$gsm = mysqli_real_escape_string($link, $_POST['gsm']);
	$email = mysqli_real_escape_string($link, $_POST['email']);
	$residential = mysqli_real_escape_string($link, $_POST['residential']);
	
	$sql = 'INSERT INTO contactperson SET
			name="' . $name . '",
			title="' . $title . '",
			gsm="' . $gsm . '",
			email="' . $email . '",
			residential="' . $residential . '" 
			authorid=signup.id';
	
	if (!mysqli_query($link, $sql))
	{
		$error = 'Sorry we are experiencing some difficulties in creating your account.<br> 
	Please call our customer care on ---------- to report this problem.:<br> ' . mysqli_error($link);
		echo $error;
		exit();
	}


}
$output = 'Your account has been created.';
echo $output;

signup.php


include 'magic_quotes.php';
$link = mysqli_connect('localhost', 'db', 'yesoyes');
if (!$link)
{
	$error = 'Sorry we are experiencing some difficulties in creating your account.<br> 
	Please call our customer care on --------to report this error.';
	echo $error;
	exit();
}

if (!mysqli_select_db($link, 'hifisms'))
{
	$error = 'Sorry we are experiencing some difficulties in creating your account.<br> 
	Please call our customer care on ---------- to report this complain.';
	echo $error;
	exit();
}
if (isset($_POST['username']))
if (isset($_POST['password']))
{
	$username = mysqli_real_escape_string($link, $_POST['username']);
	$password = mysqli_real_escape_string($link, $_POST['password']);
	
	$sql = 'INSERT INTO signup SET
			username="' . $username . '",
			password="' . $password . '" ';
			
	if (!mysqli_query($link, $sql))
	{
		$error = 'Sorry we are experiencing some difficulties in creating your account.<br> 
	Please call our customer care on ------- to report this problem.:<br> ' . mysqli_error($link);
		echo $error;
		exit();
	}

}
$output = 'Your account has been created.';
echo $output;
2

Thanks, I implemented it to my already prepared code, and it works like magic, thanks so much “Janl” and “felgall”
I appreciate your help so much.

3

How please describe in details, possible re-arrange the code above please I really need this, my head is hot.

4

INSERT operates on a single table at a time.
Run 2 queries on the same page.

5

You probably don’t want to do the second query if the first one fails. You may also want to use transactions, so that if the second of fails, the first will be rolled back.

Your tables will have to be InnoDB


mysqli_query($link, "SET AUTOCOMMIT = 0");
mysqli_query($link, "START TRANSACTION");

$sql = "INSERT INTO signup SET
            username='Tony',
            password='1234'";
 
if(!$result = mysqli_query($link, $sql)) {
	exit("Error signing up");
	mysqli_query($link, "ROLLBACK");
}
$signupid = mysqli_insert_id($link);
$signupid = (int)$signupid;
 
 
 
$sql = "INSERT INTO contactperson SET
            name='Tony',
            title='Dr',
            gsm='1',
            email='tony@dot.com',
            residential='3', 
            authorid='$signupid'";
 
 
if($result = mysqli_query($link, $sql)) {
	mysqli_query($link, "COMMIT");
}
else {
	mysqli_query($link, "ROLLBACK");
	exit("Error signing up");
}
6

When you do the insert into the signup table you need to get the last signup id via mysql_insert_id after you have run the insert query.

Example:

$signupid = mysql_insert_id();

Then when you insert into the contactperson table you can set authorid = $signupid

I hope this is what you mean.

7

Thanks Janl, but the out put is always 0, and errors like this
Warning: mysql_insert_id() expects parameter 1 to be resource

8

The code should look something like this:


$sql = "INSERT INTO signup SET
            username='Tony',
            password='1234'";

$result = mysqli_query($link, $sql);	
$signupid = mysqli_insert_id($link);
$signupid = (int)$signupid;



$sql = "INSERT INTO contactperson SET
            name='Tony',
            title='Dr',
            gsm='1',
            email='tony@dot.com',
            residential='3', 
            authorid='$signupid'";


$result = mysqli_query($link, $sql);
9

With the code you are using the resource is $link and so for it to work you need

$signupid = mysql_insert_id($link);