How to query a range of data from MySQL?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ljCharlie
Forum Contributor
Posts: 289
Joined: Wed May 19, 2004 8:23 am

How to query a range of data from MySQL?

Post 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
jason
Site Admin
Posts: 1767
Joined: Thu Apr 18, 2002 3:14 pm
Location: Montreal, CA
Contact:

Post 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]'
jason
Site Admin
Posts: 1767
Joined: Thu Apr 18, 2002 3:14 pm
Location: Montreal, CA
Contact:

Post by jason »

ljCharlie
Forum Contributor
Posts: 289
Joined: Wed May 19, 2004 8:23 am

Post 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
jason
Site Admin
Posts: 1767
Joined: Thu Apr 18, 2002 3:14 pm
Location: Montreal, CA
Contact:

Post by jason »

Try it and find out. =)
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
ljCharlie
Forum Contributor
Posts: 289
Joined: Wed May 19, 2004 8:23 am

Post by ljCharlie »

Thank you very much. I'll keep that in mind when I emplement that into my code.

ljCharlie
Post Reply