ORDER BY problem

[trendMM]

I'm having trouble ordering the dates of on my bands shows page correctly. It sorts the year and month ok but when it comes to the day it doesn't sort that right. I have no idea what it would be. If you want to see what I mean please check out www.thetideband.com/shows.php. It's wierd because I tested it out 21132131 times before I launched the site and it was working fine. Not it's messing up on sorting the day. Can someone please help me? Here's the code if you need it...

<?

// Define database connection details
$dbHost = "localhost";
$dbUser = "xxx";
$dbPass = "xxx";
$dbName = "xxx";
$table = "shows";


// Attempt to connect to MySQL server
$link = @mysql_connect($dbHost, $dbUser, $dbPass);

// If the connection was unsuccessful...
if (!$link)
{
    // Report error to Flash and exit
    print ("Could not connect to server");
    exit;
}

// Attempt to select database. If unsuccessfull...
if (!@mysql_select_db($dbName))
{
    // Report error to the browser and exit
    print ("Could not select $dbName database");
    exit;
}

// Build query to fetch shows from database
$query = "SELECT * FROM shows ORDER BY year, month, day DESC";

// Execute query
$result = @mysql_query($query);

// If query was okay AND we have at least 1 shows item...
if ($result && @mysql_num_rows($result) > 0)
{
    // Initialise variable to hold shows items
    $showsText = "";

// For each shows item returned from query...
while($row = mysql_fetch_array($result))
{

    // Output Show
    $showsText .= '<tr><td align="left" valign="top" width="100"><font class="blureg">';
    $showsText .= $row['month'] . "." . $row['day'] . "." . $row['year'];
    $showsText .= '</font></td><td align="left" valign="top" width="150"><font class="blkreg">';
    $showsText .= $row['venue'] . "<br>" . $row['location'];
    $showsText .= '</font></td><td align="left" valign="top" width="70"><font class="blkreg">';
    $showsText .= $row['time'];
    $showsText .= '</font></td><td align="left" valign="top" width="260"><font class="blkreg">';
    $showsText .= $row['body'];
    $showsText .= '</font></td></tr><tr><td colspan="4" height="1" bgcolor="#eeede5"></td></tr>';

// EMAIL PARSER
$pattern = '/\[(email|EMAIL)=(.*?)\](.*?)\[\/(EMAIL|email)\]/';
$replace = '<a href="mailto:\\2">\\3</a>';
$showsText = preg_replace($pattern, $replace, $showsText);

// URL PARSER
$pattern = '/\[(url|URL)=(.*?)\](.*?)\[\/(URL|url)\]/';
$replace = '<a href="\\2" target="_blank">\\3</a>';
$showsText = preg_replace($pattern, $replace, $showsText);

// NEWLINE PARSER
$showsText = ereg_replace("\r\n", "<br>", $showsText);

}

// Output shows items back to the browser
print ($showsText);
}
else
{
// Tell browser no shows items were found
print ("<tr><td colspan=\"4\"><font class=\"blkreg\">Current no shows scheduled. Check back soon.</td></tr>") ;
}

mysql_close();

?>

Thanks in advance!

[acey99]

try:

$query = "SELECT * FROM shows ORDER BY (year, month), day DESC";

[trendMM]

Originally posted by acey99
try:

$query = "SELECT * FROM shows ORDER BY (year, month), day DESC";

Thanks for your help but I tried and it just sent me my output saying there was no shows scheduled. There's gotta be a way to make this work. Got any other ideas?

[trendMM]

anybody got any ideas?