Databases
StackOverflow2013
Postsdbo
POLoop through MySQL data based on common dates
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLoop through MySQL data based on common dates
    text
    copied!<p>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:</p> <pre><code>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`) ) </code></pre> <p>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).</p> <p>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.</p> <p>Here's the code I have so far:</p> <pre><code>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 &lt;= $info['month'] &amp;&amp; $todayDate &lt;= $info['day'] &amp;&amp; $todayYear &lt;= $info['year']) { $showDate = date("F j, Y", mktime(0, 0, 0, $info['month'], $info['day'], $info['year'])); echo "&lt;b&gt;&lt;li&gt;$showDate&lt;/li&gt;&lt;/b&gt;"; $concertDetails = $info['band'] . " @ " . $info['venue'] . " in " . $info['city'] . ", " . $info['state']; echo "&lt;li&gt;$concertDetails&lt;/li&gt;"; } } mysql_close($con); } </code></pre> <p>This code works perfectly if there are 2 different dates. That is, if the rows inserted into the table look like this:</p> <pre><code>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'); </code></pre> <p>The web page displays:</p> <pre><code>December 8, 2011 Some Band @ The Crocodile Cafe in Seattle, WA December 15, 2011 Some Other Band @ Nectar Lounge in Seattle, WA </code></pre> <p>Perfect, exactly what I wanted. BUT, if I change the first row so that the show is on the 15th as well, like so:</p> <pre><code>(1,'Some Band',12,15,2011,'The Crocodile Cafe','Seattle','WA','2011-12-07 22:50:06') </code></pre> <p>The web page only prints this first entry and doesn't display the second entry (Some Other Band) at all, like this:</p> <pre><code>December 15, 2011 Some Band @ The Crocodile Cafe in Seattle, WA </code></pre> <p>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:</p> <pre><code>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 &lt;= $info['month'] &amp;&amp; $todayDate &lt;= $info['day'] &amp;&amp; $todayYear &lt;= $info['year']) { $showDate = date("F j, Y", mktime(0, 0, 0, $info['month'], $info['day'], $info['year'])); echo "&lt;b&gt;&lt;li&gt;$showDate&lt;/li&gt;&lt;/b&gt;"; $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 "&lt;li&gt;$concertDetails&lt;/li&gt;"; } } } mysql_close($con); } </code></pre> <p>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.</p> <p>Cheers for any help.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload