Query to find if value is between "1|3"

Asked

Viewed 77 times

5

I have a table units that represents the number of dormitories of the buildings:

id|  dorm
1 |   1|3

In this case above means that id 1 has units with 1 or 3 dormitories.

Then I have a search using $_GET where the dormitory field is Dorm=2, for example

In this example I need the query to bring this id 1, because the person selected minimum 2 dormitories, and as in the Dorm column has 1|3 (which means that it has units of 1 or 3 dormitories) it must bring the id 1.

To make the query I would have to "break" the Dorm column, right? I don’t know how

mysql select

1 answer

4


Use the function SUBSTRING_INDEX separating the values and comparing with the value received in GET.

The third parameter positive will return the value to left of the delimiter |, and negative will return the value to right of the delimiter:

"SELECT id FROM tabela
WHERE SUBSTRING_INDEX(dorm,'|',1) >= '$dorm'
OR
SUBSTRING_INDEX(dorm,'|',-1) >= '$dorm'"

If a or another value is equal to or greater than the GET value, will return the record.

  • just put () in Where, because I have other Ands in the query

  • has a problem in this, if the dormitory has more than 1 character, it gives error, because it cuts in half

  • Show me an example to see

  • ex, id1 = Dorm 1|3. id2 = 10|8, id3 = 1|10.

  • Leandro, I updated the answer... now do a test to see why maybe the value $dorm must be out of quotation marks....

  • it worked yes, then I tried to apply this same one to another, but it did not, this other one has 4 values: 20|35|115|10. you know how I can do for this case?

  • I managed, I played one inside the other SUBSTRING_INDEX(SUBSTRING_INDEX(footage,'|',2),'|', -1)

Show 2 more comments

Browser other questions tagged mysql select

You are not signed in. Login or sign up in order to post.