Creating MySQL Insert statement from Simplecart array

Question

I have implemented Simplecart and am passing the contents of the shopping cart to another page where i want to save the product identifier to a MySQL database table along with the quantity and a unique identifier so i can pull the products and quantities requested based on that identifier. Simplecart is sending the following information to my page..

Array ( [currency] => USD [shipping] => 0 [tax] => 0 [taxRate] => 0 [itemCount] => 5 
[item_name_1] => ProductA [item_quantity_1] => 1 [item_options_1] => identifier: 0057 
[item_name_2] => ProductB [item_quantity_2] => 3 [item_options_2] => identifier: 0024 
[item_name_3] => ProductC [item_quantity_3] => 1 [item_options_3] => identifier: 0059 
[item_name_4] => ProductD [item_quantity_4] => 1 [item_options_4] => identifier: 0106 
[item_name_5] => ProductE [item_quantity_5] => 1 [item_options_5] => identifier: 1031 )

I cobbled together various scripts i saw here to generate the sql statement which prints like this

insert into products values (51ca3d6e580c6,1,identifier: 0057)
insert into products values (51ca3d6e580c6,3,identifier: 0024)
insert into products values (51ca3d6e580c6,1,identifier: 0059)
insert into products values (51ca3d6e580c6,1,identifier: 0106)
insert into products values (51ca3d6e580c6,1,identifier: 1031)

This at least told me that I had created a loop that populated the right data each time, but when i try to insert the record as it loops each time, I get only the last iteration. I also do not understand how I can explode the Item_Options variable in the loop.

The kludged code is below

$content = $_POST;
$item_number = array();
$item = array(); 

for($i=1; $i < $content['itemCount'] + 1; $i++) 
{
$name = 'item_name_'.$i;
$quantity = 'item_quantity_'.$i;
$options = 'item_options_'.$i;
$item_number['total'] = $i;

$item[$i]['name'] = $content[$name];
$item[$i]['quantity'] = $content[$quantity];
$item[$i]['options'] = $content[$options]; 
}  

$total = $item_number['total'];
$line = 0;

while ($line <= $total -1) 
{
$line++;
$statement = $myid . "," . $item[$line]['quantity'] . "," . $item[$line]['options'];
$sql = "insert into products values ($statement)"; 

$result=mysql_query($sql);

}
mysql_close();

So, my questions really are, how do I add the unique ID, the quantity and the (exploded) identifier into MySQL creating a new record everytime it loops?

Thanks Michael, i wasn't sure how yo add a long comment here so i asked another question below... — user2522177, Jun 26 '13 at 10:15

Sean · Accepted Answer · 2013-06-27T01:32:19.267

try -

while ($line <= $total -1){
    $line++;
    $statement[] = "(".$myid . "," . $item[$line]['quantity'] . "," . $item[$line]['options'].")";
}

$sql = "insert into products values ".implode(",",$statement);
$result=mysql_query($sql)

This creates an array of $statement with values like

(id,qty,product)

and then on the implode() the query becomes -

insert into products values (id,qty,product),(id,qty,product),...

edit

based off your edit, try -

for($i=1; $i < $content['itemCount'] + 1; $i++){
    $quantity = 'item_quantity_'.$i;
    $options = 'item_options_'.$i;
    $item_number['total'] = $i;

    $exploded = explode(' ', $content[$options]);  // need to use the $content[] here
    $item_id = $exploded[1];

    $item[$i]['quantity'] = $content[$quantity];
    $item[$i]['options'] = $item_id;   // use the exploded value, not the $content[]
}  

$total = $item_number['total'];
$line = 0;

while ($line <= $total -1){
    $line++;
    $statement[] = "('".$myid . "','" . $item[$line]['quantity'] . "','" . $item[$line]['options'] ."')";
}

Creating MySQL Insert statement from Simplecart array

1 Answers1