Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I am trying to insert data into a table, and the data is drawn from another table. At the moment my code looks like this:

$result3 = mysql_query('SELECT order_no 
                    FROM orders 
                    WHERE ord_date = "' . ($_POST["ord_date"]) . '"');

while($row=mysql_fetch_array($result3)){ $order=$row['order_no'];}

$result4 = mysql_query('SELECT door_product_no 
                    FROM estimateDescribesDoorProduct 
                    WHERE estimate_no = "' . ($_GET["estimate_no"]) . '"');

while($row=mysql_fetch_array($result4)){ $door=$row['door_product_no'];}

$result5 = mysql_query('SELECT quantity 
                    FROM estimateDescribesDoorProduct 
                    WHERE estimate_no = "' . ($_GET["estimate_no"]) . '"');

while($row=mysql_fetch_array($result5)){ $dquantity=$row['quantity'];}


$sql2="INSERT INTO orderConsistsOfDoor (order_no, door_product_no, product_quantity)

VALUES ('$order','$door','$dquantity')";

I used this method yesterday thanks to some advice on this site. My problem today is that I need to insert multiple rows. The tables 'orderConsistsOfDoor' and 'estimateDescribesDoorProduct' are identical except that for the first column (order_no/estimate_no). Basically if an estimate (or order) consists of e.g. 3 products, then there will be 3 rows in the table with that estimate_no (but different product_no and quantity).

I think that the code I have will only insert one row into orderConsistsOfDoor, but I need it to insert every row where the estimate_no is ($_GET["estimate_no"]). I think this can be done with foreach or something but I've never used this and don't know how it works.

Can somebody help me?

share|improve this question
4  
Your code is vulnerable to SQL injection. You really should be using prepared statements, into which you pass your variables as parameters that do not get evaluated for SQL. If you don't know what I'm talking about, or how to fix it, read the story of Bobby Tables. – eggyal Sep 4 '12 at 15:33
2  
Also, as stated in the introduction to the PHP manual chapter on the mysql_* functions: This extension is not recommended for writing new code. Instead, either the mysqli or PDO_MySQL extension should be used. See also the MySQL API Overview for further help while choosing a MySQL API. – eggyal Sep 4 '12 at 15:34
Learn about SQL joins and consider using INSERT ... SELECT. – eggyal Sep 4 '12 at 15:38
The advice you were given was terrible. You must use SQL placeholders to do your data escaping or you will suffer severe consequences. mysqli and PDO both provide easy facilities for this and there is no excuse to not use them. It takes all of half an hour to learn how to use PDO and you should make it a priority to figure that out now before you create even more problems. – tadman Sep 4 '12 at 15:44

1 Answer

up vote 1 down vote

To insert multiple records with one query, you can do:

INSERT INTO `table_name` (`foo`, `bar`) VALUES (1, 2), (3, 4), (5, 6);

See INSERT Syntax

You should use a library, though, it's 2012!

share|improve this answer
But how do I use this to insert as many rows as necessary (i.e. as many rows as there are with a given estimate_no)? – user1620419 Sep 4 '12 at 15:56
The short answer is to do lots of implodes: $record_set = array(array(1, 2), array(3, 4), array(5, 6)); $values = array(); foreach ($record_set as $record) { $values[] = '('. implode(', ', $record) .')'; } $query = 'INSERT INTO table_name (foo, bar) VALUES '. implode(', ', $values); echo $query; But, like I said, you should use a library that does that sort of stuff for you (and most importantly, escapes your data!). I use my own, but you could try libraries such as Doctrine or Pork. See Good PHP ORM Library? – Alexei Sep 4 '12 at 18:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.