PHP/Mysql confuse in working with dates

[thara]

Hi All,

I have some issues when I trying to insert/update data into mysql through php. Hoping someone may help me out.

This is how I store data in my table:

select * from offer;
+----------+---------------+---------------------+---------------------+-----------------------------+------+
| offer_id | offer_type_id | valid_from          | valid_until         | terms                       | name |
+----------+---------------+---------------------+---------------------+-----------------------------+------+
|        1 |             1 | 2022-04-29 21:26:38 | 2022-04-30 21:28:09 | {"qty": 4, "total": 260.00} | aaa  |
|        2 |             1 | 2022-04-30 21:26:38 | 2022-05-25 23:59:59 | {"qty": 2, "total": 150.00} | bbb  |
|        3 |             1 | 2022-05-10 21:26:38 | 2022-05-20 23:59:59 | {"qty": 3, "total": 200.00} | ccc  |
+----------+---------------+---------------------+---------------------+-----------------------------+------+

This is how it look my POST array: (not for the data in the table shown above)

Array
(
    [offerName] => Array
        (
            [34] => aaa
            [35] => bbb
            [36] => ccc
        )

    [quantity] => Array
        (
            [34] => 3
            [35] => 2
            [36] => 4
        )

    [offerPrice] => Array
        (
            [34] => 200
            [35] => 150
            [36] => 250
        )

    [validFrom] => Array
        (
            [34] => 2022-04-29
            [35] => 2022-04-30
            [36] => 2022-05-01
        )

    [validUntil] => Array
        (
            [34] => 2022-04-30
            [35] => 2022-05-05
            [36] => 2022-05-20
        )

    [offer_type] => multisellTotal
)

Insert/Update conditions:

If the "validFrom" date in POST array overlaps the previous offer in the table, then previous offer record need to be updated as following.

valid_until = (validFrom - 1 second) and "validFrom" date of new offer = (validFrom H:i:s) (H:i:s is the time when the script is executed.)

If the "validFrom" date in POST array equal to "curdate()", then previous offer record need to be updated as following.

valid_until = (now() - 1 second) and "validFrom" date of new offer = now();

If the "validFrom" date in POST array is not overlaps the previous offer and new offer have future dates, then previous offer record updation should be ignore, and new offer records need to be inserted with relavant "validFrom" and "validUntil" dates. when inserting "validFrom" time should be "00:00:00" and "validUntil" time shold be "23:59:59"

This is how I tried it. But it doesn't work as I needed.

$d1 = new DateTime();
$d2 = clone $d1;
$d2->modify("-1 second");

$today     = $d1->format('Y-m-d H:i:s');
$yesterday = $d2->format('Y-m-d H:i:s');


$offerType = isset($_POST['offer_type']) ? $_POST['offer_type'] : '';

$prvOferUpdt = $newOferUpdt = [];

if ($offerType != '' && $offerType == 'multisellTotal') {

  foreach ($_POST['validUntil'] as $key => $endDate) {
    if ($endDate < $_POST['validFrom'][$key]) {
      $messages = array('success'=>false, 'message'=>'Start date should be less than to End date.');
      echo json_encode($messages);
      return;
    }

    $start_date[$key] = $_POST['validFrom'][$key];
    $end_date[$key]   = $_POST['validUntil'][$key];
  }


  $sql = "SELECT offer_id FROM offer o
            JOIN offer_item i USING(offer_id)
          WHERE i.item_id = ?
                AND ? BETWEEN valid_from AND valid_until
                AND o.offer_id = i.offer_id";
  $stmt = $pdo->prepare($sql);
  $i = 1;
  foreach ($start_date as $id => $sdate) {
    $stmt->execute([$id, $sdate]);
    $offerID = $stmt->fetch();

    // If offer already available and new offer's start date is still
    // in between previous offer's date_from and date_valid:
    // then, first closed the old offer and set its date_valid for NOW()-1 second
    // and set to new offer's date from to NOW():

    // Create array to store offer_id and valid_until date which
    // is need to be updated in previous offer:
    $oferStart1 = DateTime::createFromFormat( "Y-m-d", $sdate);
    $oferStart2 = DateTime::createFromFormat( "Y-m-d", $sdate);
    $oferStart2->modify("-1 second");

    if (isset($offerID['offer_id'])) {

      $diff      = $d1->diff($oferStart1);
      $diffDays = (integer)$diff->format("%R%a");

      if ($diffDays == 0) {
        $prvOferUpdt[$offerID['offer_id']] = $yesterday;  
        $newOferUpdt[$id] = $today;  
      } else if ($diffDays > 0) {
        $prvOferUpdt[$offerID['offer_id']] = $oferStart2->format("Y-m-d H:i:s");  
        $newOferUpdt[$offerID['offer_id']] = $oferStart1->format("Y-m-d H:i:s");  
      }
    } else {

      // IF offer start date is a future Date:
      // $prvOferUpdt[$i] = $oferStart1->format("Y-m-d H:i:s");  
      $newOferUpdt[$i] = $oferStart1->format("Y-m-d H:i:s");  

    }

    $i++;
  }

  // Insert new offers into the DB:    
  $query = "INSERT INTO offer ( offer_type_id
                              , valid_from
                              , valid_until
                              , terms
                              , name
                              ) VALUES
                              (1,?,?,?,?)";
  $stmt = $pdo->prepare($query);
 
  $offerIDs = [];
  ksort($_POST['offerPrice']);

  foreach ($_POST['offerPrice'] as $id => $offerPrice) {
    
    //$offerPrice = floatval($offerPrice);
    if(!empty($offerPrice) && $_POST['validFrom'][$id] != '') {
      
      $quantity   =  intval($_POST['quantity'][$id]);
      
      $offerPrice =  floatval($offerPrice);
      $offerPrice =  number_format($offerPrice,2, '.', '');
      
      $validFrom  =  date('Y-m-d H:i:s', strtotime($_POST['validFrom'][$id]));
      
      $validUntil =  $_POST['validUntil'][$id];
      $validUntil =  ($validUntil != '' AND $validUntil != NULL) ? date('Y-m-d 23:59:59', strtotime($validUntil)) : '9999-12-31 23:59:59';
      
      $offerName  =  (!empty($_POST['offerName'][$id])) ? filter_var($_POST['offerName'][$id], FILTER_SANITIZE_STRING) : 'Buy and pay a fixed price';

      $terms      =  "{\"qty\": $quantity, \"total\": $offerPrice}";

      $stmt->execute([$validFrom,$validUntil,$terms,$offerName]);
      $lastInsertID = $pdo->lastInsertId();
      array_push($offerIDs, $lastInsertID);
    } else {
      $messages = array('success'=>false, 'message'=>'Offer Price and Start Date fields can NOT be empty.');
      echo json_encode($messages);
      return;
    }
  }


  $op = array_filter($_POST['offerPrice'],'is_numeric');
  $offerItems = array_combine(array_keys($op), array_values($offerIDs));

  $sql = "INSERT INTO offer_item (offer_id, item_id) VALUES (?,?)";
  $stmt = $pdo->prepare($sql);
  foreach ($offerItems as $k => $v) {
    $offerID = $v;
    $itemID = $k;
    $stmt->execute([$offerID,$itemID]);
  }  

  // Then, update previous offers if exist: 
  if (!empty($prvOferUpdt)) {
    $sql = "UPDATE offer SET valid_until = ? WHERE offer_id = ?";
    $stmt = $pdo->prepare($sql);
    foreach ($prvOferUpdt as $oid => $date) {
      $stmt->execute([$date,$oid]);
    }
  }

  // If, newOferUpdt array is not empty, 
  // then reset valid_from date for newly added offers: 
  if (!empty($newOferUpdt)) {

    $newStartDates = array_combine(array_values($offerIDs), array_values($newOferUpdt));

    $sql = "UPDATE offer SET valid_from = ? WHERE offer_id = ?";
    $stmt = $pdo->prepare($sql);
    foreach ($newStartDates as $k => $dt) {
      if (isset($newStartDates[$k])) {
        $stmt->execute([$dt, $k]);
      }
    }   
  }   
}

 

Also attached sample SQL.

Thank you.

sql-dump.txt

[mac_gyver]

my 2¢. your offer table should hold the unique/one-time information about each offer. once you have inserted a row of data into that table it should only be updated in the case of a mistake in a value or its status changes, such as being marked as deleted. you need all the raw offer data to be stored in that table, giving you an audit trail, so that you can detect if a programming mistake, multiple form submission, or nefarious activity has inserted an incorrect offer.

if a new offer supersedes or modifies an existing offer, that should be handled when the data is queried for or in the processing when it is fetched.

[ginerjm]

And in case you didn't get this from mac-gyver's post, your 'terms' field is completely Wrong.  One does not (as in never) store data in this kind of format.  Apparently you want to convert that 'terms' field to a 'qty' and a 'total' field

[Barand]

49 minutes ago, ginerjm said:

And in case you didn't get this from mac-gyver's post, your 'terms' field is completely Wrong.  One does not (as in never) store data in this kind of format.  Apparently you want to convert that 'terms' field to a 'qty' and a 'total' field

If the terms column always contained a qty and a value I would agree with you, but supermarkets have many different types of offer.

• Buy one get one free
• Buy 2 cabbages, get an Easter egg half price
• Buy 1 item from the main course range, one from the desserts range and one from the specified wine selection all for $10.

In these cases the attributes are different for the various offer types, which could be handled by an EAV model but they are a PITA to query. JSON is another way to do it.

[Barand]

2 hours ago, thara said:

valid_until = (validFrom - 1 second) and "validFrom" date of new offer = (validFrom H:i:s) (H:i:s is the time when the script is executed.)

I don't understand why you would end offers at random times during they day. You buy four items for which the offer label said 50% off. Part way through your checkout the offer finishes, so you you get one of items half price and pay full price for the other three. 1 unhappy customer. You may as well do away with checkout tills and instal a roulette wheel as customers, if they carry on shopping there, won't know what they will be charged.

[thara]

Let me explain this little bit more..

Through my question I have pointed out a "Buy and pay a fixed price" type of offer. (Eg: Buy 3 SAVE 25 / 😃

Suppose the selling price of item X is 70.00. The owner decides to make an offer for that X item. Think that offer named as "Buy 3 SAVE 30 / =". That offer means that 3 of the X items will sell for 180 / -.

Through the system, the shop owner has the ability to create, delete or edit this kind of offers for a product.

He has the ability to change an offer created in this way at any time. Each time that is changed, the data related to the changed offer should be entered as a separate row in the database.This change can even happen to a single day also. In such a case, There is a possibility that the offer made to the customer in the morning may be changed or not received in the evening.

Each of these offers has a relevant date range, so when adding a new offer to the table, you should be careful about the date range of the previous offer related to that particular item. For that reason the previous offers' date range should be updated accordingly, then the conditions should be applicable as I mentioned in the question above.

For better understanding, here I have attached an image of my offer input form:

Edited April 30, 2022 by thara

[Barand]

Existing offers that will be affected by the new offer are those
 

... WHERE item_id = new_tem_id
          AND start_time <= new_end_time
          AND end_time >= new_start_time

 

                              new start              new end
                                  |                      |
                                  |                      |
       |--------------|           |                      |                             not affected
                                  |                      |
                                  |                      |     |-------------|         not affected
                                  |                      |
                                  |                      |
                  |--------------------------------------------|                       change
                                  |                      |
                    |-------------------|                |                             change
                                  |                      |
                                  |             |-----------------|                    change
                                  |                      |
                                  |      |---------|     |                             superseded
                                  |                      |
                                  |                      |

 

Edited May 2, 2022 by Barand