Database Data Comparison Question

[hosker]

I currently am working on a fantasy golf site. I have a table where I store each users picks by tournament. I want to be able to write a small snippet of code that can compare the current date and time to the Start Time in the database and display that weeks picks. I can do this the long way by extracting the date in each row and comparing it. Is there a way that I can do it without having to write out the code for each row in the database?

 

this is the current code I have to do it the long way:

 

<?php
$result2 = mysql_query("SELECT * FROM `2010_Picks` WHERE Id = 2");
echo "<table border='1'>
<tr>
<th>Tournament</th>
<th>Player</th>
<th>Golfer</th>
</tr>";
while($row2 = mysql_fetch_array($result2))
{
$result = mysql_query("SELECT * FROM `2012_tournaments` WHERE id = 2");

while($row = mysql_fetch_array($result))
  {
  $time1 = strtotime($row['start_date_time']);  
  if ($time2 < $time1)
{
  
  echo "<tr>";
  echo "<td>" . $row2['tournament'] . "</td>";
  echo "<td>" . $row2['player'] . "</td>";
  echo "<td>" . $row2['golfer'] . "</td>";
  echo "</tr>";

}
else
echo "This did not work";
}}echo "</table>";
?>

 

 

So here is a small sample of the data I have stored in my database:

 

 

Date

Start Time

Tournament

Jan. 2-92012-01-06 16:10:00Hyundai Tournament of Champions(Monday finish) Jan. 9-152012-01-12 16:00:00Sony Open in HawaiiJan. 16-222012-01-19 10:00:00Humana Challenge in partnership with the Clinton Foundation  Jan. 23-292012-01-26 10:00:00Farmers Insurance Open  Jan. 30-Feb. 5 2012-02-02 09:00:00Waste Management Phoenix Open  Feb. 6-122012-02-09 10:00:00AT&T Pebble Beach National Pro-Am  Feb. 13-192012-02-16 11:00:00Northern Trust Open  Feb. 20-262012-02-23 09:25:00World Golf Championships-Accenture Match Play Championship  Feb. 27-March 42012-03-01 07:00:00 The Honda Classic  March 5-112012-03-08 07:00:00World Golf Championships-Cadillac Championship  March 12-182012-03-15 07:00:00Transitions Championship  March 19-252012-03-22 07:00:00Arnold Palmer Invitational presented by MasterCard  March 26-April 12012-03-29 08:00:00Shell Houston Open  April 2-82012-04-05 07:00:00Masters Tournament # April 9-152012-04-12 07:00:00RBC Heritage  April 16-222012-04-19 09:00:00Valero Texas Open  April 23-292012-04-26 07:00:00Zurich Classic of New Orleans  April 30-May 62012-05-03 07:00:00Wells Fargo Championship  May 7-132012-05-10 07:00:00THE PLAYERS Championship  May 14-202012-05-17 09:00:00HP Byron Nelson Championship  May 21-272012-05-24 09:00:00Crowne Plaza Invitational at Colonial  May 28-June 32012-05-31 07:00:00 the Memorial Tournament presented by Nationwide Insurance  June 4-102012-06-07 08:00:00FedEx St. Jude Classic  June 11-172012-06-14 11:00:00U.S. Open # June 18-242012-06-21 07:00:00Travelers Championship  June 25-July 12012-06-28 07:00:00AT&T National  July 2-82012-07-05 07:00:00THe Greenbrier Classic  July 9-152012-07-12 08:00:00John Deere Classic  July 16-222012-07-19 01:00:00The Open Championship # July 23-292012-07-26 07:00:00RBC Canadian Open  July 30-Aug. 52012-08-02 07:00:00World Golf Championships-Bridgestone Invitational  Aug. 6-122012-08-09 07:00:00PGA Championship # Aug. 13-190000-00-00 00:00:00Off weekAug. 20-262012-08-23 07:00:00The Barclays  Aug. 27-Sept. 32012-08-30 07:00:00Deutsche Bank Championship  (Monday Finish) Sept. 3-92012-09-06 07:00:00BMW Championship  Sept. 10-160000-00-00 00:00:00Off weekSept. 17-232012-09-20 10:00:00TOUR Championship by Coca-Cola 

 

 

[scootstah]

Yes. Take a look at these : http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

[hosker]

None of that really tells me how to compare the current timestamp with the fields in the database and pulley the data from the row that I want. Could you give me a few pointers here?

[kicken]

SELECT
   *
FROM 2012_tournaments
WHERE
   start_date_time < CURRENT_TIMESTAMP

 

That would return all the rows where the start_date_time column has a date/time less than the current date/time.  It's not clear from your code above what exactly your trying to do, so it's hard to provide much further help.

 

The above query assumes that you have correctly defined the start_date_time column as a DATETIME datatype.  If you did something silly like make it a VARCHAR you'll have to change it.

 

[mikosiko]

For a start in your code you are not defining what is $time2, secondly if your tables `2010_picks` and `2012_tournaments` are in some way related (the field ID maybe ?) then you don't need those 2 SELECT in your code and those nasty loops, for that case you just need a JOIN between the tables and compare your start_date_time as kichen suggested