[SOLVED] Writing foreign key to the database

[almystersv]

Hi Guys,

 

I am trying to write to two tables. One is orders and one is productorder.

OrderID in the orders table is auto increment and is a foreign key in the productorder table. When an order is saved it writes it first to the orders table and then in the same action writes it to the productorder table but I need to get the orderID value from the first query to be entered into the productorder table in the second query.

 

I Hope this makes sense.

 

Here is my code, at the moment I am hard coding the OrderID in the productorder table as I dont know how to do it properly.

 

<?php
require "connect.php";
$empID = $_SESSION['empID'];
$username = $_SESSION['username'];

if($_SESSION['basket'] == !null)
{
	$query =  "insert into orders values ('','".$empID."','".$username."','".date("d-M-Y")."')";
	$result = @mysql_query($query, $connection) 
	or die ("Unable to perform query<br>$query");

	foreach($_SESSION['basket'] as $key => $product)
	{
		$query2 = "insert into productorder values ('[color=yellow]1[/color]','".$_SESSION['basket'][$key]['URN']."','".$_SESSION['basket'][$key]['quantity']."','Awaiting Approval')";
		$result2 = @mysql_query($query2, $connection) 
		or die ("Unable to perform query<br>$query2");
	}
	unset($_SESSION['basket']);
	$message2 = "Order has successfully been submitted";
	header("Location: StationaryMain.php?var=URN");
	exit();
}
else
{
	$message1 = "There are no products in the Stationary Basket";
	header("Location: StationaryBasket.php?message1=$message1");
	exit();
}

?>

[Psycho]

http://us.php.net/mysql_insert_id

 

[Psycho]

Here's the revised code. BTW: You do not need to assign the query to a variable if you are not going to use that variable (e.g. for an insert). Also, have you tested the ELSE caluse? You may need to URL encode that message to work since it has spaces in it. Your success path has a message with spaces, but you don't use it.

 

<?php
require "connect.php";
$empID = $_SESSION['empID'];
$username = $_SESSION['username'];

if($_SESSION['basket'] == !null)
{
	$query =  "insert into orders values ('','".$empID."','".$username."','".date("d-M-Y")."')";
	@mysql_query($query, $connection) or die ("Unable to perform query<br>$query");
	$order_id = mysql_insert_id();

	foreach($_SESSION['basket'] as $key => $product)
	{
		$query2 = "insert into productorder values ($order_id,'".$_SESSION['basket'][$key]['URN']."','".$_SESSION['basket'][$key]['quantity']."','Awaiting Approval')";
		@mysql_query($query2, $connection) or die ("Unable to perform query<br>$query2");
	}
	unset($_SESSION['basket']);
	$message2 = "Order has successfully been submitted";
	header("Location: StationaryMain.php?var=URN");
	exit();
}
else
{
	$message1 = "There are no products in the Stationary Basket";
	header("Location: StationaryBasket.php?message1=$message1");
	exit();
}

?>

[almystersv]

Superb. Thank you for your help!