[SOLVED] QUERY - Headed with date inserted

[Canman2005]

Hi all

 

I have the following database table called `people`

 

 

   

id

   

fullname

   

  datetime

 

 

   

1

   

Bob

   

  2008-12-04 12:33:33

 

 

   

2

   

Brian

   

  2008-12-05 14:30:00

 

 

   

3

   

Vicki

   

  2008-12-06 09:21:00

 

 

   

4

   

Sarah

   

  2008-12-06 21:23:59

 

 

   

5

   

John

   

  2008-12-07 20:00:00

 

 

and at the moment I do the following to get my rows

 

$sql = "SELECT * FROM people ORDER BY datetime DESC";
$show = @mysql_query($sql,$connection) or die(mysql_error());
while ($row = mysql_fetch_array($show))
{
print $row['fullname'].' - '.$row['datetime'];
print "<br>";
}

 

and this returns the following

 

John - 2008-12-07 20:00:00

Sarah - 2008-12-06 21:23:59

Vicki - 2008-12-06 09:21:00

Brain - 2008-12-05 14:30:00

Bob - 2008-12-04 12:33:33

 

Which is cool, but is it possible to adjust the QUERY so it uses the datetime field to group rows by what date they were inserted using the `datetime` field, so with my data, it would produce the following;

 

--Today--

John - 2008-12-07 20:00:00

 

--6th December--

Vicki - 2008-12-06 09:21:00

Sarah - 2008-12-06 21:23:59

 

--5th December--

Brain - 2008-12-05 14:30:00

 

--4th December--

Bob - 2008-12-04 12:33:33

 

Is this at all possible, been hunting all morning online and trying things.

 

Thanks in advance

 

Dave

[gaza165]

Change your sql statement too

 

<?php

$sql = "SELECT * FROM people GROUP BY datetime ASC";
$show = @mysql_query($sql,$connection) or die(mysql_error());

while ($row = mysql_fetch_array($show))
{
$head_date = strtotime($row['datetime']);
echo date('m-d-Y',$head_date);
echo $row['fullname'].' - '.$row['datetime'];
echo "<br>";
}

?>

[Canman2005]

Cool, cheers gaza165, that seemed to have worked lovely.

 

The only problem with the GROUP on your solution is that although some rows have the same date, they don't always have the same time, therefore the GROUP BY isnt really working and it only GROUPS rows with the exact same date and time, is there a way around that or do I need to change the database and store one field for date and one for time?

[Canman2005]

Oh also, futher to that, do I need to run another QUERY inside your solution gaza165? Because if I do a GROUP BY it only returns one row if the same fields match, therefore, would I need to run your QUERY and then another one inside that doesnt do a GROUP?

[Canman2005]

Solved