concert listings by month

[sarahp]

I have a program that displays concert listings with sql. i have these fields:

date
event_title
venue
month_number (corresponds to a month name in a month table)

I have it display by month, for example:

                May

2002-05-23 Blink 182/Green Day Hershey Park

                June 

2002-06-02 Incubus Hershey Park

the trouble is, if it's December (2002), and there are listings for January (2003), the listings for January 2003 will show up first.

Any help would be most appreciated.

Sarah

[Anon]

I would need to know more about your code to give you a definitive answer, but if you have the date stored in the database records it would appear you need to change your database SQL to ORDER BY DATE.

As a slight aside why have a month number in the record when you could get this from the date.

Regards,

Fes.

[Anon]

whats the point of the "month_number" field? You already have in the date field. Pull the data out the table ORDER BY date and use PHP's strtotime() and date() functions to format it any way you want.

[sarahp]

Well...my original code is in perl, but I wanted to get an answer in php.

I do have an 'order by date' in my sql statement, as for the month number field, this was one of my first programs and I did a lot of redundant things, but i didn't want to have to re-vamp everything now...kind of like, if it aint broke dont fix it... i just wanted to see if i could make this one adjustment.

thanks for helping and here's my original code anyway:

sub search_db {

%SARAH_HASH=();
$dbh = DBI->connect("DBI:mysql:database=$mysql_db;host=$mysql_host", $mysql_user, $mysql_pass); #prepare and execute SQL statement

$sqlstatement = "SELECT date, event_title, venue, month_number from concert_listings WHERE date < now() and month_number = '$key' order by date";

$sqlstatement = "DELETE from concert_listings WHERE date < now()";
$sth = $dbh->prepare($sqlstatement);
$sth->execute || die "$order_by Could not execute SQL statement...maybe invalid?";

$sqlstatement="SELECT month_number FROM concert_listings";
$sth = $dbh->prepare($sqlstatement);
$sth->execute || die "$order_by Could not execute SQL statement...maybe invalid?";
$number_of_rows = $sth->rows;

if ($number_of_rows == 0) {
print "<TR><TD colspan=\"3\">$FO <font color=\"#FF0000\">Sorry, there are currently no concerts scheduled. Please check back soon.</font> $FC</TD></TR>\n";
} else {

while (($mo_num)=$sth->fetchrow_array) {
$SARAH_HASH{$mo_num} = "$mo";
}

$sth->finish;

foreach $key(sort(keys %SARAH_HASH)) {

$sqlstatement = "SELECT date, event_title, venue, month_number FROM concert_listings WHERE month_number = '$key' order by date";
$sth = $dbh->prepare($sqlstatement);
$sth->execute || die "$order_by Could not execute SQL statement...maybe invalid?";

print "<tr><td align=\"center\" colspan=\"3\"><font size=\"3\" face=\"Verdana, Arial, Helvetica\"><b>$mine_mo{$key}</b></font></td></tr>\n";
    while (($RE_date,$RE_event_title,$RE_venue,$RE_month_number) = $sth->fetchrow_array) {
         print "<tr bgcolor=\"#FFFFFF\" valign=\"top\">\n";
         print "<td><font size=\"1\" face=\"Verdana, Arial, Helvetica\">$RE_date</font></td>\n";
         print "<td><font size=\"1\" face=\"Verdana, Arial, Helvetica\"><b>\U$RE_event_title\E</b></font></td>\n";
         print "<td><font size=\"1\" face=\"Verdana, Arial, Helvetica\">$RE_venue</font></td>\n";
         print "</tr>\n";  
         print "<tr><td colspan=\"3\" background=\"images/dot_bar.gif\"><img src=\"images/spacer.gif\" width=\"1\" height=\"1\"></td>\n";  
}
print "<tr><td colspan=\"3\">&nbsp;</td></tr>\n";
$sth->finish;

}
$dbh->disconnect;

}

} #end of search_db

Fes wrote:

I would need to know more about your code to give you a definitive answer, but if you have the date stored in the database records it would appear you need to change your database SQL to ORDER BY DATE.

As a slight aside why have a month number in the record when you could get this from the date.

Regards,

Fes.

[Anon]

OK I am not over familiar with Perl, but looking at your code I think you're driving the event retrieval by the month number which means you will get all month 1's prior to month 2's etc. hence Jan 2003 will be before Feb 2002.

Here is a schematic of what I would do in PHP:-

Generate a SQL statement to retrieve ALL events after today ordered by date.

$month_no = ""

for each event in db array
{
if ($month_no != month_no in db record)
{

output events for mont/year message

 $month_no = month_no in db record

}

Output event

}

Hope you catch my drift and hope it helps,

Fes.

[sarahp]

Thanks, I finally got it to work!