[SOLVED] group by date

[corillo181]

so i got some data on a mysql table that i want to group by date

 

i was thinking of

 

SELECT song_name,DATE_FORMAT(date, '%m - %d - %y') as dates FROM tra_artist_song GROUP  BY DATE

 

how do make every data under the same date appear under that date

 

 

[corillo181]

i came up with this, if any one know a better way please let me know.

 

function newSongs($limit){

$select="SELECT DATE_FORMAT(date,'%a, %M %d, %y') as dates FROM tra_artist_song GROUP BY dates LIMIT $limit";
$query=mysql_query($select)or die(mysql_error());

while($name=mysql_fetch_array($query)){
$dates=$name['dates'];

echo '<h3>'.$dates.'</h3>';
$select2="SELECT song_name FROM tra_artist_song WHERE DATE_FORMAT(date,'%a, %M %d, %y')='$dates'";
$query2=mysql_query($select2)or die(mysql_error());

while($songNames=mysql_fetch_array($query2)){
echo $songNames['song_name'].'<br />';
}

}

}

[Barand]

try

<?php
$select="SELECT DATE_FORMAT(date,'%a, %M %d, %y') as dates, song_name FROM tra_artist_song ORDER BY dates LIMIT $limit";
$query=mysql_query($select)or die(mysql_error());

$prevdate = '';
while (list($dt, $song) = mysql_fetch_row($query)) {
    if ($prevdate != $dt)
    {
        echo "<h3>$dt</h3>";       // output date when its value changes
        $prevdate = $dt ;
    }
    echo "$song<br/>";
}
?>

[corillo181]

works the same, but lest code thankx.

[Barand]

and less query traffic. You could also do this

 

<?php
$select="SELECT DATE_FORMAT(date,'%a, %M %d, %y') as dates,
        GROUP_CONCAT (song_name SEPARATOR '<br/>') 
        FROM tra_artist_song 
        GROUP BY dates LIMIT $limit";
$query=mysql_query($select)or die(mysql_error());

while (list($dt, $songs) = mysql_fetch_row($query)) {
    echo "<h3>$date</h3>$songs";
}
?>