Hi,
This is my table structure:
CREATE TABLE IF NOT EXISTS
commercial
(
id
int(6) NOT NULL auto_increment,
date
date NOT NULL default ‘0000-00-00’,
location
varchar(100) NOT NULL,
property_type
varchar(100) NOT NULL,
tenure
varchar(50) NOT NULL,
status
varchar(50) NOT NULL,
sale_rent
varchar(50) NOT NULL,
size
decimal(15,2) default ‘0.00’,
price
decimal(15,2) default ‘0.00’,
price_psf
decimal(15,2) default ‘0.00’,
rooms
varchar(5) NOT NULL,
baths
varchar(5) NOT NULL,
facilities
varchar(150) NOT NULL,
tenure_remaining
varchar(5) NOT NULL,
furnishings
varchar(150) NOT NULL,
phone_1
varchar(20) NOT NULL,
advertiser
varchar(50) NOT NULL,
e_num
varchar(20) NOT NULL,
unique
varchar(255) NOT NULL,
PRIMARY KEY (id
),
UNIQUE KEY unique
(unique
)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=210966 ;
This is my query which I’ve tried running in phpmyadmin. It does not work because mysql returns the entire 210966 records though there are only a few records which are newer than 2015-01-07
SELECT * FROM
commercial
WHERE date
> 2015-01-07
Why is this happening & how to get the query working properly?
“date” is a reserved word
1 Like
“unique” is also a reservered word
http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html
That lists the reserved words in version 5.5 of PHP, if you’re using a different version, check the page for that version
2 Likes
2015-01-07 is not a date but a numeric expression which is evaluated to 2007. Mysql will implicitly convert this to a date value, ‘0000-00-00’. All your date values are larger than this value, hence all rows are returned. Use a proper date literal in the comparison predicate.
SELECT * FROM commercial WHERE date > date '2015-01-07'
1 Like
Wonderful help & explanation swampBoogie. I appreciate it. Yes, the query works fine now. Have a good day
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.