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...
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?
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";
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?
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?
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'];
$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:
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.