help filtering dates

[junglyboi]

Ok, I'm trying to set up an events list on my site.  I only want to show current and future events; not expired ones.  So I figured out how to break down the current time into seconds, account for the timezone differences, compare that to the end date also broken into seconds, and only show rows that hadn't passed the end date yet.

But then I got all excited when I found out about pagination, and added that (thanks to php freaks!).  The only problem is that the pagination counts ALL the rows, and my if statement only shows the current ones.  The result is a couple blank pages at the beginning until it finds current dates.

So my question is this... how can I change my SELECT statement so that it only counts the current dates.  I understand that I can store the date values as integers and use a > or <, but I also need these dates to display in the proper format on the page.  You can see the current page without the date filter at www.ticketstone.com/events.php?q=all.

Here's my source code so far (I've commented out the if statment for the date so it won't show blank pages for now):

<?php

// Start Default Search
$q = $_GET['q'];
if ($q == "all") {

  // Connect to the MySql Server
  $db = mysql_connect('connection info here');
 
  // Select the Core Database
  mysql_select_db('database info here', $db);

 
  // Query the default list of event
 
  $count = mysql_query("SELECT * FROM shows,venues,zipcodes WHERE shows.show_venue LIKE venues.venue_id AND venues.venue_zip LIKE zipcodes.zipcode ORDER BY shows.show_start", $db);
 
  $total_rows = mysql_num_rows($count);
  $page_rows = 25;
 
  if (isset($_GET['page'])) {
    $page = $_GET['page'];
  }
  else {
    $page = 1;
  }
 
  $total_pages = ceil($total_rows / $page_rows);
 
  $offset = ($page - 1) * $page_rows;
 
  $result = mysql_query("SELECT * FROM shows,venues,zipcodes WHERE shows.show_venue LIKE venues.venue_id AND venues.venue_zip LIKE zipcodes.zipcode ORDER BY shows.show_start LIMIT $offset, $page_rows", $db);
 
  // Start Title Line
  echo "<span class='body-text'>Showing</span> ";
  echo "<span class='title-text'>";
  echo $total_rows;
  echo "</span>";
  echo " <span class='body-text'> events in the United States.";

      echo "</td></tr>"; // End title row of Events column
  echo "<tr><td width='507' height='241' valign='top' background='images/slices/events/events_body.jpg' style='padding-left:10px'>"; // Start Body row of Events column
 
  echo "<table cellspacing=1 cellpadding=3>";
  echo "<tr class='hot_tix_row2'><td class='title-text'><b>Time</b></td><td class='title-text'><b>Event</b></td><td class='title-text'><b>Location</b></td><td class='title-text'><b>Tickets</b></td></tr>";
 
  $r = 0;
 
  while ($row = mysql_fetch_array($result)) {
 
// Set the Local Time Zone

$timezone = $row['timezone'];

// Set the Local Time

$local = date('m/d/y g:i a', strtotime('now +' .(8 - $timezone). 'hours'));

// Split the Local Time into seconds

$local_mo = date('m', strtotime($local));
$local_da = date('d', strtotime($local));
$local_yr = date('Y', strtotime($local));
$local_ho = date('H', strtotime($local));
$local_mi = date('i', strtotime($local));
$local_se = date('s', strtotime($local));
$local_time = mktime($local_ho,$local_mi,$local_se,$local_mo,$local_da,$local_yr);

// Get the Show's Start Time

$start = $row['show_start'];

// Split the Show's Start Time into seconds

$start_mo = date('m', strtotime($start));
$start_da = date('d', strtotime($start));
$start_yr = date('Y', strtotime($start));
$start_ho = date('H', strtotime($start));
$start_mi = date('i', strtotime($start));
$start_se = date('s', strtotime($start));
$start_time = mktime($start_ho,$start_mi,$start_se,$start_mo,$start_da,$start_yr);

// Get the Show's End Time

$end = $row['show_end'];

// Split the Show's End Time into seconds

$end_mo = date('m', strtotime($end));
$end_da = date('d', strtotime($end));
$end_yr = date('Y', strtotime($end));
$end_ho = date('H', strtotime($end));
$end_mi = date('i', strtotime($end));
$end_se = date('s', strtotime($end));
$end_time = mktime($end_ho,$end_mi,$end_se,$end_mo,$end_da,$end_yr);

// Format Start Time as m/d/y

$start_date = mktime(0,0,0,$start_mo,$start_da,$start_yr);

// Format End Time as m/d/y

$end_date = mktime(0,0,0,$end_mo,$end_da,$end_yr);


//if ($local_time < $end_time) {


  // Set the row colors

  if ($r % 2 == 0) {
echo "<tr class='hot_tix_row1'>";
$r++;
  }

  else {
echo "<tr class='hot_tix_row2'>";
$r++;
  }

  echo "<td>";
 
if ($start_date < $end_date) {
  echo date('m/d/y', strtotime($row['show_start']));
  echo "<br>";
  echo date('m/d/y', strtotime($row['show_end']));
}
else {
  echo date('m/d/y', strtotime($row['show_start']));
  echo "<br>";
  echo date('g:i a', strtotime($row['show_start']));
}
 
  echo "</td><td>";
  echo $row['show_name'];
  echo "</td><td>";
  echo $row['venue_name'];
  echo "<br>";
  echo $row['zip_city'];
  echo ", ";
  echo $row['zip_state'];
  echo "</td><td>";
 
  if ($row['show_buy'] <> NULL) {
echo "<a href='";
echo $row['show_buy'];
echo "'>on sale now</a></td></tr>";
  }
  else {
echo "more info</td></tr>";
  }
 

    // End of row
 
  } // End of while
 
  echo "</table>";
  echo "<span class='body-text'><center>";
 
  for ($i = 1; $i <= $total_pages; $i++) {
    if ($i == $page) {
  echo $i . " ";
}
else {
  echo " <a href='events.php?q=all&page=" . $i . "'>" . $i . "</a> ";
}
  }
 
  echo "<br>all rows:$all_rows, total rows:$total_rows, total pages:$total_pages, offset: $offset";
 
  echo "</center></span>";
 
      }  // End of Q all

else {
    echo "No Events Found";
    }
  ?>

[roopurt18]

I didn't bother to look at your source since it's not in code tags in addition to the fact that it looks like more than I want to go about reading.

But, you should be storing any dates in one of the date fields supported by MySQL: TIMESTAMP, DATETIME, or DATE.  If you're interested in the day and time you'll probably want to be using DATETIME.

Let's say you have the following table:
[b]events[/b]
[code]
CREATE TABLE events (
   name VARCHAR(25) NOT NULL,
   description TINYTEXT,
   scheduled DATETIME
   UNIQUE( name, scheduled )
  );
[/code]

Here is some relevant MySQL...

Select all expired events:
[code]SELECT * FROM events WHERE scheduled < NOW();[/code]

Select all non-expired events:
[code]SELECT * FROM events WHERE scheduled >= NOW();[/code]

Select all non-expired events between now and three weeks from now, order them descending by date
[code]SELECT * FROM events WHERE scheduled >= NOW() AND scheduled <= (NOW() + INTERVAL 3 WEEKS)
         ORDER BY scheduled DESC;[/code]

Select page 5 of 200, 50 records per page, of non-expired events
[code]SELECT * FROM events WHERE scheduled >= NOW()
  ORDER BY scheduled ASC LIMIT 250,50[/code]

Hope that helps you out some.

[junglyboi]

oh thank you thank you thank you!!!!!  i swear i tried that a billion times before, but something about you saying it made it work... you must have special powers

[roopurt18]

After looking at your code some, you're going to take a performance hit here:
[code]<?php
$count = mysql_query("SELECT * FROM shows,venues,zipcodes "
                    . "WHERE shows.show_venue LIKE venues.venue_id "
                    . "AND venues.venue_zip LIKE zipcodes.zipcode "
                    . "ORDER BY shows.show_start" , $db);
$total_rows = mysql_num_rows($count);
$page_rows = 25;

if (isset($_GET['page'])) {
$page = $_GET['page'];
}else{
  $page = 1;
}
$total_pages = ceil($total_rows / $page_rows);
$offset = ($page - 1) * $page_rows;
$result = mysql_query("SELECT * FROM shows,venues,zipcodes "
                      . "WHERE shows.show_venue LIKE venues.venue_id "
                      . "AND venues.venue_zip LIKE zipcodes.zipcode "
                      . "ORDER BY shows.show_start "
                      . "LIMIT $offset, $page_rows", $db);
?>[/code]

Pagination serves two purposes.  It limits the load on the MySQL server and it makes your pages more user friendly.  You're performing one query to get the total number of records and another to get only those records you want.  However, that first query is potentially returning [b]a lot[/b] of records.

I've taken the liberty of rewriting this portion of your code:
[code]
<?php
// Get the total count
$sql = "SELECT COUNT(*) AS Num FROM shows s, venues v, zipcodes z WHERE "
    . "s.show_venue LIKE v.venue_id AND "
    . "v.venue_zip LIKE z.zipcode";
$q = mysql_query($sql);
// Set $count to the number of rows if successful, or zero on failure
$count = $q && is_array($q) && count($q) ? $q[0]['Num'] : 0;
$page_rows = 25;
$page = isset($_GET['page']) && is_int($_GET['page']) ? $_GET['page'] : 1;
$total_pages = (int)($count / $page_rows + 1)
$offset = ($page - 1) * $page_rows;

$sql = "SELECT * FROM shows s, venues v, zipcodes z WHERE "
    . "s.show_venue LIKE v.venue_id AND "
    . "v.venue_zip LIKE z.zipcode "
    . "ORDER BY s.show_start "
    . "LIMIT {$offset},{$page_rows}";
$result = mysql_query($sql);

?>
[/code]

[junglyboi]

wow i really appreciate that... because we're covering concerts across the country, our database will get large very quickly.... i'm hoping with the addition of the WHERE show_end > NOW(), that it will cut way back on how many each query is pulling in, that way it's not querying all archived shows in addition to the current ones.... am i on the right track with that?