Multiple insert within a loop

Creates MySQL error

[henry0]

The problem lies in the loop duplicating the insert section
but I need that query to get the different values of $dog_name (for example two rows=2 different names!)
aside dog_name the other values are the same (I mean same values inserted within each row for a same ID)

Note the $dog_name[$counter] in the values.

$result2 = $db->query("
INSERT INTO
aaaaaa
(customer_id, dog_name, productId, cart_order_id, quantity, productCode, time_purchase)

VALUES('$customer_id', '$dog_name[$counter]', '$productId', '$cart_order_id',
'$quantity', '$productCode', '$time_purchase') ");
$counter++;
}

This is the result, obviously stating twice insert cause the problem:
INSERT INTO aaaaaa(customer_id, dog_name, productId, cart_order_id, quantity, productCode, time_purchase) VALUES ('1', 'asasas', '6', '080909-151044-5644', '2', 'two-2', '1220987444')
INSERT INTO aaaaaa(customer_id, dog_name, productId, cart_order_id, quantity, productCode, time_purchase) VALUES ('1', 'zxzxzx', '6', '080909-151044-5644', '2', 'two-2', '1220987444')

[Sekka]

Format your query like this instead,

INSERT INTO aaaaaa 
(customer_id, dog_name, productId, cart_order_id, quantity, productCode, time_purchase) 
VALUES 
('1', 'asasas', '6', '080909-151044-5644', '2', 'two-2', '1220987444'),
('1', 'zxzxzx', '6', '080909-151044-5644', '2', 'two-2', '1220987444');

Note the comma after the values bracket, and the colon after the last one.

[henry0]

Being in a loop as I mentionned you cannot separate
the insert from the values
the insert follows the ++; rule

what you did show will only work in a non dynamic environment, unless I miss your demo

[PHP_Chimp]

Could you not separate out the construction of the query string and sending the query to the database?

Something like:

$queryString = "INSERT INTO aaaaaa (customer_id, dog_name, productId, cart_order_id, quantity, productCode, time_purchase)
VALUES";
for ($i=0;$i<=$counter;$i++) {
if ($i == $counter) { // final VALUES
$queryString.= "('$customer_id', '$dog_name[$i]', '$productId', '$cart_order_id','$quantity', '$productCode', '$time_purchase') ");
}
else { // more VALUES to come
$queryString.= "('$customer_id', '$dog_name[$i]', '$productId', '$cart_order_id','$quantity', '$productCode', '$time_purchase'), ");
}
$result2 = $db->query("$queryString");

As I'm not sure how your counter is working I dont know if this is exactly correct, however hopefully it will give you an idea.

[henry0]

You are correct: In the correct environment.

But you said it "It depends on $counter"
and $counter is set in the script way above so I believe to be correct when I state that even in your solution, since $counter is way above the insert section; this insert section will still be duplicated in direct relation to whatever # is in the counter.

remember that asides $dog_name all other cols will get similar input.
so I tried
<<<

VALUES('$customer_id[$counter]', '$dog_name[$counter]', '$productId[$counter]', '$cart_order_id[$counter]',
'$quantity[$counter]', '$productCode[$counter]', '$time_purchase[$counter]') ");

>>>

as such only dog names are getting their expected values
BUT other fields either don't show or are truncated to 1 char! (which makes sense cause not really related to $counter result- being similar in value they don't need $counter-
hmmmm I might have to tweak something to make believe that those fields (aside $dog_name) are somehow governed by $counter.

I might have a logic problem
but I really do not think so.

Will it help if I paste the script with comment from the beginning of $counter?
thanks

[cameraman]

More script would be helpful. The only thing I notice with what you've posted so far is you really should have braces around your array references:
{$dog_name[$counter]}
I don't know if that's what's causing your trouble - there's something about resolving character reference in scalar string versus array element reference, but I don't know where my 'php pocket reference' is and I never use whatever syntax it is to get a char out of a string (it's either braces or brackets).

[PHP_Chimp]

Did you try my idea?
As the below works fine...with braces around the arrays and a couple of other problems fixed ;)

<?php
$counter = 4;
$customer_id = 1;
$dog_name = array('fido', 'spot', 'jip', 'rex', 'killer');
$productId = '0023456';
$cart_order_id = '0987765';
$quantity = 7;
$productCode = '456789';
$time_purchase = time();
$queryString = "INSERT INTO aaaaaa (customer_id, dog_name, productId, cart_order_id, quantity, productCode, time_purchase)
VALUES";
for ($i=0;$i<=$counter;$i++) {
if ($i == $counter) { // final VALUES
$queryString.= "('$customer_id', '{$dog_name[$i]}', '$productId', '$cart_order_id','$quantity', '$productCode', '$time_purchase') ";
}
else { // more VALUES to come
$queryString.= "('$customer_id', '{$dog_name[$i]}', '$productId', '$cart_order_id','$quantity', '$productCode', '$time_purchase'), ";
}
$queryString.= ')';
}
//$result2 = $db->query("$queryString"); 
echo $queryString;
?>

As the for loop is running around $i not with $counter directly, so each iteration gets more than just a single value.

Again may not be exactly what you need, however may give ideas.

[henry0]

Thanks for the help
let me try a few of your latest suggestions
and I will get back to you with more code excerpt or a victory dance :)

[henry0]

We are getting closer

first for the bad news

Array ( [0] => aaas [1] => xcccx )
Notice: Undefined offset: 2 in ............ on line 227
L 227 is where we use {$dog_name[$i]}

more: although the count is OK it ends by an extra insert with of course empty dog name cause the correct count is 2.

somehow good news :)
as is it inserts OK in the table but inserts also that unexpected 3 insert.

to make it working due to the comma I had to remove the: $queryString.= ')'; line.

INSERT INTO aaaaa(customer_id, dog_name, productId, cart_order_id, quantity, productCode, time_purchase) VALUES('1', 'aaas', '6', '080909-151044-5644','2', 'two-2', '1220987444'), ('1', 'xcccx', '6', '080909-151044-5644','2', 'two-2', '1220987444'), ('1', '', '6', '080909-151044-5644','2', 'two-2', '1220987444')

///////////////////////////////////////////////////////////
// print_r($dog_name); print correct expectations
///////////////////////////////////////////////////////////
//echo"counter $counter<P>"; // echo correct expectations!
$queryString = "INSERT INTO aaaaa (customer_id, dog_name, productId, cart_order_id, quantity, productCode, time_purchase)
VALUES";
for ($i=0;$i<=$counter;$i++) {
if ($i == $counter) { // final VALUES
$queryString.= "('$customer_id', '{$dog_name[$i]}', '$productId', '$cart_order_id','$quantity', '$productCode', '$time_purchase') ";
}
else { // more VALUES to come
$queryString.= "('$customer_id', '{$dog_name[$i]}', '$productId', '$cart_order_id','$quantity', '$productCode', '$time_purchase'), ";
}
//$queryString.= ';
}
echo $queryString;

$result2 = $db->query("$queryString");

[henry0]

VICTORY DANCE :)

It took me a good deal of focus to figure something obvious:
The cause of that extra insert and offset error was due to a simple fact, what do we do in the first section? Well we iterate once so the rest is counter minus 1, this and the comma fix did it.
et voila

thanks again

$queryString = "INSERT INTO aaaaa (customer_id, dog_name, productId, cart_order_id, quantity, productCode, time_purchase) 
VALUES"; 
for ($i=0;$i<=$counter;$i++) { 
if ($i == $counter) { // final VALUES 
$queryString.= "('$customer_id', '$dog_name[$i]', '$productId', '$cart_order_id','$quantity', '$productCode', '$time_purchase') "; 
} 
elseif ($i != $counter) { // more VALUES to come 
$counter=$counter-1;
$queryString.= "('$customer_id', '$dog_name[$i]', '$productId', '$cart_order_id','$quantity', '$productCode', '$time_purchase'), "; 
} 
} 
$result2 = $db->query("$queryString"); 
echo $queryString;