selecting where values match

ucbones

Hi,

Couldn't think of a short way of describing my problem, so apologies for the cryptic subject.

I have a table (eventdetails) storing names and details of event types, and specifying whether it is a "big event" (i.e. newsworthy).

I have a separate table (calendar) storing the dates and times of these events, and the type of event (this value is the ID column of the eventdetails table.

I wish to print out the details of the next big event in the calendar (the date/time info is stored as a UNIX tstamp in the column tstamp, in the table "calendar").

I have no idea where to begin with this query, I can think of a very inefficient method involving while loops, but I'm sure there is an elegant method using MySQL.

Any ideas?

Many thanks,

ucbones

here's my inefficient method, it solves the problem, but I'm sure it's not the best method...

function nextbigevent(){
$result=mysql_query("SELECT ID FROM eventdetails WHERE bigevent=1");
$maxid=0;
$now=mktime();
$soonest=$now+31536000;
while ($row = mysql_fetch_array($result)){
$query=mysql_query("SELECT ID, start FROM calendar WHERE type='".$row["ID"]."' AND end>$now ORDER BY start ASC LIMIT 1");
$id=mysql_fetch_array($query, MYSQL_BOTH);
if (($id["start"] < $soonest) and ($id["start"] > $now)){
$soonest=$id["start"];
$currentid=$id["ID"];
}
}
if (!$currentid) return false;
else return $currentid;
}

barand

SELECT e.*, c.start FROM eventdetails e
INNER JOIN calendar c ON e.id = c.type
WHERE c.start > cur_date() and e.bigevent = 1
ORDER BY c.start LIMIT 1

BTW for dates such as as event dates etc, use field type DATE or DATETIME and not TIMESTAMP. If you update a field in the table, a timestamp will automatically record the date/time of the update, destroying the event date.

hth

ucbones

many thanks, cur_date didn't seem to work for me (couldn't find it in the MySQL manual either...), worked great with unix_timestamp though!!!

$result=mysql_query("SELECT e. * , c.start, c.ID AS nextid
		FROM eventdetails e
		INNER JOIN calendar c ON e.id = c.type
		WHERE c.start > unix_timestamp() AND e.bigevent = 1
		ORDER BY c.start
		LIMIT 1 ");
$id=mysql_fetch_array($result);
return $id["nextid"];

I don't use MySQL timestamps, I store PHP's timestamp as an integer, find it easier to manipulate. Just personal preference though.

ucbones

barand

My mistake. Its either

CURDATE()
or
CURRENT_DATE

to test for current date in MySQL