Compare array values with mysql database values with PDO

Question

I cannot compare the values of this array

 $products = [];
foreach (array_keys($match[2]) as $idx)
{
    $tagliaprodotto = rtrim(substr($match[2][$idx],1));
    $tagliaquantita = ltrim($match[3][$idx],'0');
    $products[] =   [
        'product'  => $tagliaprodotto,
        'quantity'  => $tagliaquantita,
        'terminal' => $match[4][$idx]
                    ];
}

With the values inside the database! Should I do that?

I would like to compare the elements that I have inside the database with array's value and make some if as:

-If product is not present in the database:

INSERT INTO table (product, quantity, terminal, date) VALUES (:product, :quantity, :terminal, NOW ())

-If a database record matches perfectly: product, quantity and terminal: Do nothing

-If a database record contains: same product but different quantity and / or terminal :

UPDATE table SET quantity=:quantity, terminal=:terminal

https://stackoverflow.com/questions/23305300/check-if-username-exists-pdo — Shafiqul Islam, Oct 17 '17 at 12:51
yes but i don't know how! because it is an other array! and i don't know how to compare the same product and it's record — Squalo, Oct 17 '17 at 13:27

Spencer · Answer 1 · 2017-10-17T13:59:36.043

1

I'm assuming you index on product, otherwise this gets stickier. Use a combination of ON DUPLICATE KEY and IF():

INSERT INTO table (product, quantity, terminal, date)
    VALUES (:product, :quantity, :terminal, NOW())
ON DUPLICATE KEY UPDATE
    quantity = IF(quantity != :quantity AND terminal = :terminal, :quantity, quantity),
    terminal = IF(quantity = :quantity AND terminal != :terminal, :terminal, terminal)

If there's not an index on product (or any of the specified columns), the UPDATE will never fire. If there are indices on multiple columns in the query, there will be issues where rows you don't intend to update get updated, especially if the indices are non-unique.

edited Oct 17 '17 at 13:59

answered Oct 17 '17 at 13:53

Spencer

297
2
10

thanks but i need to update also the date but only when quantity or terminal change or when product doesn't exists – – Squalo Oct 17 '17 at 14:16
That's a relatively easy addition to the query in the update section which you should be able to add. It'll still use `IF()` and look similar to the quantity and terminal settings, but a bit longer. Give it a shot, and if you still need help I'll walk you through it. – Spencer Oct 17 '17 at 18:07
One answer: 1 if quantity is different and terminal is different it doesn't work it's right? – Squalo Oct 18 '17 at 06:09

Compare array values with mysql database values with PDO

1 Answers1