PHP multi-dimensional Array Out of Order from MySQL Query

Question

I have two tables with schedule information that I want a consolidate multi-dimensional array. Here is the function that gets me the records correctly. Some records may have the same 'id' but they are further filtered by the 'type' of meeting elsewhere:

function getAllScheduleItems($db){
  try {
    $schedule_items = array();
    $stmt = $db->prepare("(SELECT id, meeting_date, type, approved, expire_date FROM midweek_schedule)
      UNION (SELECT id, meeting_date, type, approved, expire_date FROM weekend_schedule)
      ORDER BY meeting_date ASC");

    if ($stmt->execute()) {
      while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $schedule_id = $row['id'];
        // So we have a news item.  Now add/create the read flag...
        $schedule_items[$schedule_id]['id'] = $schedule_id;
        $schedule_items[$schedule_id]['meeting_date'] = $row['meeting_date'];
        $schedule_items[$schedule_id]['type'] = $row['type'];
        $schedule_items[$schedule_id]['approved'] = $row['approved'];
        $schedule_items[$schedule_id]['expire_date'] = $row['expire_date'];
        echo "getAllScheduleItems- " . $row['meeting_date'] . "<br>";
      }
    }

    return $schedule_items;
  } catch(PDOException $ex) {
    logIt($db, "ERROR", "Database request for account information failed $ex");
    return 0;
  }
}

The results of this are called by other functions to further filter my results:

function getAllApprovedScheduleItems($db){
  $all_schedule_items = array();
  $all_schedule_items = getAllScheduleItems($db);
  $approved_schedule_items = array();

  foreach($all_schedule_items as $record){
    $schedule_id = $record['id'];
    if($record['approved'] == "Y"){
      $approved_schedule_items[$schedule_id]['id'] = $schedule_id;
      $approved_schedule_items[$schedule_id]['type'] = $record['type'];
      $approved_schedule_items[$schedule_id]['meeting_date'] = $record['meeting_date'];
      $approved_schedule_items[$schedule_id]['approved'] = $record['approved'];
      $approved_schedule_items[$schedule_id]['expire_date'] = $record['expire_date'];
      echo "getAllApprovedScheduleItems- " . $record['meeting_date'] . "<br>";
    }
  }
  return $approved_schedule_items;
}

And finally the last:

function getUnexpiredApprovedScheduleItems($db){
  $all_approved_schedule_items = array();
  $all_approved_schedule_items = getAllApprovedScheduleItems($db);
  $unexpired_approved_schedule_items = array();

  foreach($all_approved_schedule_items as $record){
    $schedule_id = $record['id'];
    $expire_date = $record['expire_date'];
    $stale_date = new DateTime($expire_date);
    $date1 = date("Y-m-d G:i:s");
    $now = new DateTime($date1);
    $interval = $now->diff($stale_date);
    if($stale_date >= $now){
      $unexpired_approved_schedule_items[$schedule_id]['id'] = $schedule_id;
      $unexpired_approved_schedule_items[$schedule_id]['meeting_date'] = $record['meeting_date'];
      $unexpired_approved_schedule_items[$schedule_id]['type'] = $record['type'];
      $unexpired_approved_schedule_items[$schedule_id]['expire_date'] = $record['expire_date'];
      $unexpired_approved_schedule_items[$schedule_id]['expire_days'] = $interval->days;
      echo "getUnexpiredApprovedScheduleItems- " . $record['meeting_date'] . "<br>";
    }
  }

As you can see, I've embedded echo statements to see what's happening. The final printouts of dates with a single letter are from the final php returned array just printed from the main php code. I get the following:

getAllScheduleItems- 1969-12-31
getAllScheduleItems- 1969-12-31
getAllScheduleItems- 1969-12-31
getAllScheduleItems- 2017-07-02
getAllScheduleItems- 2017-07-12
getAllScheduleItems- 2017-07-16
getAllScheduleItems- 2017-07-19
getAllScheduleItems- 2017-07-23
getAllScheduleItems- 2017-07-26
getAllScheduleItems- 2017-07-30
getAllScheduleItems- 2017-08-02
getAllScheduleItems- 2017-08-06
getAllApprovedScheduleItems- 2017-07-02
getAllApprovedScheduleItems- 2017-07-16
getAllApprovedScheduleItems- 2017-07-30
getAllApprovedScheduleItems- 2017-07-19
getAllApprovedScheduleItems- 2017-07-23
getAllApprovedScheduleItems- 2017-07-26
getAllApprovedScheduleItems- 2017-08-02
getAllApprovedScheduleItems- 2017-08-06
getUnexpiredApprovedScheduleItems- 2017-07-16
getUnexpiredApprovedScheduleItems- 2017-07-30
getUnexpiredApprovedScheduleItems- 2017-07-19
getUnexpiredApprovedScheduleItems- 2017-07-23
getUnexpiredApprovedScheduleItems- 2017-07-26
getUnexpiredApprovedScheduleItems- 2017-08-02
getUnexpiredApprovedScheduleItems- 2017-08-06
2017-07-16 W
2017-07-30 W
2017-07-19 M
2017-07-23 W
2017-07-26 M
2017-08-02 M
2017-08-06 W

Why are they out of order? I've looked into multi-dimension sorting but I'd like to understand the why here too. Oh, and if I get an example of a multi-dimensional sort that works, that'd be great too.

score 0 · Accepted Answer · answered Jul 13 '17 at 14:02

If you have multiple records with the same $schedule_id, when you do:

$schedule_items[$schedule_id]['id'] = $schedule_id;

it will not create a new element in the top-level $schedule_items array, it will just overwrite the $schedule_items[$schedule_id] element in its existing position in the array. The result is that the array will not be in order by date of the final results, it will be in the order of the dates of the first item with each id.

If you just want the first date of each id, you can check whether it's already in the array and not add a replacement:

  while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $schedule_id = $row['id'];
    if (isset($schedule_items[$schedule_id]))) {
        continue;
    }
    // So we have a news item.  Now add/create the read flag...
    $schedule_items[$schedule_id]['id'] = $schedule_id;
    $schedule_items[$schedule_id]['meeting_date'] = $row['meeting_date'];
    $schedule_items[$schedule_id]['type'] = $row['type'];
    $schedule_items[$schedule_id]['approved'] = $row['approved'];
    $schedule_items[$schedule_id]['expire_date'] = $row['expire_date'];
    echo "getAllScheduleItems- " . $row['meeting_date'] . "<br>";
  }
}

If you want to get the last of each, you can do:

unset($schedule_items[$schedule_id]);

before adding the new item.

You might want to change the SQL query so it only returns the first or last row for each ID, but that might get complicated because of the UNION.

score 0 · Answer 2 · answered Jul 13 '17 at 14:50

Thank you Barmar! I was thinking associative keys. Clearly the key index will overwrite! Doh! I'll give you the correct answer but I have modified it slightly. I need all records returned, so I added a simple counter to uniquely identify each record. See below

function getAllScheduleItems($db){
  try {
    $schedule_items = array();
    $stmt = $db->prepare("(SELECT id, meeting_date, type, approved, expire_date FROM midweek_schedule)
      UNION (SELECT id, meeting_date, type, approved, expire_date FROM weekend_schedule)
      ORDER BY meeting_date ASC");
    $schedule_id = 1; //Keep ALL record using a unique id
    if ($stmt->execute()) {
      while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        //$schedule_id = $row['id'];
        // So we have a news item.  Now add/create the read flag...
        $schedule_items[$schedule_id]['id'] = $schedule_id;
        $schedule_items[$schedule_id]['meeting_date'] = $row['meeting_date'];
        $schedule_items[$schedule_id]['type'] = $row['type'];
        $schedule_items[$schedule_id]['approved'] = $row['approved'];
        $schedule_items[$schedule_id]['expire_date'] = $row['expire_date'];
        echo "getAllScheduleItems- " . $row['meeting_date'] . "<br>";
        $schedule_id++; //increment for each record returned
      }
    }

    return $schedule_items;
  } catch(PDOException $ex) {
    logIt($db, "ERROR", "Database request for account information failed $ex");
    return 0;
  }
}

PHP multi-dimensional Array Out of Order from MySQL Query

2 Answers2