Reputation: 7
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
Reputation: 8885
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 . ' ';
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
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
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
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