Inserting data and Retreiving the same data immediately

jeffgman

Okay, I have a database where I have one table which will be the header file of an online order. The second table will be the detail lines of that order. The first table (header) consists of:

order_no - INT 5 auto_increment unique
date - date
store - varchar(2)

the second table (detail) consists of:

order_no - INT 5
id - varchar(5)
sku- varchar(11)
order - varchar(8)
date - date
store - varchar(2)

The first page is a form with the items listed on it and the user will put the quantity for each item they want. The quantity will go into a variable called order. I have it set up to call another script which will create the header file and the detail file. But, when I create the header file with the following:

<?php
//connect to the database
require('inc/database_conn.php');

//get the variables from the other page
$id=$_POST['id'];
$sku=$_POST['sku'];
$description=$_POST['description'];
$order=$_POST['order'];
$store=$_POST['store'];
$date=now();

// create the unique order number

$headerqry = "INSERT INTO misc_order_header (date, store) VALUES ($date, $store)";

mysql_query($headerqry, $conn) or die(mysql_error());

How do I get the new automatically created order_no? Do I have to do a fetch of that data? But, the problem is there could be the same date and same store with different order numbers. How do I know the order number of the line I just inserted?

Thanks for your help,
Jeff

stevesweetland

are you using the primary key as the order id? if so, you need to look at mysql_insert_id.

this will return the last recently created id.

jeffgman

Thank you. That command is exactly what I was looking for. But, now I have a problem with my second query. Here is my first file:

	  <?php
	  //connect to the database
	  require ('inc/database_conn.php');

  //query the database
  $sql = "SELECT * FROM misc_order_items ORDER BY description";
  $result = mysql_query($sql, $conn);
  ?>
  <BR><BR>
  <FORM ACTION="misc_order_submit.php" METHOD=POST>
  	<TABLE BORDER="0">
  		<TR>
  			<TD colspan="5">&nbsp;&nbsp;<select name="store">
  				<option value="01">01 - Signal Hill</option>
  				<option value="02">02 - Reseda</option>
  				<option value="03">03 - Orange</option>
  				<option value="04">04 - West Covina</option>
  				<option value="07">07 - Riverside</option>
  				<option value="08">08 - Norwalk</option>
  				<option value="09">09 - Fountain Valley</option>
  				<option value="10">10 - Pasadena</option>
  				<option value="11">11 - South Bay</option>
  				<option value="12">12 - San Bernardino</opion>
  				<option value="13">13 - Kearny Mesa</option>
  				<option value="16">16 - San Marcos</option>
  				<option value="40">40 - Chino</option>
  				<option value="90">90 - Chino Warehouse</option>
  				</select>
  			</TD>
  		</TR>
		<TR>
			<TD width="300"><center><b><u>Description</u></b></center></TD>
			<TD width="60"><center><b><u>Min</u></b></center></TD>
			<TD width="60"><center><b><u>Max</u></b></center></TD>
			<TD width="80"><center><b><u>Pkge Qty</u></b></center></TD>
			<TD width="80"><center><b><u>Order</u></b></center></TD>
		</TR>
	<?php
				while ($row=mysql_fetch_array($result)):
					$id=$row["id"];
					$sku=$row["sku"];
					$description=$row["description"];
					$vpc=$row["vpc"];
					$package_qty=$row["package_qty"];
					$location=$row["location"];
					$min=$row["min"];
					$max=$row["max"];
		echo "<TR>
			<TD>{$description}</TD>
			<TD>{$min}</TD>
			<TD>{$max}</TD>
			<TD>{$package_qty}</TD>
			<TD><input name='order[$i]' type=text></TD>
		</TR>";
			endwhile
	?>
	<TR>
		<TD colspan="5">&nbsp;&nbsp;<input type=submit></TD></TR>
	</TABLE>
	</FORM>

Here is my second file. It does not like the second query line:

<?php
//connect to the database
require('inc/database_conn.php');

//get the variables from the other page
$id=$_POST['id'];
$sku=$_POST['sku'];
$description=$_POST['description'];
$order=$_POST['order'];
$store=$_POST['store'];

//$month=getdate(mon);
//$day=getdate(mday);
//$year=getdate(year);
//$today=$year . "-" . $month . "-" . $day
$today=date('Y-m-d');

// create the unique order number

$headerqry = "INSERT INTO misc_order_header (date, store) VALUES ('$today', '$store')";

echo "First insert okay<BR><BR>";

mysql_query($headerqry, $conn) or die(mysql_error());

$order_no=mysql_insert_id();

echo "Date: {$today}<BR>
	Store: {$store}<BR>
	Order Number: {$order_no}";

$detailqry = "INSERT INTO misc_order_order (order_no, id, sku, order, date, store) VALUES ('$order_no', '$id', '$sku', '$order[$i]', '$today', '$store')";

foreach ($order as $i => $val) {
mysql_query($detailqry, $conn) or die(mysql_error());
}

?>

Thanks for any help you can offer me.

stevesweetland

try

$detailqry = "INSERT INTO misc_order_order (order_no, id, sku, order, date, store) VALUES ('$order_no', '$id', '$sku', '".$order[$i]."', '$today', '$store')";

jeffgman

Nope, that did not work. But, I figured out the problem, I am not sure how to fix it. The problem is with my array.

In the first script, I need to make sure that each $order quantity is tied to the correct $id field. In my second script, when I am inserting the line into the database, my $id field is blank. But, the database is set to not allow NULL values for that field. Here is the error I am getting:

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 'order, date, store) VALUES ('9', '', '', 'Array', '2004-08-03',

As you can see, after the '9' it is blank. But, I need that field to be the $id from the line that the ordered quantity goes with.

Jeff