[SOLVED] displaying by date and grouping by day

[scarhand]

wasnt sure if this belonged in php help or mysql help so im posting it in both

 

im trying to display results from my database by date and group them together by day.

 

this is what the mysql table looks like:

 

title | date

test title 4 | 2008-01-11 05:14:56

test title 3 | 2008-01-11 05:14:56

test title 2 | 2008-01-10 05:14:56

test title 1 | 2008-01-10 05:14:56

 

and i want the results to look like this:

 

january 11, 2008

 

test title 4

test title 3

 

january 10, 2008

 

test title 2

test title 1

 

i have been messing around with this for a while and cant get it to work the way i want it to. you can probably do this in 1 mysql statement but heres the coding i have so far:

 

Code:

 

<?php

 

$sql1 = mysql_query("SELECT * FROM videos GROUP BY date");

$sql2 = mysql_query("SELECT * FROM videos ORDER BY date DESC");

$count = mysql_num_rows($sql1);

 

if ($count == 0)

{

  echo 'There are no videos to display';

}

else

{

  while ($row = mysql_fetch_array($sql1))

  {

    $date = date("M j, Y", strtotime($row['date']));

 

    echo "$date<br><br>";

   

    while ($row = mysql_fetch_array($sql2))

    {

      $title = $row['title'];

 

      echo "$title<br>";

    }

  }

}

 

?>

 

 

any help is greatly appreciated

[GingerRobot]

If you order by the date, you can simply monitor when this date isn't the same as the last one, and echo out the new date:

 

<?php
$sql = "SELECT `title`,DATE_FORMAT(`date`,'%M %d, %Y') FROM `videos` ORDER BY `date` DESC";
$result = mysql_query($sql) or die(mysql_error());
$curr_date = '';
while(list($title,$date) = mysql_fetch_row($result)){
if($curr_date != $date){
	echo '<br />'.$date."<br /><br />\n";
	$curr_date = $date;
}
echo $title."<br />\n";
}
?>

[twostars]

wasnt sure if this belonged in php help or mysql help so im posting it in both

 

im trying to display results from my database by date and group them together by day.

 

this is what the mysql table looks like:

 

title | date

test title 4 | 2008-01-11 05:14:56

test title 3 | 2008-01-11 05:14:56

test title 2 | 2008-01-10 05:14:56

test title 1 | 2008-01-10 05:14:56

 

and i want the results to look like this:

 

january 11, 2008

 

test title 4

test title 3

 

january 10, 2008

 

test title 2

test title 1

 

i have been messing around with this for a while and cant get it to work the way i want it to. you can probably do this in 1 mysql statement but heres the coding i have so far:

 

Code:

 

<?php

 

$sql1 = mysql_query("SELECT * FROM videos GROUP BY date");

$sql2 = mysql_query("SELECT * FROM videos ORDER BY date DESC");

$count = mysql_num_rows($sql1);

 

if ($count == 0)

{

  echo 'There are no videos to display';

}

else

{

  while ($row = mysql_fetch_array($sql1))

  {

    $date = date("M j, Y", strtotime($row['date']));

 

    echo "$date<br><br>";

   

    while ($row = mysql_fetch_array($sql2))

    {

      $title = $row['title'];

 

      echo "$title<br>";

    }

  }

}

 

?>

 

 

any help is greatly appreciated

OOh, tough one.

Here goes..

 

<?php
$sql1 = mysql_query("SELECT * FROM videos GROUP BY date");
$count = mysql_num_rows($sql1);
if ($count == 0)
	echo 'There are no videos to display';
else
{
  while ($row = mysql_fetch_array($sql1))
  {
    $date = date("M j, Y", strtotime($row['date']));
    $_date = explode(" ", $row['date']);
    echo $date . "<br />";
$sql2 = mysql_query("SELECT * FROM videos WHERE date LIKE '%{$_date[0]}' ORDER BY date DESC");
    while ($row = mysql_fetch_array($sql2))
    {
      $title = $row['title'];
      echo $title . "<br />";
    }
  }
}

?>

 

Edit: GingerRobot, your solution is much nicer. I like it. ( I was coding in the post window again. )

[scarhand]

thanks ginger. works like a charm.