Page 1 of 1

updating table1 then table2 issues

Posted: Thu Jun 10, 2010 6:51 pm
by jvblue
I am new to PHP and i am having some difficulties hitting table1 then table2.

I have checked many forums and still cant resolve this issue.. here is my code...


Here is my 1st table and i am inserting the 'id'..... I need this id to populate the 'OrderNumber' field in Table 2..
I think it should be something simple, but with my limited knowledge i am not able to get it to connect without error..

Code: Select all

$query= "insert into orders(id, dt) VALUES ('NULL','".$DT."')";
mysql_query ($query) or die ('1st query');

for ( $counter = 1; $counter <= 4; $counter += 1) {
	if ($_POST["qty".$counter] > 0){
	$query="INSERT INTO item(uID, OrderNumber, item_sku, qty, DT,)VALUES 
('".$RouteNumber."','NULL','".$_POST ["SKU".$counter]."','".$_POST ["qty".$counter]."','".$DT."')";
mysql_query ($query) or die ('Error updating database');
}}

Thanks in Advance.!@!

Re: updating table1 then table2 issues

Posted: Thu Jun 10, 2010 8:36 pm
by califdon
There are quite a few issues there, not surprising for a new PHP programmer. Are you trying to enter a new order and its items? Why would you ever enter the string "NULL" as the order number? If you entered a second order, you would lose all the relationships! The customary approach is to assign the order number BEFORE you perform ANY database operations, then you can use the order number to relate the items to their order record (which I assume will have other fields, such as customer, etc.). A relational database should never be left, even momentarily, in an indeterminate state, such as missing the main identifier. Oh, as I re-read your code, are you using an Auto-increment field for the order number in the orders table?? If so, that's a different issue. You would omit that field from your first INSERT, then immediately after the insertion, determine what value had been assigned, using the PHP function mysql_insert_id(). See http://php.net/manual/en/function.mysql-insert-id.php.

Another issue is that raw data from a $_POST value should never be entered directly into a database, without validation, at least using the PHP function mysql_real_escape_string(), which affords some protection from malicious hackers that could destroy your database and even delete files on the server. Read about "SQL injection" such as here: http://www.tizag.com/mysqlTutorial/mysq ... ection.php. In other words, never use $_POST['xxxx'] within a SQL statement, always process it first.

Then, make your code easier to read and take advantage of the PHP feature that $variables are expanded within double-quoted strings. Instead of

Code: Select all

"... VALUES ('".$RouteNumber."',  ...
use this

Code: Select all

"... VALUES ('$RouteNumber',  ...
Note that you DO have to use concatenation for arrays, functions, etc.