Date range problems...Please have a look

Databases
1

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.

$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

are you sure these two different date values are set correctly?

3

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

no, i was asking if the $date1 and $date2 values are set correctly

but having the date columns as strings is probably the problem

5

This was not the problem as I knew it would not be, and yes the values are set properly.

6

oh, well, then it must be something else

perhaps you could set up a test case for us to work with?

7

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

I got this lil warning when i searched the db within an SQL Terminal.

“Truncated incorrect DOUBLE value: ‘2012-02-09’”

9

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

10

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 
===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

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> 


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

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

13

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

-- 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 
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

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

this should help you –> http://www.sitepoint.com/forums/showthread.php?664040-Checking-a-number-from-a-range-contained-within-a-range-under-SQL

17

Which in case would equal this query here:

WHERE date_to >= $date1 AND date_from <= $date2
or

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

Think i might have it sorted, will reply shortly!

19

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.

    $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

Do an echo of $sql and post the result.