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')";
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.
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.
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!!!
===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&#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's been there before preferably. |
+----+---------+------------+------------+---------------+-------------+-----------------------------------------------------------------------------+
1 row in set, 2 warnings (0.00 sec)
mysql>
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
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
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”…
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'";