Displaying db results by day of week group

PHP
1

My results are being displayed on a web page as a list in descending order by date. Today’s results at the top, then yesterdays below that and so on. Today may have 2 results so far; yesterday had 5 the day before that had 4 and so on.

Now I want the day of the week to be displayed for each day to divide the list on the page.

result
result
result
day of the week
result
result
result
result
result
day of the week
result
result
result
result

Can someone show me how to do this? Thanks.

Here is code that displays results in descending order.


<?php	
 $query = "SELECT name, id FROM table ORDER BY date DESC LIMIT 60";
 $result = $db->query( $query );	

 while ($row = $result->fetch_assoc()) {
?>

 <p><a href="display_results.php?id=<?php echo $row['id']; ?>"><?php echo $row['name']; ?></a></p>

<?php } ?>
2

Make sure it translated the data already in the table correctly when you did the change.

Using a Datetime field will allow the DAYNAME mysql query function to work correctly.
Though if i were you, i’d move the
$last_day = $row[‘day’];

line inside the IF statement. Just for optimization sake.

3

It probably wont be in your PHP book, because the date field in question is a MYSQL type.
http://dev.mysql.com/doc/refman/5.6/en/date-and-time-types.html

4

That looks brilliantly simple. I was expecting a lot more involved coding.

When you say my column being a proper date type, I think your referring to the date column in my table.

My table has this for the date. date char(100) DEFAULT NULL,

This probably is not proper.
I will have to read through my PHP books to learn about date types, something I did not know about until now. After that I will try to test your example.

Thanks very much for your help.

5

Assuming the date column is actual a proper date type and not something you’ve hacked to contain a date then try:


<?php	
 $query = "SELECT DAYNAME(date) AS day, name, id FROM table ORDER BY date DESC LIMIT 60";
 $result = $db->query( $query );

$last_day;	

 while ($row = $result->fetch_assoc()) {
if($last_day != $row['day']) {

echo $row['day'];
} ?>

 <p><a href="display_results.php?id=<?php echo $row['id']; ?>"><?php echo $row['name']; ?></a></p>

<?php

$last_day = $row['day'];
} 

?>
6

Thanks for the tip. That helps me learn.

This thing works!

7

Thanks for your response.

I am using date(‘Y-m-d-g-i’); earlier in my code. g and i is hours and minutes, I am sure you know this.

I changed my column type from:

date char(100) DEFAULT NULL,

to

date DATETIME

I wonder if this will work.