Insert multiple rows into table from a variable in PHP for Mysql & Oracle tables

Question

I am fetching data from some tables & storing it in a variable like below-:

$result = mysql_query("SELECT * FROM sample where column=mysql_insert_id()");
while($row=mysql_fetch_array($result))
{
   $str = "'". $row["name"] . "',". "'" . $row[quantity] . "'," . "'" . $row["id"];
}

So in my variable $str, suppose I have following values-:

 shirt,10,1,pant,50,2....i.e. it will store values in a comma separated format.

Now I want to insert these values in another table say test-:

$qry = "INSERT INTO test(name,quantity,id)values(".$str.");

Now I want to store values in test table in two rows like-:

shirt    10   1
pant     50   2

So how to do the same for Mysql & Oracle tables?

Plz help

See my below query-:

    $query2 = "SELECT sfoi.name, sfoi.sku, sfoi.qty_ordered, sfoi.price, sfoi.row_total, sfo.base_subtotal, sfo.base_shipping_amount, sfo.base_grand_total,
    (select mso.order_primary from mysql_sales_order mso where mso.increment_id =sfo.increment_id) 
    FROM sales_flat_order sfo
    JOIN sales_flat_order_item sfoi
    ON sfoi.order_id = sfo.entity_id
    WHERE sfo.increment_id = ". $order_id ;
$result_query2 = mysql_query($query2);

So for one order id i.e. for one order may contain more than 1 products i.e. many name,sku,quantity ordered etc. So at the time of mysql_fetch_array(), I want all product data in a single variable...my code for fetching is like this-:

while($row = mysql_fetch_array($result_query2))
{
$string = "'". $row["name"] . "',". "'" . $row["sku"] . "'," . "'" . $row["qty_ordered"] . "',". "'" . $row["price"] . "'," . "'" . $row["row_total"] . "'," . "'" . $row["base_subtotal"]. "'," . "'" . $row["base_shipping_amount"] . "'," . "'" . $row["base_grand_total"] ."'," . $row["prod_foreign"];
$query3 = "INSERT into mysql_sales_product(name, sku, qty_ordered, price, row_total, base_subtotal,base_shipping_amount,base_grand_total,prod_foreign) VALUES(".$string.")";

} 
$result_query_product_outbound = mysql_query($query3);

Here I want to store result of mysql_ fetch_array in variable in such a way that if there are multiple rows I can still able to pass those rows using variable$string. e.g-:

name   sku qty_ordered  price  row_total  subtotal shipping_amnt grand_total prod_foreign            
nokia   nk      2        500      1000      1000      300            1300         11
sansung sam     3        400      1200      1200      500            1700         30
sony    sny     4        200       800       800      200             1000         45

In the 1st insert, you have a bug : you should generate a string like "('shirt',10,1),('part',50,2)" — haltabush
– haltabush, Commented Mar 23, 2012 at 7:11

Oğuz Çelikdemir · Accepted Answer · 2012-03-19 11:24:17Z

1

For Oracle, like below :

INSERT ALL
   INTO your_table(column1,column2,column3) VALUES('values1.1', 'values1.2', 'values1.3')
   INTO your_table(column1,column2,column3) VALUES('values2.1', 'values2.2', 'values2.3')
SELECT * FROM dual;

edited Mar 19, 2012 at 11:24

answered Mar 19, 2012 at 11:18

Oğuz Çelikdemir

4,9804 gold badges32 silver badges57 bronze badges

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

3 Comments

Prat Over a year ago

Hi Oguz, How to separate that values as 1.1,1.2,1.3 etc. As they all store in a variable...say suppose there are 20 columns then is it feasible to do the same?

Oğuz Çelikdemir Over a year ago

If I am not mistaken, you used an array for $str variable, then you can use inside a loop like you specified in your code $row["name"]

Oğuz Çelikdemir Over a year ago

of course, you should put other oracle things inside the loop, for instance oci_execute but if you don't want to face of performance problems, prepare insert quieries in an array or string then execute by oci_execute

haltabush · Accepted Answer · 2012-03-19 10:34:41Z

1

Easier than doing a PHP loop, you can do this directly in MySQL. I'm not sure this is an option for you, since you didn't told us what the matter with Oracle...

Anyway, here is what I would do in your case:

INSERT INTO test(name,quantity,id)
SELECT name, quantity, id FROM sample where id=<your id here>

Or, if you have multiple ids,

INSERT INTO test(name,quantity,id)
SELECT name, quantity, id FROM sample where id in (<your ids here as comma separated list>)

edited Mar 19, 2012 at 10:34

answered Mar 19, 2012 at 9:39

haltabush

4,5382 gold badges26 silver badges42 bronze badges

2 Comments

Prat Over a year ago

HI, I need to do the same to insert data in tables for Oracle also, As for inserting into Oracle I am passing variable only. -: Insert into Oracle_table(name,quantity, id) values($str1); etc. but what I need is data must be in separate rows...shoes 50 7-1st row, tie 20 10-2nd row likewise..

haltabush Over a year ago

Basically, you select all from your Mysql DB & insert it into Oracle?

Collectives™ on Stack Overflow

Insert multiple rows into table from a variable in PHP for Mysql & Oracle tables

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related