CODEWITHSUNDEEP
mysql
coderking
coderking

Reputation: 39

Using a number with mySQL IN() function

I am having trouble using mySQL IN() when using it with a number, rather than a field name.

My query is: 

$query =    "select rental_id from {$this->table_name}
    where rental_id='$unit_id' AND '49' IN (specifications)";

The field specifications contains 20,10,49,22,18

An explanation states, 

Note: #1003 /* select#1 */ select '270' AS `rental_id` from `mydatabase`.`rental_buildings` where 0

and shows and Extra of Impossible WHERE noticed after reading const tables

If I remove the single quotes from the 49, I get 

Warning: #1292 Truncated incorrect DOUBLE value: '20,10,49,22,18'

Note: #1003 
    /* select#1 */ select '270' AS `rental_id`
    from `mydatabase`.`rental_buildings` 
    where (('270' = '270') and (49 = '20,10,49,22,18'))

This seems like it should be very straightforward, but I can't get it to work. I'd appreciate any guidance

Upvotes: 0

Views: 59

Answers (1)

tadman
tadman

Reputation: 211670

You can use FIND_IN_SET:

SELECT rental_id FROM ...
  WHERE rental_id=? AND FIND_IN_SET(?, specifications)

Keep in mind this will be exceptionally slow and extremely punishing on datasets of non-trivial size as this must search through each field individually for all entries for that particular rental_id.

To fix this you need a proper relational table where your query would look like:

SELECT DISTINCT rental_id FROM specifications WHERE id=?

For that particular specification_id. This can be indexed and can be really fast.

Upvotes: 1

Related Questions