php query to select min date

[jas4]

hi,

 

im trying to get a minimum date from my database, the current way im doing it is:

 

SELECT * FROM events where Date > NOW() OR Date = '9999-12-12' ORDER BY Date ASC LIMIT 1

 

 

this works in terms of code, but it doesnt do what I want it to do. Its the ORDER BY Date, that is the problem:

 

What I want to select is

 

The next date OR a date = 9999-12-12

 

I tried searching for a query like "select MIN date" but couldnt find anything on it, plus the date has to be in the future, hence the need for DATE > NOW()

 

quite confusing I know!! but any advice?

 

thanks

 

 

[freakstyle]

hey there,

it looks like you are trying to order numerically by date, but your date isn't numeric, what you should do is store your dates in a unix timestamp, and when you need to display them in a friendly way just convert it with a simple function.

 

ref: http://us.php.net/manual/en/function.mktime.php

 

good luck

 

 

 

 

[jas4]

thanks for your reply. Thats probably what I should do in good practise, but here is what I have done to work around the problem:

 

$get_next = mysql_query("SELECT * FROM events where Date > NOW() ORDER BY Date ASC LIMIT 1") or die (mysql_error());
while ($row = mysql_fetch_assoc($get_next)) {$next_id = $row['eventid'];}


$getDetails = mysql_query("SELECT * FROM events where eventid = '$next_id' OR Date = '9999-12-12' ORDER BY rand() ASC LIMIT 1") or die (mysql_error());

 

I use 2 queries:

 

1. to get the entry id with the next date

2. use this entry id in the second query to get a random record where date = $next_id or date = '9999-12-12'

 

 

so I've got it working as I want it but is this bad practice - having to perform 2 queries instead of 1? is this something that will slow down the speed when a user visits my site?

 

cheers

[Barand]

do you mean

 

 

SELECT * FROM events

WHERE Date = (SELECT MIN(Date) FROM events WHERE Date > CURDATE())

[jas4]

yeh thats exactly the kinda query i was looking for...

 

SELECT * FROM events
WHERE Date = (SELECT MIN(Date) FROM events WHERE Date > CURDATE())

 

would it be possible to do something like:

 

 

SELECT * FROM events
WHERE Date = (SELECT MIN(Date) FROM events WHERE Date > CURDATE() AND Date = '9999-12-12')

 

 

[Barand]

WHERE Date > CURDATE() AND Date = '9999-12-12'

 

The only records where both those conditions are true are those with date = 9999-12-12, so those are the only ones you will get.

 

Do you mean

SELECT * FROM events
WHERE (Date = (SELECT MIN(Date) FROM events WHERE Date > CURDATE() ))
      OR (Date = '9999-12-12')