Date & Time Questions

[ajs83]

I'm using the basic db output code below...

<?
$username="blah";
$password="blah";
$database="blah";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM table ORDER BY name";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

$i=0;
while ($i < $num) {

$name=mysql_result($result,$i,"name");

echo "<b>&nbsp;· $name</b><br>";

$i++;
}

?>

It displays the items as follows

· $name
· $name
· $name

Currently all items also have a date entered in the db as well, but that isn't shown. What I would like to do is add the date to the top item and then display all the items for that, then the next, as shown below

March 3rd, 2005
· $name
· $name
· $name

March 10th, 2005
· $name
· $name
· $name

and so forth. I know how to sort them using the db command, but I don't know how I would have the items show up on the page like that with the date other then creating a table for each week which I'd prefer not to do if possible.

Is this something that can be done easily? If so, which method would I use?

Thanks

[coopster]

As you loop through your result set, just check to see if the date changed. If it did, then print out the date, otherwise keep printing the $name.

[ajs83]

Would the best way be if & LIKE functions?

Also, I only want this to show 2 dates worth of selections, would that be done through a similar method?

[ergophobe]

Assuming your date field is recording a full timestamp, we need to format it for grouping. If just recording the date, you can skip that part.

SELECT DATE_FORMAT(date, '%M %D, %Y') as day, name FROM table ORDER BY date DESC, name

Now, as coop said,

$result=mysql_query($query);
mysql_close();

$date = "";
$count = 1;
$stop = false;

while ($row = mysql_fetch_assoc &&!$stop) {
if ($date!= $row['day'])
{
if ($count <= 2)
{
$date = $row['day'];
echo "<p>$date<p>";
}
else
{
$stop = true;
}
}
if (!$stop)
{
echo "<b>&nbsp;· $name</b><br>";
}
}

You could also do it with two queries, getting the count with the first one

SELECT count(name), DATE_FORMAT(raw_date, '%Y %m %d') as day, username from users GROUP BY date DESC LIMIT 2

Run through the two rows returned, then use the total as your LIMIT in the next query and save yourself having to use the $stop flag in the while loop.