Loop through MySQL data based on common dates

Question

I have a task that I've been racking my head around all day no avail. I have a MySQL table that's set up as follows:

CREATE TABLE `Shows` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `band` varchar(255) NOT NULL,
  `month` int(2) NOT NULL,
  `day` int(2) NOT NULL,
  `year` int(4) NOT NULL,
  `venue` varchar(255) NOT NULL DEFAULT '',
  `city` varchar(255) NOT NULL DEFAULT '',
  `state` varchar(2) NOT NULL DEFAULT '',
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
)

Basically, it's a table to hold information about concerts. The month and day are stored numerically without leading zeros (e.g. February 3 would be represented as 2 in the month column and 3 in the day column).

I'm creating an events calendar page that displays information about upcoming concerts. I want to set it up so that it loops through each day that's equal to or after the current date so that no previous concerts show up on events that are supposed to be upcoming.

Here's the code I have so far:

function displayUpcomingShows()
{   
    include "/path/to/config/file/that/contains/credentials";

    $con = mysql_connect($credentials);

    if (!$con)
    {
        die('Could not connect: ' . mysql_error());
    }

    mysql_select_db("database", $con);  

    $dateQuery = mysql_query("SELECT * FROM Shows GROUP BY month, day, year") or die(mysql_error());
    $todayMonth = date(n);
    $todayDate = date(j);
    $todayYear = date(Y);


    while ($info = mysql_fetch_array($dateQuery))
    {
        if ($todayMonth <= $info['month'] && $todayDate <= $info['day'] && $todayYear <= $info['year'])
        {
            $showDate = date("F j, Y", mktime(0, 0, 0, $info['month'], $info['day'], $info['year']));
            echo "<b><li>$showDate</li></b>";
            $concertDetails = $info['band'] . " @ " . $info['venue'] . " in " . $info['city'] . ", " . $info['state'];
            echo "<li>$concertDetails</li>";
        }
    }
    mysql_close($con);
}

This code works perfectly if there are 2 different dates. That is, if the rows inserted into the table look like this:

INSERT INTO `Shows` (`id`, `band`, `month`, `day`, `year`, `venue`, `city`, `state`, `timestamp`)
VALUES
(1,'Some Band',12,8,2011,'The Crocodile Cafe','Seattle','WA','2011-12-07 22:50:06'),
(2,'Some Other Band',12,15,2011,'Nectar Lounge','Seattle','WA','2011-12-07 15:17:39');

The web page displays:

December 8, 2011
Some Band @ The Crocodile Cafe in Seattle, WA

December 15, 2011
Some Other Band @ Nectar Lounge in Seattle, WA

Perfect, exactly what I wanted. BUT, if I change the first row so that the show is on the 15th as well, like so:

(1,'Some Band',12,15,2011,'The Crocodile Cafe','Seattle','WA','2011-12-07 22:50:06')

The web page only prints this first entry and doesn't display the second entry (Some Other Band) at all, like this:

December 15, 2011
Some Band @ The Crocodile Cafe in Seattle, WA

Can somebody point me in the right direction or explain what I'm doing wrong? I've also tried structuring my query so that it selects the distinct dates only, loops through those using the same while loop and if statement in the code above, but then contains a new query inside the if statement that selects all information from the table where the month, day, and year match, then uses another while loops to print that data, but this causes the page to stop loading right when the function is called. That code looks like this:

function displayUpcomingShows()
{   
    (...same code as above up until here...)

    $dateQuery = mysql_query("SELECT DISTINCT month, day, year FROM Shows") or die(mysql_error());
    $todayMonth = date(n);
    $todayDate = date(j);
    $todayYear = date(Y);

    while ($info = mysql_fetch_array($dateQuery))
    {
        if ($todayMonth <= $info['month'] && $todayDate <= $info['day'] && $todayYear <= $info['year'])
        {
            $showDate = date("F j, Y", mktime(0, 0, 0, $info['month'], $info['day'], $info['year']));
            echo "<b><li>$showDate</li></b>";

            $detailsQuery = mysql_query("SELECT * FROM Shows WHERE month = $info['month'] AND day = $info['day'] AND year = $info['year']") or die(mysql_error());
            while ($concertInfo = mysql_fetch_array($detailsQuery)
            {
                    $concertDetails = $concerinfo['band'] . " @ " . $concertInfo['venue'] . " in " . $concertInfo['city'] . ", " . $concertInfo['state'];
                    echo "<li>$concertDetails</li>";
            }
        }
    }
    mysql_close($con);
}

Sorry if this particularly verbose, but I wanted to make sure I was as detailed as possible. I'm making decent progress with learning PHP/MySQL more fluently, but I suppose that's why I'm still struggling.

Cheers for any help.

Answer 1

Easy update your query like so:

mysql_query("SELECT * FROM Shows WHERE timestamp >= '".date('Y-m-d H:i:s').'") or die(mysql_error());

Get rid of:

$todayMonth = date(n); $todayDate = date(j); $todayYear = date(Y);

And in your while loop get rid of the IF statement all together

So your code will look like:

function displayUpcomingShows()
{   
    include "/path/to/config/file/that/contains/credentials";

    $con = mysql_connect($credentials);

    if (!$con)
    {
        die('Could not connect: ' . mysql_error());
    }

    mysql_select_db("database", $con);  

    $dateQuery = mysql_query("SELECT * FROM Shows WHERE timestamp >= '".date('Y-m-d H:i:s')."'") or die(mysql_error());


    while ($info = mysql_fetch_array($dateQuery))
    {
        $showDate = date("F j, Y", mktime(0, 0, 0, $info['month'], $info['day'], $info['year']));
        echo "<b><li>$showDate</li></b>";
        $concertDetails = $info['band'] . " @ " . $info['venue'] . " in " . $info['city'] . ", " . $info['state'];
        echo "<li>$concertDetails</li>";
    }
    mysql_close($con);
}