Mysql query to fetch from integer range

i have a column in database that stores a range of user ages, like 5-10, 10-18, 18-25....

Now i want to write a query to fetch the columns with age matching this range.

For example, if i pass the age as 12, it should fetch the columns that fit in the range 10-18. If i pass the age as 10, it should fetch the columns that fit in the range 5-10.

开发者_Python百科

But the fact is that, i am storing the range as a varchar type (there is a hyphen in between).

So i am curious about how to achieve this, and how to construct a query.

---

You could split up the values using SUBSTRING_INDEX

SELECT * FROM `yourtable` WHERE 5 BETWEEN SUBSTRING_INDEX(`agespan`,'-', 1) AND SUBSTRING_INDEX(`agespan`,'-', -1) 

This query asumes agespan is the column containing 5-10, 12-18.

SUBSTRING_INDEX(agespan,'-', 1) matches everything in the string before the first occurence of -

SUBSTRING_INDEX(agespan,'-', -1) matches everything in the string after the last occurence of -

---

I think that is going to be asy (if you can) to change a bit the data model and add to numeric fields for the range for example:

| MIN_AGE | MAX_AGE |
    5           10
    11          18

so you can do a select between the two values.

Aswell i recomend you to not use the same value for two blocks, for example, if you have 5-10 the next should be 11-18.

If you cant change the data model i will probably get the data and process it in the PHP or make some king of stored procedure in the database to process de thing, but this is a non trivial task.

EDIT 1:

If you have not too many ranges you can get all of them to your PHP code and afer convert the "5-10" string in two integers using this expresion:

"""
^           # Assert position at the beginning of a line (at beginning of the string or after a line break character)
(           # Match the regular expression below and capture its match into backreference number 1
   [0-9]       # Match a single character in the range between “0” and “9”
  +           # Between one and unlimited times, as many times as possible, giving back as needed (greedy)
)
-           # Match the character “-” literally
(           # Match the regular expression below and capture its match into     backreference number 2
   [0-9]       # Match a single character in the range between “0” and “9”
   +           # Between one and unlimited times, as many times as possible, giving    back as needed (greedy)
)
$           # Assert position at the end of a line (at the end of the string or before a line break character)
"""
^([0-9]+)-([0-9]+)$

This way you ar goint to get two capture groups, group 1 with the min and group 2 with the max value. At this point is easy to check if the value you have is in the range.

I hope this make sense for you, please let me know if not :)

---

You'll need to split the string in two parts, the part before the "-" and the part after the "-".

SELECT range INTO @Range FROM ATable WHERE ...
/* or for a quick test: SET @range = "5-10"; */
SET @dash_pos = LOCATE("-",@Range);
SET @from = LEFT(@Range,@dash_pos-1);
SET @to = RIGHT(@Range, LENGTH(@Range) - (@dash_pos));

/*debug code to test if @from and @to are filled correctly*/
SELECT @Range, @dash_pos, @from, @to;
/*remove in production*/    

SELECT * FROM tableName 
  WHERE tablename.age BETWEEN @from AND @to;

---

I don't get your question correctly but you should try this code out...

$query = "SELECT * FROM tablename BETWEEN $int_1 AND $int_2"

But for that varchar thingy I don't know...

Sorry..

But I hope this would work!