junglyboi

sweet... works perfectly :D thaaaanks :D

I am having some trouble parsing a mysql timestamp into a php date(). In my 'promoters' table, I have a timestamp column which defaults to now() so that when I update a record, the timestamp updates, and I have a "last update" value to work with. I'm using php to create an xml page.  Here's my code: [code] <?php header("Content-type: text/xml"); // index.php echo "<url>"; echo "<loc>http://www.ticketstone.com/</loc>"; echo "<lastmod>"; $mod = date('Y-m-d\TH:i:s-05:00', strtotime(now)); echo $mod;     echo "</lastmod>";     echo "<changefreq>hourly</changefreq>";     echo "<priority>0.7</priority>"; echo "</url>"; // events.php q=all "All Current Events" echo "<url>"; echo "<loc>http://www.ticketstone.com/events.php?q=all</loc>"; echo "<lastmod>"; $mod = date('Y-m-d\TH:i:s-05:00', strtotime(now)); echo $mod;     echo "</lastmod>";     echo "<changefreq>hourly</changefreq>";     echo "<priority>0.8</priority>"; echo "</url>"; // events.php q=pro "All Current Events by Promoter" $db = mysql_connect('localhost', 'user', 'password'); mysql_select_db('database', $db); $result = mysql_query("SELECT * FROM promoters ORDER BY promoter_id", $db); while ($p = mysql_fetch_array($result)) { echo "<url>"; echo "<loc>http://www.ticketstone.com/events.php?q=pro&amp;v="; echo $p['promoter_id']; echo "</loc>"; $time = date('Y-m-d\TH:i:s-05:00', strtotime($p['promoter_mod'])); echo "<lastmod>" . $time . "</lastmod>"; echo "<changefreq>hourly</changefreq>"; echo "<priority>0.3</priority>"; echo "</url>"; } echo "</urlset>"; ?> [/code] promoter_mod is a timestamp column in my table so that I always have the last update time.  So for the first record (promoter_id "1000"), the promoter_mod is "20061120071925".  When I use the strtotime($p['promoter_mod']), I get a result of -1, which then of course gives me the infamous 69 date when I try to format it with date().  Can anyone help me with the code to be able to take the timestamp in mysql and turn it into "Y-m-d\TH:i:s-05:00"?  Puhleese? Chris

ok this all worked great... thanks for everyone's help so far... now i've gone a step further... and on a shows' page like www.ticketstone.com/shows.php?show=61 i want the user to be able to click the band's name, and it take it to www.ticketstone.com/events.php?q=art&art=[artist_id] so from the example above, if they click on Superchick, it would take them to www.ticketstone.com/events.php?q=art&art=1005. now that's all working just great for me... here's the question... artist_id in the 'artists' table can match with any of the following in the 'shows' table... headliner_1, headliner_2, headliner_3, etc. this is the SELECT i'm using so far ($art is the artist_id passed from the URL) [code]SELECT * FROM shows,venues,zipcodes WHERE $art LIKE headliner_1 AND show_venue LIKE venue_id AND venue_zip LIKE zipcode AND show_end > NOW() ORDER BY show_start[/code] so ya get what i mean? it works if the artist is headliner_1, but i need to make it also look in headliner_2, headliner_3 and so on... but keeping the rest of the line where it says AND show_venue LIKE venue_id, etc.  hope that makes sense.

oh you know what i think you did answer my question... (thinking out loud) cus if the cell for headliner 2 is contained in an IF statement, then i can check the main query to see if headliner_2 contains info... then if it does, SELECT headliner_2 info from the artist table... i'll give that a try :D

there is always a headliner_1, but not always headliner_2, etc. so I know I'll have to use some IF statements to determine if those cells in the table should populate... so for example, if there are 5 headliners, my table will have 5 cells across, if there's only 1 headliner, then just 1 will be in the center... i get how to do all that but once i've determined, which headliners exist for the show, i don't know how to put them in the right place with the right info ... example: [code] <td>headliner 1 info</td><td>headliner 2 info</td><td>headliner 3 info</td><td>and so on... [/code]

ok i have a page where i'm trying to show the details of an upcoming concert.  An example is www.ticketstone.com/shows.php?show=1 I have all the tables in place and such.  In my 'shows' table, I have a field for 'headliner_1', 'headliner_2', 'headliner_3' and so on.  Then I have an 'artists' table, where 'artist_id' should match up with 'headliner_1' or 'headliner_2' and so on. Ok, here's my question.  On the page where I'm displaying the show details, I need to show a list of all artists playing that show.  So I would show headliner_1, headliner_2, headliner_3, and so on as long as they are not null.  But I need to pull in their name, website and additional info from the artists table.  So how do I pull the informaiton for say headliner_1 in one spot, headliner_2 in another spot, etc.? I probably need help with the SELECT statement as well as knowing how to tell it that in this specific spot on the page, I want the information for headliner_1, and over here I want the info for headliner_2, etc.  Make sense?

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?

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

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";     }   ?>