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