ESavage
ESavage

Reputation: 7

multiple dates datetime

I am trying to display a table with PHP MYSQL where the FIELD is the DATE and rows are TIME from a MySQL database with unknown/infinite records, one with different TIMES for the same DATE, by querying it for the DateTime.

My mysql date stores the dateTime in the same column, but I am splitting this and trying to display them seperately. BUT I cannot seem to display the date only once and the time multiple times, it is just both.

$sql_result = mysqli_query($connection, "SELECT DATE(date_time) AS date_part, TIME(date_time) AS time_part FROM $table WHERE date_time LIKE '$date_input%'");

if (mysqli_num_rows($sql_result) == 0) 
{
echo "<p>No bookings exist.</p>";
}

else {

echo "<h3>Results for booked " . $table . " Appointments:</h3>";

echo "<h3>" . $formattedDate ."</h3>";



while ($row = mysqli_fetch_array($sql_result))
{

     echo $row['date_part'];

$array_time = array($row['time_part']); 

 foreach ($array_time as $time_output)                  
     {
 echo $time_output;
 }
    } 
}

My output is like this:

2013-12-0809:00:002013-12-0810:00:002013-12-0811:00:002013-12-0812:00:002013-12-0814:00:002013-12-0815:00:002013-12-0816:00:002013-12-0817:00:002013-12-0909:00:002013-12-0809:00:00

But I would like it like this:

2013-12-08     09:00:0010:00:0011:00:0012:00:0014:00:0015:00:0016:00:0017:00:0009:00:000
2013-12-09     9:00:00

Upvotes: 0

Views: 184

Answers (4)

Hrrmm. Theres a bit of logic problem here:

while ($row = mysqli_fetch_array($sql_result))
{

     echo $row['date_part'];

$array_time = array($row['time_part']); // HERE

 foreach ($array_time as $time_output)                  
     {
 echo $row['time_part'];
 }
    } 

$array_time will always have only one value, since $row['time_part'] only refers to ONE row (each iteration of while ($row = mysqli_fetch_array($sql_result)) reassigns a single row to $row)

try doing this first pass to define a workable array

while ($row = mysqli_fetch_array($sql_result))
{
 $array[$row['date_part']][$row['time_part']] = 1; // the value assigned doesn't matter, all we wish is a definite hierarchy
    } 

this will give you an array like:

['2013-12-08']['9:08'] = 1
              ['12:30'] = 1
              [23:17] = 1
['2013-12-09']['10:00'] = 1
              [14:20] = 1

THEN! you can foreach through your result

foreach ($array as $date_part => $array_time)
 {
 echo $date_part . '&nbsp;&nbsp;';
 foreach ($array_time as $time_part => $i) // again our $i is not used
   {
    echo $time_part;
    }
 echo '<br>'; // here it breaks line after all times for the date has been echoed
 }

Upvotes: 1

Mike Brant
Mike Brant

Reputation: 71384

Two comments. First, it seems like you need an order by time so as to order your records.

SELECT DATE(date_time) AS `date_part`, TIME(date_time) AS `time_part`
FROM $table
WHERE date_time LIKE '$date_input%'
ORDER BY date_time ASC

Second, if I read your question correctly, it seems like you want to output your data into two columns, one with date and the other with all times for that date. You might consider using GROUP_CONCAT() to do this in SQL, making your output easy.

SELECT DATE(date_time) AS `date_part`, GROUP_CONCAT(TIME(date_time)) AS `times`
FROM $table
WHERE date_time LIKE '$date_input%'
GROUP BY `date_part`
ORDER BY date_time ASC

This would give output like

date_part   times
2013-12-08  11:22:33,11:33:44,12:44:55
2013-12-09  12:00:00

With this approach, there would be no need to build a multi-dimensional array in PHP, as the data would come out of the database just the way you need it. That also means that you don't need to load the entire result set into memory in order to work with it (as you would have to do if creating multi-dimensional array).

Upvotes: 0

Rwd
Rwd

Reputation: 35180

I think I understand what you're trying to say, however, MySQL won't create a multidimensional array. You should try something like this:

$dates = array();
while ($row = mysqli_fetch_array($sql_result)) {

    $dates[$row['date_part']][] = $row['time_part']
}

Then you could have something like this:

foreach ($dates as $key => $value) {

    echo $key.'<br />';
    foreach ($value as $time) {
        echo $time.' - ';
    }
    echo '<br />';
}

which should look something like:

2013-09-01
09:00 - 09:30 - 10:20 - 11:00

2013-09-02
10:12 - 11:00 - 12:24 //etc

Hope this helps!

Upvotes: 0

Rob M.
Rob M.

Reputation: 36511

You need to GROUP in your query:

SELECT DATE(date_time) AS date_part, TIME(date_time) AS time_part
FROM $table
WHERE date_time LIKE '$date_input%'
GROUP BY date_part

Upvotes: 0

Related Questions