updating table1 then table2 issues

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jvblue
Forum Newbie
Posts: 1
Joined: Thu Jun 10, 2010 6:46 pm

updating table1 then table2 issues

Post 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.!@!
Last edited by Benjamin on Thu Jun 10, 2010 7:53 pm, edited 1 time in total.
Reason: Added [syntax=php] tags.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: updating table1 then table2 issues

Post 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.
Post Reply