MYSQL INSERT Loop - With PHP

Question

I am assuming I am going about this is the completely wrong fashion, can anyone assist?

I want to take all the rows with a certain product number out of one table and put it in another table when it sells.

Thanks in advance.

$addinfo1 ="";
    //Connect to the database through our include
include_once "connect_to_mysql.php";
$sql = mysql_query("SELECT * FROM tm_interested_buyers WHERE fk_product_id='$productid");

while($row = mysql_fetch_array($sql)){
$interestedbuyersid = $row["pk_interested_buyers_id"];
$fkproductid = $row["fk_product_id"];
$fkcustomerid = $row["fk_customer_id"];

    $addinfo1 .= mysql_query("INSERT INTO tm_int_buyers_complete (fk_interested_buyers_id, fk_product_id, fk_customer_id) VALUES('$interestedbuyersid','$fkproductid','$fkcustomerid')") or die (mysql_error());
echo $addinfo1;
}

Kyra · Accepted Answer · 2012-06-13 19:23:23Z

1

You just need the one query:

INSERT INTO tm_int_buyers_complete (fk_interested_buyers_id, fk_product_id, fk_customer_id) 
SELECT pk_interested_buyers_id, fk_product_id, fk_customer_id 
   FROM tm_interested_buyers
   WHERE fk_product_id='$productid'

With this the select statement (2nd line and down) is your original select with the values you want specified. This allows all your selected lines to be inserted into your table (top line).

answered Jun 13, 2012 at 19:23

Kyra

5,4775 gold badges39 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ExtremelyPoorMan · Accepted Answer · 2012-06-13 19:28:40Z

0

Use INSERT INTO SELECT.

It is much easier.

INSERT INTO tm_int_buyers_complete (`fk_interested_buyers_id`, `fk_product_id`,
`fk_customer_id`) SELECT tm_interested_buyers.pk_interested_buyers_id,
tm_interested_buyers.fk_product_id, tm_interested_buyers.fk_customer_id
FROM tm_interested_buyers WHERE tm_interested_buyers.fk_product_id='$productid'

answered Jun 13, 2012 at 19:28

ExtremelyPoorMan

14311 bronze badges

Comments

mvladk · Accepted Answer · 2012-06-13 19:37:47Z

0

Use only one query INSERT from SELECT:

INSERT INTO `tm_int_buyers_complete` (fk_interested_buyers_id, fk_product_id,     fk_customer_id)
  SELECT fk_interested_buyers_id, fk_product_id, fk_customer_id
  FROM `tm_interested_buyers` WHERE fk_product_id='$productid";

edited Jun 13, 2012 at 19:37

answered Jun 13, 2012 at 19:24

mvladk

6281 gold badge19 silver badges30 bronze badges

Collectives™ on Stack Overflow

MYSQL INSERT Loop - With PHP

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related