SitePoint Sponsor

User Tag List

Results 1 to 21 of 21
  1. #1
    SitePoint Member
    Join Date
    Oct 2007
    Location
    United Kingdom
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Date range problems...Please have a look

    Hello

    I have been working on a website which requires a user to be able to search between certain date ranges.

    I have a table which consists of two columns which are used for matching and comparing the dates from. Each user is able to submit a travel request and will be travelling within a certain time.

    For example:-

    date_from (The day the user is travelling)
    date_to (The day upto travelling, or also known as the last day of travel)

    date_from & date_to are also the names of the columns on the table.

    Now i would like the query to also pickup users which are travelling between these point of times, nothing less than the "date_from" and nothing greater than the "date_to". I have had several play around with queries and some return results and other do not. Now the ones which have returned results, also returns the result if the "date_to" is greater than one matched within the table, this should not be possible! Can somebody help me here.... I have given example of the kind of query i am working with.

    PHP Code:
    $sql "SELECT travelling_details.*, user_table.* FROM travelling_details LEFT JOIN user_table ON travelling_details.user_id=user_table.id WHERE (date_from <= '$date1') and (date_to >= '$date2')"
    Some insight would be great thankyou!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by reubenrd View Post
    Code:
    WHERE (date_from <= '$date1') and (date_to >= '$date2')";
    are you sure these two different date values are set correctly?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Oct 2007
    Location
    United Kingdom
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    are you sure these two different date values are set correctly?
    How do you mean, are they set properly??? They are stored as 2012-02-02 (Y-m-d) and search is being performed on the same format. I stored date using string for some reason, but this shouldnt matter.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    no, i was asking if the $date1 and $date2 values are set correctly

    but having the date columns as strings is probably the problem
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Oct 2007
    Location
    United Kingdom
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    no, i was asking if the $date1 and $date2 values are set correctly

    but having the date columns as strings is probably the problem
    This was not the problem as I knew it would not be, and yes the values are set properly.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    oh, well, then it must be something else

    perhaps you could set up a test case for us to work with?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Oct 2007
    Location
    United Kingdom
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have shortened the query just to this below for testing.

    WHERE date_from <= $date1

    Technically that should grab any date value from "date_from" that is GREATER OR EQUAL to $date1, is that correct? And in this case that query returns nothing.

    Value stored in 'date_from' = 2012-02-09
    Values being searched:
    2012-02-08
    2012-02-09
    2012-02-10
    2012-02-11


    Nothing is returned from the query! Must be wrong here.... If i change it too:
    WHERE date_from <= $date1

    Then it displays dates both sides of the range which is also incorrect.

  8. #8
    SitePoint Member
    Join Date
    Oct 2007
    Location
    United Kingdom
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by reubenrd View Post
    This was not the problem as I knew it would not be, and yes the values are set properly.
    I got this lil warning when i searched the db within an SQL Terminal.

    "Truncated incorrect DOUBLE value: '2012-02-09'"

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by reubenrd View Post
    Technically that should grab any date value from "date_from" that is GREATER OR EQUAL to $date1, is that correct?
    no, " <= " actually means LESS THAN OR EQUAL


    by test case, i meant for you to provide a CREATE TABLE statement along with several INSERT statements, hopefully mirroring your actual table -- the easiest way to do this is to dump the table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Oct 2007
    Location
    United Kingdom
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    no, " <= " actually means LESS THAN OR EQUAL


    by test case, i meant for you to provide a CREATE TABLE statement along with several INSERT statements, hopefully mirroring your actual table -- the easiest way to do this is to dump the table
    But wud that not mean, 'date_from' is < $valuein ($valuein is greater than date_from). Doing it the other way round 'date_from' >= $valuein (in my eyes is saying $valuein is smaller than 'date_from') Ahhhh im so confused!!!!!!

  11. #11
    SitePoint Member
    Join Date
    Oct 2007
    Location
    United Kingdom
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    no, " <= " actually means LESS THAN OR EQUAL


    by test case, i meant for you to provide a CREATE TABLE statement along with several INSERT statements, hopefully mirroring your actual table -- the easiest way to do this is to dump the table
    Code:
    ===Database reubenrd_dontflysolo
    
    == Table structure for table travelling_details
    
    |------
    |Column|Type|Null|Default
    |------
    |//**id**//|int(11)|No|
    |user_id|int(11)|Yes|NULL
    |date_from|date|Yes|NULL
    |date_to|date|Yes|NULL
    |location_from|int(11)|Yes|NULL
    |location_to|int(11)|Yes|NULL
    |extra|text|Yes|NULL
    == Dumping data for table travelling_details
    
    |2|10|2012-02-09|2012-02-16|223|154|Would like to travel with someones who&amp;#039;s been there before preferably.
    == Table structure for table travelling_details
    
    |------
    |Column|Type|Null|Default
    |------
    |//**id**//|int(11)|No|
    |user_id|int(11)|Yes|NULL
    |date_from|date|Yes|NULL
    |date_to|date|Yes|NULL
    |location_from|int(11)|Yes|NULL
    |location_to|int(11)|Yes|NULL
    |extra|text|Yes|NULL
    Code:
    mysql> select * from travelling_details;
    +----+---------+------------+------------+---------------+-------------+-----------------------------------------------------------------------------+
    | id | user_id | date_from  | date_to    | location_from | location_to | extra                                                                       |
    +----+---------+------------+------------+---------------+-------------+-----------------------------------------------------------------------------+
    |  2 |      10 | 2012-02-09 | 2012-02-16 |           223 |         154 | Would like to travel with someones who's been there before preferably. |
    +----+---------+------------+------------+---------------+-------------+-----------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql>
    Code:
    mysql> select * from travelling_details where date_from <= 2012-09-22;
    Empty set, 2 warnings (0.00 sec)
    
    mysql> select * from travelling_details where date_from >= 2012-09-22;
    +----+---------+------------+------------+---------------+-------------+-----------------------------------------------------------------------------+
    | id | user_id | date_from  | date_to    | location_from | location_to | extra                                                                       |
    +----+---------+------------+------------+---------------+-------------+-----------------------------------------------------------------------------+
    |  2 |      10 | 2012-02-09 | 2012-02-16 |           223 |         154 | Would like to travel with someones who&#039;s been there before preferably. |
    +----+---------+------------+------------+---------------+-------------+-----------------------------------------------------------------------------+
    1 row in set, 2 warnings (0.00 sec)
    
    mysql>

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i was kinda hoping you would actually use mysqldump, or, alternatively exporting the table from phpmyadmin, which is where it looks like you've copied the structure from

    further, it looks like you've actually used the correct datatype, DATE, instead of the strings which you alluded to earlier

    Quote Originally Posted by reubenrd View Post
    Code:
    mysql> select * from travelling_details where date_from >= 2012-09-22;
    dates need to be enclosed in quotes, otherwise you have arithmetic subtraction being performed with three integers
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Member
    Join Date
    Oct 2007
    Location
    United Kingdom
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i was kinda hoping you would actually use mysqldump, or, alternatively exporting the table from phpmyadmin, which is where it looks like you've copied the structure from

    further, it looks like you've actually used the correct datatype, DATE, instead of the strings which you alluded to earlier

    dates need to be enclosed in quotes, otherwise you have arithmetic subtraction being performed with three integers
    The post above consists of a export via text.

    select * from travelling_details where date_from >= '2012-02-01'; - Returns a match
    select * from travelling_details where date_from <= '2012-02-01'; - Returns no match

    There queries are single range queries which i a currently testing, i am still yet to test within a range.

    Here is an SQL DUMP

    Code:
    -- MySQL dump 10.13  Distrib 5.5.18, for Linux (x86_64)
    --
    -- Host: localhost    Database: dontflysolo
    -- ------------------------------------------------------
    -- Server version	5.5.18-log
    
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8 */;
    /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
    /*!40103 SET TIME_ZONE='+00:00' */;
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
    
    --
    -- Table structure for table `travelling_details`
    --
    
    DROP TABLE IF EXISTS `travelling_details`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `travelling_details` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `user_id` int(11) DEFAULT NULL,
      `date_from` date DEFAULT NULL,
      `date_to` date DEFAULT NULL,
      `location_from` int(11) DEFAULT NULL,
      `location_to` int(11) DEFAULT NULL,
      `extra` text,
      PRIMARY KEY (`id`),
      KEY `date_from` (`date_from`,`date_to`),
      KEY `date_to` (`date_to`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Dumping data for table `travelling_details`
    --
    
    LOCK TABLES `travelling_details` WRITE;
    /*!40000 ALTER TABLE `travelling_details` DISABLE KEYS */;
    INSERT INTO `travelling_details` VALUES (2,10,'2012-02-09','2012-02-16',223,154,'Would like to travel with someones who's been there before preferably.');
    /*!40000 ALTER TABLE `travelling_details` ENABLE KEYS */;
    UNLOCK TABLES;
    /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
    
    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
    /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
    
    -- Dump completed on 2012-02-06 13:47:02

  14. #14
    SitePoint Member
    Join Date
    Oct 2007
    Location
    United Kingdom
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i was kinda hoping you would actually use mysqldump, or, alternatively exporting the table from phpmyadmin, which is where it looks like you've copied the structure from

    further, it looks like you've actually used the correct datatype, DATE, instead of the strings which you alluded to earlier

    dates need to be enclosed in quotes, otherwise you have arithmetic subtraction being performed with three integers
    Code:
    select * from travelling_details where date_from >= '2012-02-09' and date_to <= '2012-02-18';
    Returns that there is a value in the Database, which I now assume its working fine!!!!

  15. #15
    SitePoint Member
    Join Date
    Oct 2007
    Location
    United Kingdom
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But if date_from is passed that 2012-02-09 no value is returned which i also assume is correct. How would i make it this query knows that someone is still travelling within that date range?? For example someone is travelling between dates "2012-02-11 ----> 2012-02-14"....

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by reubenrd View Post
    How would i make it this query knows that someone is still travelling within that date range??
    this should help you --> http://www.sitepoint.com/forums/show...ange-under-SQL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Member
    Join Date
    Oct 2007
    Location
    United Kingdom
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Which in case would equal this query here:

    WHERE date_to >= $date1 AND date_from <= $date2
    or
    Code:
    select * from travelling_details WHERE date_to >= 2012-02-09 AND date_from <= 2012-02-16;

    $date1 (being the date from/ also know as the starting point)
    $date2 (being the date up to/ also known as the end point)

    Query still does not work! Thankyou for trying to help solve this problem, means a lot.

  18. #18
    SitePoint Member
    Join Date
    Oct 2007
    Location
    United Kingdom
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Think i might have it sorted, will reply shortly!

  19. #19
    SitePoint Member
    Join Date
    Oct 2007
    Location
    United Kingdom
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Working nicely within the SQL shell, but PHP is now the problem hahaha, I just cant get my head around it, the date is posted correctly and im using quotations within the php.

    PHP Code:
        $sql "SELECT travelling_details.*, user_table.* FROM travelling_details LEFT JOIN user_table ON travelling_details.user_id=user_table.id WHERE date_to >= '$date1' AND date_from <= '$date2'"

  20. #20
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Do an echo of $sql and post the result.

  21. #21
    SitePoint Member
    Join Date
    Oct 2007
    Location
    United Kingdom
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Do an echo of $sql and post the result.
    Problem has been SOLVED. Thankyou all for your help especially Rudy


Bookmarks

Posting Permissions

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