CODEWITHSUNDEEP
phpmysqlsql
clamp
clamp

Reputation: 34026

Select next event

I would like to select the next event from my table. the date of the event is stored as a VARCHAR in the format Y-m-d in a column called date.

But i am unsure how to compare VARCHARS/strings in sql. I tried the following but it gives me an event in the past.

$d = date("Y-m-d");
mysql_query("SELECT * FROM events WHERE date>$d ORDER BY date ASC LIMIT 1,1");

Upvotes: 0

Views: 56

Answers (2)

shrmn
shrmn

Reputation: 1523

SELECT * FROM events WHERE active=1 AND DATEDIFF(date, CURDATE()) >= 0 ORDER BY date ASC LIMIT 1,1;

This will select the next event inclusive of today's events. IF you do not want to include today's events, change >= to >

sqlfiddle: http://sqlfiddle.com/#!2/f205a3/9

Upvotes: 1

Marc B
Marc B

Reputation: 360702

You haven't quoted your date string, so your query will be literally somethign like

... AND date > 2014-04-11 ORDER ...

Since there's no quotes, MySQL is free to interpret your date as a simple mathematical subtraction, and you end up doing

... AND date > 1999 ORDER ...

Try

... AND date > curdate() ...

instead. There's no point in having PHP generate a date and passing it into mysql, when mysql can generate that date perfectly well on its own.

As well, having PHP generate the date can lead to race conditions. E.g. PHP generates "today 11:59:59pm", but mysql actually executes as "tomorrow 00:00am". Maybe not relevant to you, but in banking "overnight run" code, this could cost someone literally millions of dollars.

Upvotes: 1

Related Questions