views:

115

answers:

3

I have loaded an associative array of records from a MySQL database table.

The array consists of 1 to 7 rows representing one week of entries, which might not have been entered for each day.

How can I insert blank rows into the array for the missing days so that I can easily display the data in a table?

I don't need to update the database with the blanks.

Example:

             Field1  Field2  Field3  Field4 .... Field#  
Record[0]
Record[1]
Record[2]
Record[3]
Record[4]
Record[5]
Record[6]

Field4 is the date as yyyy-mm-dd

I load the array automatically using a start date and end date

Some weeks there will be a Sun, Tue, and Fri or Mon, Tue, Wed, Fri & Sat.

+1  A: 

If your array is associative, then when constructing the table why not just check for and skip the empty rows? As an example:

Example 1:

if ($row['Monday'] == '')
{
    // draw blank template
}
else
{
    // draw using live data
}

Based on added example (untested; for php 5.1 and above):

Example 2:

for($i = 0; $i < count($Record); $i++)
{
    $recordDate = strtotime($Record[$i][field4]);
    $dayOfWeek = $date('N', $recordDate);
    switch ($dayOfWeek)
    {
        case '1':
            // Monday
            break;
        case '2':
            // Tuesday
            break;
        // and so on...
     }
}

Edit

The above code assumed that your rows are in weekday order, with possible omissions. The problem with the first example, is that the array is not associative quite like the example. The problem with the second example, is that a missing weekday row results in a completely skipped output, which could provide a table like MTWFS (skipped Thursday).

So, you need to build a loop that draws each day of the week, and checks all of the rows for the appropriate day to draw. If the day is not found, an empty day is drawn:

Example 3:

$dayNames = {'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'};
// Loop that walks the days of the week:
for($d = 1; $d < 7; $d++)
{
  // Loop that checks each record for the matching day of week:
  $dayFound = false;
  for($i = 0; $i < count($Record); $i++)
  {
    $recordDate = strtotime($Record[$i][field4]);
    $dayOfWeek = $date('N', $recordDate);
    // output this day name in your own formatting, table, etc.
    echo $dayNames[$i];
    if ($dayOfWeek == $d)
    {
        // output this day's data
        $dayFound = true;
        break;
    }
    if (!$dayFound)
    {
      // output a blank template
    }
  }
}

Edit 2

Ok it seems you are more interested in having a fully populated array of weekdays than an output routine (I was assuming you would just want to draw the tables in php or something). So this is my example of how to arrive at a 7-day array with no gaps:

Example 4:

$weekData = array(); // create a new array to hold the final result
// Loop that walks the days of the week, note 0-based index
for($d = 0; $d < 6; $d++)
{
  // Loop that checks each record for the matching day of week:
  $dayFound = false;
  for($i = 0; $i < count($Record); $i++)
  {
    $recordDate = strtotime($Record[$i][field4]);
    $dayOfWeek = $date('N', $recordDate);
    // Add one to $d because $date('N',...) is a 1-based index, Mon - Sun
    if ($dayOfWeek == $d + 1)
    {
        // Assign whatever fields you need to the new array at this index
        $weekData[$d][field1] = $Record[$i][field1];
        $weekData[$d][field2] = $Record[$i][field2];
        $weekData[$d][field3] = $Record[$i][field3];
        $weekData[$d][field4] = $Record[$i][field4];
        // ...
        break;
    }
    if (!$dayFound)
    {
      // Assign whatever default values you need to the new array at this index
      $weekData[$d][field1] = "Field 1 Default";
      // ...
    }
  }
}
JYelton
What is missing is the code to insert a blank row if the entry for that day is not there.
ChuckO
Added new example. Note that it assumes that only one instance of a given weekday will be present in the results. i.e. there shouldn't be two 'Mondays' in the result set. (Can you imagine *two* Mondays in a week??) :)
JYelton
What is still missing is the code to insert a blank row if the entry for that day is not there or to copy the row if it exists." // output this day's data "" // output a blank template "I tried but example did not work for me.
ChuckO
I was focusing more on the code that outputs to your table rather than altering the array itself. Personally I saw no reason to alter the array if you're not saving it. The third example could be used to draw a weekly calendar, printing days with events per the array, or blank days per the template.
JYelton
A: 

Without having seen your current code (as requested in the comments by Felix Kling), my guess is that you will need to loop through your array, passing it to a function (or passing the array to the function) which checks Field4 and keeps track of which days of that week have data and fills in those missing days. This would be easier if the array was in order to start with (as you would only need to track the previous 'entry' rather than the entire week).

I'm a bit busy currently, here's some pseduo-code that will need to be expanded etc.

$formattedWeek = getFormattedWeek($inputArray);

function getFormattedWeek($input) {
    $nextDay = 'Sunday';
    foreach ($input as $entry) {
        if (date-call-that-returns-day-of-the-week !== $nextDay) {
            $output[] = 'add whatever you need to your array';
        } else {
            $output[] = $entry;
        }
        $nextDay = call-to-increase-the-day-or-loop-back-to-Sunday();
    }
    return $output;   
}

You should get the picture.

Blair McMillan
+3  A: 

this is simple: Do you know how many "Fields" you have for each day? let's say it's "num of fields"

$records = array_fill(0, 7, array_fill(0, <num of fields>, ''));

what this does is it creates a blank array from [0] to [6] and for each array element it inserts another array of "Fields", with "num of fields", each of which is set to an empty string ''.

Now that you have this, you read your data from the mysql table and if you selectively assign $records by index (i'm assuming), the rest of them will stay blank.

Keep in mind that you can reassign an element of $records array by using something like

$records[5] = array('New value', 'Field2 value');

which is what you do when you read data from mysql table.

Do you use some kind of index in your mysql table to correspond to the numbered day in a week?

comment here if you get stuck with mysql part.

Raine
Thanks. This is the part I was missing.
ChuckO