PHP and MySQL Data Formatting

I've created a database and stored some data along with the scheduled date for each item. I would like to output a schedule in the format like this:

January 1:

    - 1:00 pm

        Doctors Appt

    - 4:45 pm开发者_StackOverflow社区

        Birthday Party

January 3:

    - 10:00 am

        Hair Cut Appt

    - 4:50 pm

        Bob's House

The problem I've run into is I'm not sure on how to output the data like this. I only want to display the date once but the time and date are in the same row, so a foreach loop won't work. If there's a better way to structure my data to achieve this then I'm willing to do that also.

My Table contains the following data for each row:

id, name, about, date, time

Any help would be much appreciated. Thanks

You don't need to structure your data differently, it sounds like it's been normalised properly. Instead pre-process your data into a temporary array that groups results by date, then loop over that:

$dateArray = array();
foreach($dbResult as $result) {
    $dateArray[$result['date']][] = $result;
}
foreach($dateArray as $date => $entries) {
    echo $date . ':<br>';

    foreach($entries as $entry) {
        echo $entry['time'] . '<br>' . $entry['name'];
    }
}

Avoid doing two queries because you can do what you need with one and it'll put your database under less load.

Personally I would consider structuring the database as such:

Table: Dates(DateId, Date)
Table: Times(TimeDateID, DateID, Time)
Table: Appointment(AppointmentID, Name)
Table: Appointment_Time(AppointmentID, TimeDateID)

That way you could loop through all your dates, for each date loop through all the times that have that DateID, then loop through all your appointment_times with that timedateID

Pull the data and then create a multidimensional array from it, e.g.:

$data = array(
    'January 3' => array (
        '1:00 PM' => array (
            'Doctor\'s Appt'
        ),
        '4:45 pm' => array (
            'Birthday Party',
            'Something else at 4:45 as well'
        )
    )
);

(Your dates and times will probably not be those strings, but you get the idea.)

Then loop through dates and times in a natural way.

To create an indexed data structure like that shown above:

foreach ($dbResult as $row){
    $data[$row['date']][$row['time']] = $row;
}

You can run a query which selects data for a given date. First, run a query which will give you a list of dates... something like this:

SELECT DISTINCT date FROM table;

Then do a foreach loop on the resulting list, running this query inside the loop:

SELECT time, name, about FROM table WHERE date = currentdate;

This will give you a list of the items for the current date, and you can simply iterate over them. It shouldn't be a performance problem if this is a single-user application or low-load web application.

If you prefer only one query, try this:

SELECT date, time, name, about FROM table ORDER BY date ASC, time ASC

You can then use a single foreach and a placeholder variable to loop over this, without the semantic hoop-jumping of multiple associative arrays and the like. Set the initial value of the placeholder value to the first date in the array, and then check on each loop to see if the date has changed. If it has, output a date header before you output the time, name, and notes about each entry, set the placeholder to the new date, and repeat.