[Resolved] Release Dates

klik

Hey,

I have a releases page where I want to display release dates which are still to come (after current date).

I want each date to be a different group and each group to be alphabetical.

I know it's something like below, but it's obviously not correct.

$result = mysql_query("SELECT * FROM cms_releases WHERE release_date > NOW() ORDER BY release_date ASC GROUP BY release_date");

paulnaj

Hi Klik,

The only problem I can see here is that ORDER BY should always come after GROUP BY ...

<?php 
$result = mysql_query("
	SELECT * 
	FROM cms_releases 
	WHERE release_date > NOW() 
	GROUP BY release_date
	ORDER BY release_date ASC 
");
?>

... but otherwise, looks fine.

Originally posted by klik
I want each date to be a different group and each group to be alphabetical.

The GROUP BY means this will return one record per release_date ... is that what you want?

Paul. 🙂

klik

Thanks for the help. And you're right, this isn't what I'm after.

I'm trying to use the following code

                    <?php

				/* Query for releases */
$result = mysql_query("SELECT * FROM cms_releases WHERE release_date > CURDATE() GROUP BY release_date ORDER BY release_date ASC");
/* END Query for releases */


while ($row = mysql_fetch_assoc($result)){

	$artist = $row['artist'];
	$album = $row['album'];
	$picture = $row['picture'];
	$release_date = $row['release_date'];

			echo' 
              <table width="100%" border="0" cellspacing="1" cellpadding="1">
                <tr> 
                  <td bgcolor="#CCCCCC" height="16">&nbsp;&nbsp;<img src="/images/red_dot.gif" width="4" height="4" alt="" /> 
                    ' . $release_date . '</td>
                </tr>
              </table>
              <p>' . $artist . ' - ' . $album . '<br>
              <p>&nbsp; </p>
              <p>&nbsp;</p>
            ';
}
				?>

I know it needs a few things swapped around, but I want all release dates under the same date to be listed. Do you know how I can amend the code/query to allow this to happen?

Thanks!

paulnaj

Thought not ... usually GROUP BY means you will be using an aggregate function in the SELECT clause (COUNT(), MAX(), etc.)

One way ...

<?php 
/* Query for releases */ 
$result = mysql_query("
	SELECT DISTINCT release_date 
	FROM cms_releases 
	WHERE release_date > CURDATE() 
	ORDER BY release_date ASC
"); 

/* END Query for releases */ 

while($row = mysql_fetch_assoc($result)){ 
	$release_date = $row['release_date'];
	$result2 = mysql_query("
		SELECT *
		FROM cms_releases 
		WHERE release_date = '".$release_date."' 
		ORDER BY artist ASC 
	"); 
	echo '<table width="100%" border="0" cellspacing="1" cellpadding="1">
	<tr><td bgcolor="#CCCCCC" height="16">&nbsp;&nbsp;<img src="/images/red_dot.gif" width="4" height="4" alt="" />'.$release_date.'</td></tr>
</table>';
	while($row2 = mysql_fetch_assoc($result2)){ 
		$artist = $row2['artist']; 
		$album = $row2['album']; 
		//$picture = $row2['picture']; 
		echo '<p>' . $artist . ' -' . $album . '</p>';
	}
}
?>

There are other ways, of course.

Paul. 🙂

klik

You're a legend. Thanks very much for your help. 🙂

klik

Oh one more thing Paul, I shouldn't have marked it as resolved but hope you see this.

I am calling this little function

function displayLatestList($release_items) {


/* Query for releases */ 
$result = mysql_query("
    SELECT DISTINCT release_date
    FROM cms_releases
    WHERE release_date > CURDATE()
    ORDER BY release_date ASC
    LIMIT $release_items
"); 

/* END Query for releases */ 

while($row = mysql_fetch_assoc($result)){ 
    $release_date = $row['release_date'];
    $result2 = mysql_query("
        SELECT *
        FROM cms_releases
        WHERE release_date = '".$release_date."'
        ORDER BY artist ASC LIMIT $release_items
    "); 
    echo '<table width="100%" border="0" cellspacing="1" cellpadding="1">
    <tr><td bgcolor="#CCCCCC" height="16">&nbsp;&nbsp;<img src="/images/red_dot.gif" width="4" height="4" alt="" />  '.$release_date.'</td></tr>
</table>';
    while($row2 = mysql_fetch_assoc($result2)){ 
        $artist = stripslashes($row2['artist']); 
        $album = stripslashes($row2['album']); 
        //$picture = $row2['picture'];
        echo '<p>' . $artist . ' - ' . $album . '</p>';
    }
}

}

I'm sending it a number and would like to display the number of releases in the same format as you managed to supply in the earlier post. The date should be put in and if the number I send goes onto the next date, then I'd like to display that date and then list the remaining numbers under it.

It's just so I can display the most recent releases.

Thanks!

paulnaj

Hi klik,

The date should be put in and if the number I send goes onto the next date, then I'd like to display that date and then list the remaining numbers under it.

... not sure I understand that ... wnat to try again.

Paul

klik

Hmm I'll try to explain better.

When I enter a $number into the function, I want the function to display the ($number) most recent releases.

If we send it the number 10, for example, I would like to display in the following format

DATE
artist - album
artist - album
artist - album
artist - album
artist - album
artist - album
artist - album

DATE
artist - album
artist - album
artist - album

And then stop..

paulnaj

Ok, at the start of the function, declare a counter variable ...

$counter = 0;

... then inside the inner while loop (after the echo), increment it and check if it's equal to your passed variable ...

if($counter++ == $release_items){
return;
}

... Paul.

klik

Thanks again, Paul.

It was displaying 1 more item than the number sent to the function, but I simply set the counter to 1 at the start.

Thanks a lot for your assistance.

Thread now officially resolved. 😃