PHP divide multidimensional array into two part with comparing another array

[thara]

I have two multidimensional arrays as follows:

Array 01 for new prices of items:

Array
(
  [42] => Array
    (
        [0] => 110
        [1] => 0.00
    )

  [41] => Array
    (
        [0] => 80
        [1] => 70.00
    )

  [43] => Array
    (
        [0] => 70
        [1] => 60
    )

  [40] => Array
    (
        [0] => 90
        [1] => 80
    )

)
1

Array 02 for old prices of items:  

Array
(
  [42] => Array
    (
        [sales_price] => 100.00
        [our_price] => 0.00
    )

  [41] => Array
    (
        [sales_price] => 80.00
        [our_price] => 0.00
    )
)
1

Array key of both array are item ids (42,41,43,40).
Now I need to compare $new and $old arrays to create update and insert queries.

If the values in the new array are different from those in the old one, the table should be updated. If there are more elements in the new array than in the old array, they should be identified for the insert query.

So basically I want to divide new array into two parts with comparing  old array and considering the terms above.

Expecting two arrays should be as follows:

Array
(
  [42] => Array
    (
        [0] => 110
        [1] => 0.00
    )

  [41] => Array
    (
        [0] => 80
        [1] => 70.00
    )
)
1

Array
(
  [43] => Array
    (
        [0] => 70
        [1] => 60
    )

  [40] => Array
    (
        [0] => 90
        [1] => 80
    )

)
1

The Code I have so far:

foreach ($priceNew as $newIds => $newPrices) {
  foreach($priceOld as $oldIds => $oldPrices) {

  }
}

 

[Barand]

I'll attempt to describe this process as I see it. There will be some assumptions here but this is my best guess.

• You have a table of prices which you are querying and creating an array.
• Similarly, you have new prices which you are loading into a second array.
• You then compare these two arrays and split the contents into 2 new arrays (updates & inserts)
• You then loop through these new arrays updating and inserting records into the prices table.

Have you considered the simple approach, namely SQL.

price (before)                                      new_price
+-----+-----------------+-----------------+         +-----+-----------------+-----------------+
| id  | sales_price     | our_price       |         | id  | sales_price     | our_price       |
+-----+-----------------+-----------------+         +-----+-----------------+-----------------+
| 41  |     80.00       |     0.00        |         | 40  |     90.00       |     80.00       |
| 42  |    100.00       |     0.00        |         | 41  |     80.00       |     70.00       |
+-----+-----------------+-----------------+         | 42  |    110.00       |      0.00       |
                                                    | 43  |     70.00       |     60.00       |
                                                    +-----+-----------------+-----------------+
                                                    
                                                    
INSERT INTO price(id, sales_price, our_price)
    SELECT id, sales_price, our_price
    FROM new_price
ON DUPLICATE KEY UPDATE
    sales_price = VALUES(sales_price),
    our_price = VALUES(our_price);                                                    
                                                    

price (after)                                                    
+----+-------------+-----------+
| id | sales_price | our_price |
+----+-------------+-----------+
| 40 |       90.00 |     80.00 |
| 41 |       80.00 |     70.00 |
| 42 |      110.00 |      0.00 |
| 43 |       70.00 |     60.00 |
+----+-------------+-----------+ 

 

[thara]

Sir, Yes in that case, your gess is correct. But in my case I can't use "ON DUPLICATE KEY UPDATE" query for price table. Since every price have a date range.

+---------------+-------------+-----------+---------------------+-------------+---------+
| item_price_id | sales_price | our_price | valid_from          | valid_until | item_id |
+---------------+-------------+-----------+---------------------+-------------+---------+
|             1 |      100.00 |     50.00 | 2022-04-26 15:50:36 | NULL        |      42 |
|             2 |      120.00 |     50.00 | 2022-04-26 15:50:36 | NULL        |      41 |
|             3 |      130.00 |      0.00 | 2022-04-26 15:50:36 | NULL        |      43 |
|             4 |       40.00 |      0.00 | 2022-04-26 15:50:36 | NULL        |      40 |
|             5 |       55.00 |      0.00 | 2022-04-26 15:50:36 | NULL        |      38 |
|             6 |       30.00 |      0.00 | 2022-04-26 15:50:36 | NULL        |      39 |
+---------------+-------------+-----------+---------------------+-------------+---------+

How I create my two array?

One create from POST array as follows:

  // Sales Prices from POST array:
  $sp = $_POST['salesPrice'];

  // Remove 0.00 prices from array:
  foreach ($sp as $k => $v) {
    if ($sp[$k] == 0) {
      unset($sp[$k]);
    }
  }

  // Rearrange the POST array to access both prices in one place:
  foreach ($sp as $key => $value) {
    $newPrices[$key] = [$value, $_POST['ourPrice'][$key]];  
  }

Other one is creating from SELECT query as follows:

  // Create new array from item_id:
  foreach ($sp as $iid => $sPrice) {
    $itemIDs[] = $iid;
  }

  // Select Prices from DB:
  $in = join(',', array_fill(0, count($itemIDs), '?'));

  $sql = "SELECT item_id,sales_price,our_price FROM item_price
          WHERE NOW() BETWEEN valid_from AND COALESCE(valid_until, '9999-12-31 00:00:00')
                AND item_id IN ($in)";
  $stmt = $pdo->prepare($sql);
  $stmt->execute($itemIDs);  
  $oldPrices = $stmt->fetchAll(PDO::FETCH_UNIQUE);

Now I want execute insert / update queries comparing these two arrays..

 

 

[Barand]

Try this for size.

(Note that my item_price table uses DATE types and not DATETIME. If you are using DATETIME, use NOW() where I have used CURDATE() and use "- INTERVAL 1 SECOND" where I have used "- INTERVAL 1 DAY" so the previous price ends just before the new one comes into effect)

Starting with the current item_price table

item_price
+---------------+-------------+-----------+------------+-------------+---------+
| item_price_id | sales_price | our_price | valid_from | valid_until | item_id |
+---------------+-------------+-----------+------------+-------------+---------+
|            30 |      175.00 |      0.00 | 2021-08-08 | 2021-08-07  |      30 |
|            79 |      175.00 |      0.00 | 2021-08-08 | NULL        |      30 |
|            31 |      100.00 |      0.00 | 2021-08-08 | NULL        |      31 |
|            86 |      105.00 |      0.00 | 2022-04-09 | NULL        |      31 |
|            32 |       65.00 |      0.00 | 2021-08-08 | NULL        |      32 |
|            33 |       65.00 |      0.00 | 2021-08-08 | NULL        |      33 |
|            34 |       75.00 |      0.00 | 2021-08-08 | NULL        |      34 |
|            35 |       85.00 |      0.00 | 2021-08-08 | NULL        |      35 |
+---------------+-------------+-----------+------------+-------------+---------+

Write you POST data to a temporary table (new_price) instead of to an array

new_price
+---------+-------------+-----------+
| item_id | sales_price | our_price |
+---------+-------------+-----------+
|      30 |      180.00 |      0.00 |   changed
|      34 |       75.00 |      0.00 |   no change
|      40 |       90.00 |     80.00 |   new
|      41 |       80.00 |     70.00 |   new
|      42 |      110.00 |      0.00 |   new
|      43 |       70.00 |     60.00 |   new
+---------+-------------+-----------+

If the new prices are the same as those that are still current then they can be ignored, so we can remove those, creating a separate temporary table (changed_price) which will be used to update the item_price table

CREATE TEMPORARY TABLE changed_price
    SELECT n.item_id
         , n.sales_price
         , n.our_price
         , CURDATE() as valid_from
    FROM new_price n 
         LEFT JOIN
         item_price p ON n.item_id = p.item_id
                      AND CURDATE() BETWEEN p.valid_from AND coalesce(p.valid_until, '9999=12-31')
                      AND n.sales_price = p.sales_price
                      AND n.our_price = p.our_price
    WHERE p.item_id IS NULL;
    
changed_price
+---------+-------------+-----------+------------+
| item_id | sales_price | our_price | valid_from |
+---------+-------------+-----------+------------+
|      30 |      180.00 |      0.00 | 2022-04-27 |
|      40 |       90.00 |     80.00 | 2022-04-27 |
|      41 |       80.00 |     70.00 | 2022-04-27 |
|      42 |      110.00 |      0.00 | 2022-04-27 |
|      43 |       70.00 |     60.00 | 2022-04-27 |
+---------+-------------+-----------+------------+

Update the item price table setting the valid until to just before our valid from for the new price

UPDATE item_price p
       JOIN
       changed_price c ON p.item_id = c.item_id
                       AND c.valid_from BETWEEN p.valid_from AND coalesce(p.valid_until, '9999-12-31')
    SET p.valid_until = c.valid_from - INTERVAL 1 DAY;

then add the new prices to the item price table

INSERT INTO item_price(item_id, sales_price, our_price, valid_from) 
    SELECT * FROM changed_price;                                             

Our resulting ite price table is now

+---------------+-------------+-----------+------------+-------------+---------+
| item_price_id | sales_price | our_price | valid_from | valid_until | item_id |
+---------------+-------------+-----------+------------+-------------+---------+
|            30 |      175.00 |      0.00 | 2021-08-08 | 2021-08-07  |      30 |
|            79 |      175.00 |      0.00 | 2021-08-08 | 2022-04-25  |      30 |     **  updated
|           120 |      180.00 |      0.00 | 2022-04-26 | NULL        |      30 |     **  new
|            31 |      100.00 |      0.00 | 2021-08-08 | NULL        |      31 |
|            86 |      105.00 |      0.00 | 2022-04-09 | NULL        |      31 |
|            32 |       65.00 |      0.00 | 2021-08-08 | NULL        |      32 |
|            33 |       65.00 |      0.00 | 2021-08-08 | NULL        |      33 |
|            34 |       75.00 |      0.00 | 2021-08-08 | NULL        |      34 |
|            35 |       85.00 |      0.00 | 2021-08-08 | NULL        |      35 |
|           121 |       90.00 |     80.00 | 2022-04-26 | NULL        |      40 |     **  new
|           122 |       80.00 |     70.00 | 2022-04-26 | NULL        |      41 |     **  new
|           123 |      110.00 |      0.00 | 2022-04-26 | NULL        |      42 |     **  new
|           124 |       70.00 |     60.00 | 2022-04-26 | NULL        |      43 |     **  new
+---------------+-------------+-----------+------------+-------------+---------+

This should execute far faster than creating and manipulating arrays then looping through those arrays to run queries.

[thara]

Thank you, Sir!

This is what I was trying to achieve as alternative. I modified your code a bit to output the desired results and all works perfectly now.

4 hours ago, Barand said:

This should execute far faster than creating and manipulating arrays then looping through those arrays to run queries.

Yes, the main reason for updating this script was to reduce its execution time. The method you suggested works ten times faster than my version made using arrays and loops. For thousands of records, that process was simply very slow.

Again, Thank you very much, Sir!