My sql Query is not working. I need to filter a data with Location but it not works. Why this is not working
$sql = "SELECT count(*) FROM entries WHERE location
IN ('chennai,Bangalore') AND en_id = 'test' AND date(datetime)
BETWEEN '2015-10-02' AND '2015-10-31'";
You need to update your query
IN ('chennai,Bangalore')
into
IN ('chennai','Bangalore')
try this table and query and analyst it hope will help you table as follows
CREATE TABLE IF NOT EXISTS `entries` (
`id` int(15) NOT NULL,
`location` varchar(255) NOT NULL,
`en_id` varchar(25) NOT NULL ,
`date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;
--
-- Dumping data for table `entries`
--
INSERT INTO `entries` (`id`, `location`, `en_id`, `date`) VALUES
(1, 'chennai', 'test', '2015-10-31 12:53:38'),
(2, 'Bangalore', 'test', '2015-10-31 12:53:38'),
(3, 'chennai', 'test', '2015-10-13 12:53:38'),
(4, 'chennai', 'test', '2015-10-05 12:53:38'),
(5, 'chennai', 'test1', '2015-10-03 00:00:00'),
(6, 'Bangalore', 'test', '2015-10-04 12:53:38'),
(7, 'Bangalore', 'test1', '2015-10-03 00:00:00'),
(8, 'chennai', 'test1', '2015-10-30 00:00:00'),
(9, 'Bangalore', 'test1', '2015-10-30 00:00:00');
This is the query to get your desired result
SELECT count(*) FROM entries WHERE location
IN ('chennai','Bangalore') AND en_id = 'test1' AND date(NOW())
BETWEEN '2015-10-02' AND '2015-10-31'