PHP and MySQL Data Formatting

Question

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
        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

Thanks for all of the answers, they all helped greatly.

Paul Truesdell
– Paul Truesdell

2011-03-09 20:59:25 +00:00
Commented Mar 9, 2011 at 20:59 — Paul Truesdell
– Paul Truesdell, Commented Mar 9, 2011 at 20:59

Craig · Accepted Answer · 2011-03-09 20:12:54Z

1

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.

answered Mar 9, 2011 at 20:12

Craig

2,1931 gold badge19 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paul Truesdell Over a year ago

It took a bit of modifying (using an ORM here) but it worked perfectly. Thanks!

arnehehe · Accepted Answer · 2011-03-09 20:10:35Z

0

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

answered Mar 9, 2011 at 20:10

arnehehe

1,4082 gold badges18 silver badges34 bronze badges

Comments

Patrick Fisher · Accepted Answer · 2011-03-09 20:17:15Z

0

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;
}

edited Mar 9, 2011 at 20:17

answered Mar 9, 2011 at 20:09

Patrick Fisher

8,0725 gold badges38 silver badges28 bronze badges

Comments

asthasr · Accepted Answer · 2011-03-09 20:26:43Z

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.

Collectives™ on Stack Overflow

PHP and MySQL Data Formatting

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related