Select query - all dates in future.

[Dopple]

I am trying to select all records which have a date (integer timestamp) greater than the current day. I thought I had it with the code below but I guess I was wrong. Can anyone point me in the reight direction please? Am I wrong with (CURDATE())?

$result = mysql_query("SELECT * FROM gigs WHERE gig_date > (CURDATE()) order by gig_date ASC "); 

[chazzy]

what about CURRENT_TIMESTAMP()?

Wait, you have this as an int? why?! you should always use a date/time column and then format the results. costs less to do that than to store an int and then convert it to something else.

[Dopple]

Thanks. I take it I would just format it in the same way as I did with the int?

$date = $row["gig_date"];
$date = date("l tS of F y", $date); 

I'll try that out.

[Dopple]

Ok I've changed it to DATE format so the date's are listed as 2006-03-23 but I'm still insure as to how to get all the records with a date in the future. What I have now is

$now = date("Y-m-d");
$result = mysql_query("SELECT * FROM gigs WHERE gig_date > $now order by gig_date ASC "); 

the whole code is below
(edited)

<?php
  include( "pagehead.php" );
  include( "misc.inc" );
  $result = mysql_query("SELECT * FROM gigs WHERE gig_date > NOW() order by gig_date ASC ");
?>
<div class="content">
<h1>Gigs listing</h1>
<h2>Upcoming gigs</h2>
<?php
  while ($row = mysql_fetch_assoc($result)) {
  $date = $row["gig_date"];
  $date = date("d/m/Y", $date);
  $location = $row["gig_location"];
  $info = $row["gig_description"];
  echo "<p>$date<br />$location<br />$info</p>";
}
?>
</div>
<div class="content">
<h2>Previous gigs</h2>
<?php
  $result2 = mysql_query("SELECT * FROM gigs WHERE gig_date < NOW() order by gig_date ASC ");
  while ($row2 = mysql_fetch_assoc($result2)) {
  $date2 = $row2["gig_date"];
  $date2 = date("d/m/Y", $date2);
  $location2 = $row2["gig_location"];
  $info2 = $row2["gig_description"];
  echo "<p>$date2<br />$location2<br />$info2</p>";
}
?>
<p>The band can't remember dates, but you may have seen them at one of these many venues they've played.</p>
<hr />
<p class="gig">Droothy Neebors - Dundee</p>
<p class="gig">The New Westport Bar - Dundee</p>
<p class="gig">The Beat Bar - Dundee</p>
<p class="gig">The Rothes Halls - Glenrothes</p>
<p class="gig">The Subway - Edinburgh</p>
<p class="gig">The Path Tavern - Kirkcaldy</p>
</div>
</body>
</html>

[chazzy]

your query should be one of these:

SELECT * FROM gigs WHERE gig_date > NOW() order by gig_date ASC

SELECT * FROM gigs WHERE gig_date > CURDATE() order by gig_date ASC

You're passing it in a string when you do it at the application side instead of the db side.

[Dopple]

Thanks that's sorted out the problem of precious and future gigs but now the problem is that the DATE data is not being shown. It's just showing 01/01/1970. The data is in the database correctly. It's just not showing as planned. I reckon it's to do with the date() function as it's not a time stamp I'm using. can someone help here? I've amended the code above to show a true representation of gigs.php.

[chazzy]

select the date format of the column.

* returns raw data, you should look back at the article named "Why * is bad." or something like that.

use this:

$sql = "SELECT date_format(`gig_date`,'%d/%m/%Y') gig_date, `gig_location`, `gig_description` FROM your_table....";
//then do the query
  $gig_date = $row2['gig_date'];
  $location2 = $row2["gig_location"];
  $info2 = $row2["gig_description"];
//and continue 

and if you want to format your date differently, http://dev.mysql.com/doc/refman/4.1/...functions.html and look for the function "DATE_FORMAT"

[Dopple]

Thanks Chazzy. That's sorted it. It's ok, that's how the date should be. At least now I know if the user would prefer a DATETIME column rather a DATE one I can get it working.

[chazzy]

FYI, the functions work the same on DATETIME, DATE, and TIMESTAMP types.

at least they're supposed to.

even if you used an int column, you could use a datetime function (i think that exists) and convert.