Using PHP to INSERT multiple rows into mysql DB

Question

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?

**Your code is vulnerable to SQL injection.** You *really* should be using [prepared statements](http://stackoverflow.com/a/60496/623041), 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](http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain). — eggyal, Sep 04 '12 at 15:33
Also, as stated in [the introduction](http://www.php.net/manual/en/intro.mysql.php) to the PHP manual chapter on the `mysql_*` functions: *This extension is not recommended for writing new code. Instead, either the [mysqli](http://www.php.net/manual/en/book.mysqli.php) or [PDO_MySQL](http://www.php.net/manual/en/ref.pdo-mysql.php) extension should be used. See also the [MySQL API Overview](http://www.php.net/manual/en/mysqlinfo.api.choosing.php) for further help while choosing a MySQL API.* — eggyal, Sep 04 '12 at 15:34
Learn about [SQL joins](http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html) and consider using [`INSERT ... SELECT`](http://dev.mysql.com/doc/en/insert-select.html). — eggyal, Sep 04 '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 04 '12 at 15:44

score 1 · Answer 1 · edited Sep 04 '12 at 15:39

1

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!

edited Sep 04 '12 at 15:39

tadman

208,517
23
234
262

answered Sep 04 '12 at 15:38

Alexei

672
1
5
13

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 04 '12 at 15:56
The short answer is to do lots of `implode`s: `$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?](http://stackoverflow.com/questions/108699/good-php-orm-library) – Alexei Sep 04 '12 at 18:29

Using PHP to INSERT multiple rows into mysql DB

1 Answers1