CODEWITHSUNDEEP
phpmysqlarraysloops
Dave Byrne
Dave Byrne

Reputation: 439

PHP Array Loop + MySQL data

I am building a rota/calendar system, and I'm struggling at a certain point due to my lack of knoweldge with PHP arrays.

For this specific issue, I have two date cells, 17/08/2017 and 18/08/2017, On those days, 2 members of staff are rota'd, and I'm looking to be able to echo both of those peoples initials and shift value into the relevant cell.

Below is the query, and what I'm currently doing wit

$query_show_rota='SELECT * FROM `rota` INNER JOIN users ON users.uid=rota.staffmember WHERE "'.$this->currentDate.'" >= `rotastart` AND "'.$this->currentDate.'" <= `rotaend`';
$db = mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);
$result=mysqli_query($db,$query_show_rota);
    if (!$result) {
      printf("Error: %s\n", mysqli_error($db));
      exit();
    }

while($row = mysqli_fetch_array($result,MYSQLI_ASSOC)) {
  $rota_initials_loop_array = array();
  array_push($rota_initials_loop_array, $row['initials']);
  print_r($rota_initials_loop_array);
  echo $this->currentDate . " - ";
  echo $row['initials'] . " ";
  echo $row['shift'] . "<br />";
}

(some of the above, like the echos in the while loop, are for testing)

This results int he following data returned from MySQL (some fields removed for ease of viewing):

|  rid  |  staffmember  |  initials  |  rotastart  |  rotaend  |  shift  |
|   6   |       1       |     DB     |  2017-08-17 | 2017-08-18|   1430  |
|   7   |       6       |     LG     |  2017-08-17 | 2017-08-18|   0800  |

(2 days for each member of staff, where both members of staff are on shift)

In browser, outside of the calendar day cells, I can see:

Array ( [0] => DB ) 2017-08-17 - DB 1430
Array ( [0] => LG ) 2017-08-17 - LG 0800
Array ( [0] => DB ) 2017-08-18 - DB 1430
Array ( [0] => LG ) 2017-08-18 - LG 0800

(resultant from the testing print_r and echo in my code further above)

I wont bother you with the loop and cell generation code for each calendar day, but within it, I have the following:

<?php 
  foreach($rota_initials_loop_array as $initials_value):
    echo $initials_value 
  endforeach;
  }
?>

But I get absolutely no data printed within each days cell. And I'm not too sure why. Mainly because this is the first time I've touched arrays in PHP. I dont even know if i should be using an array for this? I had it working flawlessly with just 1 row being returned, that was before I turned the $row into a while loop etc..

I'm open to suggestions on how to do it otherwise? Essentially I want to end up with 2x div's in each day cell, that contain intials and shift of each staff member, so "DB - 1430" in one div in day X and "LG - 0800" in another div below it in the same day X. Then the same for day Y.

Upvotes: 0

Views: 152

Answers (1)

Majid Fouladpour
Majid Fouladpour

Reputation: 30282

Essentially I want to end up with 2x div's in each day cell, that contain intials and shift of each staff member, so "DB - 1430" in one div in day X and "LG - 0800" in another div below it in the same day X. Then the same for day Y.

So then you need to generate your array with dates as keys. Something similar to this:

$data = [
  'day-x' => [
    ['DB', '1430'],
    ['LG', '0800'],
  ],
  'day-y' => [
    ...
  ]
];

Even though you have tried to be thorough in explaining the existing code, I cannot say I can visualize the data structure, so the while loop below would have to be adapted to suite your data.

Assuming you somehow get all the tree components of the data (date, initial, shift) in each row, you could get the array shaped like what I gave above with this:

$data = [];
while($row=mysqli_fetch_assoc($result)) {
  if(!array_key_exists($row['date'], $data)) {
    $data[$row['date']] = [];
  }
  $data[$row['date']][] = [$row['initials'], $row['shift']];
}

At this point, you'd be better off if you json_encode and send the data to the browser. That though would require drastic changes (using ajax to get calendar data and rendering it with javascript). If you want to continue generating html in PHP, here is how you consume the $data array:

$html = '';
foreach($data as $date => $events) {
  $html .= '<div class="day">
    <span class="day-number">' . $date . '</span>';
  foreach($events as $event) {
    $html .= '<span class="event">' . $event[0] . ' - ' . $event[1] . '</span>';
  }
  $html .= '</div>';
}

Upvotes: 3

Related Questions