Gig Schedule question

[one bad monkey]

My apologies if this has been answered before; I haven't found it.

 

I'm working on making a gig schedule for my site, where I can just add everything into mysql and have it show up. That part I have taken care of, and it works just fine. Here's what I'm stumped at.

 

I would like the php to move any of the performances that have already happened to a spot below the "Upcoming Performances" section, entitled "Previous Performances." This is the code thus far.

 

$sql = mysql_query ("SELECT * FROM `Schedule` ORDER BY day ASC") or die (mysql_error ());

 

echo "<table width=100%>" . "<br>" .

"<tr><td width=15%>Date</td>" .

"<td width=10%>Time</td>" .

"<td width=20%>Venue</td>" .

"<td width=20%>Performance</td>" .

"<td width=10%>Price</td>" .

"<td width=25%>Additional Information</td></tr>";

 

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

 

echo "<tr><td>" . date("D, F j", strtotime($row['day'])) . "</td>" .

"<td>" . $row['time'] . "</td>" .

"<td>" . $row['venue'] . "</td>" .

"<td>" . $row['show'] . "</td>" .

"<td>" . $row['price'] . "</td>" .

"<td>" . $row['info'] . "</td></tr>";

 

}

 

echo "</table>";

 

My thought is that I need to add in the line for the mysql_query something to the effect of WHERE day >= "current date" for the first one, and then repeat this code below it, with a WHERE day < "current date" function. Am I on the right path? Any comments are appreciated.

[JonnoTheDev]

yes, is the day field a unix timestamp?

[one bad monkey]

I believe so. I read a couple sites about doing this sort of thing, and figured keeping it like that would be the easiest way to go.

[taquitosensei]

in your php you could do  as long your day field is a datetime

if(strtotime($row['day']) >= strtotime(date("Y-m-d H:i:s")))
{
// html for upcoming performances

}
else
{
// html for previous 
}

[mpharo]

The way taquitosensei reccommended is a preferred method, as it only requires 1 SQL query and 1 loop.

[JonnoTheDev]

The way taquitosensei reccommended is a preferred method, as it only requires 1 SQL query and 1 loop

I would disagree!

For simplicity a second query is not expensive.

Fill your tables into the appropriate places:

<?php
// future gigs
$result = mysql_query("SELECT * FROM Schedule WHERE FROM_UNIXTIME(day) >= CURDATE() ORDER BY day ASC");
while($row = mysql_fetch_assoc($result)) {

}

// past gigs
$result = mysql_query("SELECT * FROM Schedule WHERE FROM_UNIXTIME(day) < CURDATE() ORDER BY day ASC");
while($row = mysql_fetch_assoc($result)) {

}
?>

[one bad monkey]

Thanks a lot, guys! I'm heading off to lunch, but will get that moving when I get back.

 

[JonnoTheDev]

in your php you could do  as long your day field is a datetime

if(strtotime($row['day']) >= strtotime(date("Y-m-d H:i:s")))
{
// html for upcoming performances

}
else
{
// html for previous 
}

As the requirement is to have past gigs in a separate table / section adding html in this control structure would not work as you are looping through the entire result set. If using 1 query you would build a multidimensional associated array i.e $gigs['future'], $gigs['past']. Then loop each array into its own section. Using 2 queries is not expensive and a simpler implementation.