Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 14 of 14
  1. #1
    New Coder
    Join Date
    Jun 2006
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Search query help

    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)

    10-08-2009
    13-08-2009
    01-09-2009
    11-10-2009

    12-12-2009

    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.

    Can I do this within one query?

    Thanks
    Last edited by space cowboy; 09-09-2009 at 10:43 AM.

  • #2
    Regular Coder
    Join Date
    Jan 2008
    Location
    Geneva, Switzerland
    Posts
    413
    Thanks
    12
    Thanked 29 Times in 29 Posts
    Guess you want to retrieve

    01-09-2009
    and
    11-10-2009

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

  • #3
    New Coder
    Join Date
    Jun 2006
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    yup! thats correct. Is there an easy way to do this? (I realise it was wrong in my example, I have rectified this).
    Last edited by space cowboy; 09-09-2009 at 10:44 AM.

  • #4
    Regular Coder
    Join Date
    Jan 2008
    Location
    Geneva, Switzerland
    Posts
    413
    Thanks
    12
    Thanked 29 Times in 29 Posts
    You want to do that in a single query?
    Chuck Norris counted to infinity.
    Twice.

  • #5
    New Coder
    Join Date
    Jun 2006
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    yes, is that possible?

  • #6
    Regular Coder
    Join Date
    Jan 2008
    Location
    Geneva, Switzerland
    Posts
    413
    Thanks
    12
    Thanked 29 Times in 29 Posts
    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...

    PHP Code:
    <?
    $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...
    Chuck Norris counted to infinity.
    Twice.

  • #7
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,043
    Thanks
    2
    Thanked 316 Times in 308 Posts
    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.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #8
    Regular Coder
    Join Date
    Jan 2008
    Location
    Geneva, Switzerland
    Posts
    413
    Thanks
    12
    Thanked 29 Times in 29 Posts
    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?
    Chuck Norris counted to infinity.
    Twice.

  • #9
    New Coder
    Join Date
    Jun 2006
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    yes, they are already stored this way in 2009-08-08 format.

    So, how would I make this query?

  • #10
    Regular Coder
    Join Date
    Jan 2008
    Location
    Geneva, Switzerland
    Posts
    413
    Thanks
    12
    Thanked 29 Times in 29 Posts
    Did you try the code I suggested?
    Chuck Norris counted to infinity.
    Twice.

  • #11
    New Coder
    Join Date
    Jun 2006
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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:

    2009-07-12
    2009-08-01
    2009-09-20

    2009-09-30

    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.

  • #12
    Regular Coder
    Join Date
    Jan 2008
    Location
    Geneva, Switzerland
    Posts
    413
    Thanks
    12
    Thanked 29 Times in 29 Posts
    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

    2009-07-12
    2009-08-01
    2009-09-20
    2009-09-30

    The query should not return anything...

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

    Your query would then be something like:

    PHP Code:
    <?
    $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...
    Chuck Norris counted to infinity.
    Twice.

  • #13
    Regular Coder
    Join Date
    Mar 2008
    Posts
    235
    Thanks
    6
    Thanked 21 Times in 20 Posts
    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:

    PHP Code:
    $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'];
      }
      else
      {
         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!
    Andrew Sharman
    Web designer, developer and programmer.

    If you found my post helpful, why not give thanks! :)

  • #14
    New Coder
    Join Date
    Jun 2006
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •