Page 1 of 1
How to query a range of data from MySQL?
Posted: Fri May 21, 2004 1:57 pm
by ljCharlie
Does anyone know a quick way to query a range of data from MySQL database? For example, I want to query any person who have a last name starting with M to P or to query using a range of years such as from 1980 to 2000 . Is there a quick and easy SQL statement to do this?
Your suggestion is much appreciated.
ljCharlie
Posted: Fri May 21, 2004 2:13 pm
by jason
BETWEEN might help:
http://dev.mysql.com/doc/mysql/en/Compa ... ators.html
As far as a person with a last name, look into using REGEX, such as:
Code: Select all
SELECT first_name, last_name FROM people WHERE last_name REGEXP '^їm-p]'
Posted: Fri May 21, 2004 2:14 pm
by jason
Posted: Fri May 21, 2004 2:20 pm
by ljCharlie
Thanks! I'll give that try.
By the way, if I'm querying from 2004-04-01 to 2004-04-30 and the field I have in the database is declared as DATE which has this format yyyy-mm-dd, do you think the BETWEEN will work if I do this:
їcode]$query = 'SELECT * FROM regUsers WHERE myDate BETWEEN "'.$dateFrom.'" AND "'.$dateTo.'"';ї/code]
ljCharlie
Posted: Fri May 21, 2004 2:51 pm
by jason
Try it and find out. =)
Posted: Fri May 21, 2004 3:09 pm
by Weirdan
There is a lot of string functions in php, regexp is perhaps slowest among them. for you particular case (checking prefix of the name):
Code: Select all
select * from sometable where substring(fname,0,1) in ('m','n','o','p');
is as much as two times faster then regexp.
Posted: Fri May 21, 2004 3:14 pm
by ljCharlie
Thank you very much. I'll keep that in mind when I emplement that into my code.
ljCharlie