Looping through array insert if id

Question

I had my first question answered here:link

My extended question is, I can successfully update rows into the database but for some reason I cannot insert if the itemid = 0? code below, If I do it adds a further 8 rows?:

for ($i = 0; $i < count($values); $i++)
{
$item           = $values['item'][$i];
$description    = $values['description'][$i];
$rate           = $values['rate'][$i];
$itemid         = $values['itemid'][$i];

if($itemid)
{
    // Update item
    $query = "UPDATE `invoice_items`...";
}
else
{
    // Add new item
    $query = "INSERT INTO `invoice_items`...';
}

}

#

Update

#

Here is what I have now, But I am still inserting an additional 7 items to the database when I am only updating the item:

foreach ($outArray as $row)
{
$item           = $row['item'];
$description    = $row['description'];
$rate           = $row['rate'];
$qty            = $row['qty'];
$price          = $row['price'];
$itemid         = $row['itemid'];

if($row['itemid'] >= 1)
{
    $query = "UPDATE `invoice_items` SET `item` = '?', `description` = '?', `rate` = '?', `qty` = '?', `price` = '?' WHERE `id` = '?' LIMIT 1;";
    $query_prepare = $this->prep_q($query,array($item,$description,$rate,$qty,$price,$itemid));
}
else
{
    $query = "INSERT INTO `invoice_items` (`item`,`invoice_id`,`description`,`rate`,`qty`,`price`) VALUES ('?','?','?','?','?','?') LIMIT 1;";
    $query_prepare = $this->prep_q($query,array($item,$invoice_id,$description,$rate,$qty,$price));
}

$this->setquery($query_prepare);
}

What are the data types set for the DB columns? Do a 'desc invoice_items' SQL query. — Stegrex
– Stegrex, Commented Jul 19, 2012 at 6:13

Lawrence Cherone · Accepted Answer · 2012-07-19 06:43:30Z

0

Also you should create a workable array, each row has its own name, description, rate, itemid.

So instead of:

Array
(
[item] => Array
    (
        [0] => item listing 1
        [1] => item listing 2
    )

[description] => Array
    (
        [0] => item testing description
        [1] => item testing description
    )

[rate] => Array
    (
        [0] => 1.00
        [1] => 2.00
    )

[itemid] => Array
    (
        [0] => 1
        [1] => 2
    )
)

Your array should look like:

Array
(
[0] => Array
(
        [item] => item listing 1
        [description] => item testing description
        [rate] => 1.00
        [itemid] => 1
)
[1] => Array
(
        [item] => item listing 2
        [description] => item testing description
        [rate] => 2.00
        [itemid] => 2
)

Then you can simply use foreach, and PDO or mysqli:

$sql['update'] = "UPDATE `invoice_items`
                  SET `item` = :item, `description` = :description, `rate` = :rate
                  WHERE `itemid` = :itemid";

$sql['insert'] = "INSERT INTO `invoice_items` (itemid,item,description,rate)
                  VALUES (:itemid, :item, :description,:rate)";

foreach ($array as $row){
    if($row['itemid'] >= 1){
        $stmt = $db->prepare($sql['update']);
    }else{
        $stmt = $db->prepare($sql['insert']);
    }
    $stmt->execute($row);
}

answered Jul 19, 2012 at 6:43

Lawrence Cherone

46.7k7 gold badges66 silver badges107 bronze badges

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

3 Comments

neoszion Over a year ago

Thanks for this. I apologies for my lack of understanding of array() but how would you suggest I 'create a workable array'? Thanks again for your reply.

neoszion Over a year ago

Any ideas? my apologies for the quick bump!

neoszion Over a year ago

I managed to get help with regards to the array. but I am still submittin an additional items with the insert: I will update my post.

Collectives™ on Stack Overflow

Looping through array insert if id

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related