Match a Number to a Range of Numbers in MySQL

PHP
1

I’m working on a PHP script and I want people to enter a number which will be matched to a range of numbers in MySQL. It’s difficult to explain, so I’ll simplify the idea:

Suppose I ask people to answer one question on a form: What year were you born?

Based on what the user enters, PHP will connect to MySQL and pull up the generation for which the user belongs (we can easily accomplish this task in PHP, but I want to use MySQL).

The MySQL table would look something like this:


| ID  |  YEARS    |  GENERATION  |
-------------------------------------
|  1  | 1945-1960 | Baby Boomers |
|  2  | 1961-1970 | Generation Jones |
|  3  | 1971-1981 | Generation X |
|  4  | 1982-1995 | Generation Y |

How would I set up my MySQL query?

Something like?:
(Select * from Generations where yearborn = 1983)

Do I have to break the year limits of each generation into separate columns? Is there a way to find out which range the year would fall into?

Thanks, and I really appreciate any help anyone is willing to provide.

2

If you seperated years into a year_min and year_max columns, and used either a date or integer type, you could just


WHERE yearborn BETWEEN year_min AND year_max

Otherwise, you have to do ugly string parsing. Try not to store multiple values in a single column of a row, when designing the database.

3

I had my doubts about that query, but it worked PERFECTLY!

Thanks so much!