View Full Version : Search query help

space cowboy
09-09-2009, 10:50 AM
Anyone got any ideas on how best to do this search query?

Basically, I have a list of dates within records. What I need is a search query that will retrieve the first 'passed' date and the next 'future' date (limited to just the 2 records).

So for example, these would be my rows of data in the database (UK format time stamp)


So The 2 rows in bold are the ones that I need the search to display. The first (13-08-2009) is obviously in the past, but the second (01-09-2009) is in the future (therfor its not a straight forward query in terms of displaying 2 results based on past or post date. :eek:

Can I do this within one query?


09-09-2009, 11:37 AM
Guess you want to retrieve


which is the last date compared to today and the next one, right?
Your example shows 2 dates in the past...

space cowboy
09-09-2009, 11:42 AM
yup! thats correct. Is there an easy way to do this? (I realise it was wrong in my example, I have rectified this).

09-09-2009, 11:43 AM
You want to do that in a single query?

space cowboy
09-09-2009, 11:44 AM
yes, is that possible?

09-09-2009, 11:47 AM
I do not know how to do that in a single query. But I am a kind of newbie...

I would do something like that using two queries, but I guess there must be an easier way...

$today = date("d-m-Y");

$sql = "select * from TABLENAME where DATE<'". $today ."' order by DATE desc limit 0,1";

$sql = "select * from TABLENAME where DATE>'". $today ."' order by DATE asc limit 0,1";

Not tested...

09-09-2009, 12:01 PM
Since you cannot do greater-than/less-than comparisons with dates in that format, your first step would be to store the dates in the database as yyyy-mm-dd values in a DATE data type.

09-09-2009, 12:07 PM
Oh, I actually thought that you could specify that format in the DATE mysql field.

Then yes, you will first need to store your dates in a DATE field format yyyy-mm-dd in your database. It is easy to convert your dates for display so that they keep the format that you want.

Do you understand the point?

space cowboy
09-09-2009, 12:17 PM
yes, they are already stored this way in 2009-08-08 format.

So, how would I make this query?

09-09-2009, 01:37 PM
Did you try the code I suggested?

space cowboy
09-09-2009, 04:18 PM
The code you gave was 2 seperate queries though.

Also, I am stuck on a similar thing in that I need the seach query to search between dates.

So if I set a date range of 2009-08-08 and 2009-09-01

If my database reads:


My search query needs to display all rows that fall within the date range, meaning it would need to display the 2 rows in bold (ie one date in the past (which is the current time frame we are in) and one future date).
Doing it as 2 seperate queries wont work.

09-09-2009, 05:00 PM
But that is another question now. Not related to your first post.

And again, your example looks wrong...
If your query is 'between' 2009-08-08 and 2009-09-01

and your database contains


The query should not return anything...

Anyway... let's assume this is a typo...

Your query would then be something like:

$start_date = "2009-08-08";
$end_date = "2009-09-01";
$sql = "select * from TABLENAME where DATE>'". $start_date."' and DATE<'". $end_date."' order by WHATYOUWANT asc *or* desc";

You may want to use <= and >= in your query if you want to include records that are equal to your start or end dates...

09-09-2009, 05:21 PM
As said before if you can't/won't change the date syntax of your tables column parsing it with PHP is better.

a simple loop which would look SOMETHING like this:

$sql = 'select `date` FROM `table_name`';
$results = mysql_query($sql);
$today = time();

$after = '';
while ($row = mysql_fetch_array($results))
if (strtodate($row['date']) <= $today)
$before = $row['date'];
if (empty($after))
$after = $row['date'];

I just wrote that freehand, but you should definately get what you want from that. Otherwise you need to sort out your date column and then do a double SQL statement

Hope it helps! :thumbsup:

space cowboy
09-09-2009, 05:49 PM
Ok think of it like this:

A Restraunt has set menus.

Menu1 runs from 2009-06-01 (and it ends when the next one starts)
Menu2 runs from 2009-07-01
Menu3 runs from 2009-08-01
Menu4 runs from 2009-09-01

So, I am doing a search. I want to know what menus are running between certain dates.

I search 2009-07-20 - 2009-08-20.
Therfor this needs to display Menu2 and Menu3. (The start date of Menu2 doesnt fall between that date range, but menu2 is the menu that is running on 2009-07-20.)

So this is why the search query needs to be able to display one search row from the past and one from the future (so a simple "SELECT row WHERE date > $current-time-stamp) wont work.