sql IN clause only returning the current row, instead of selected ones

Question

I'm developing a site which is supposed to return similar movies by comparing the metadata from the movie that is selected, with the metadata from the ones in the database.

At the moment, I'm just comparing the classification rating. For example, if you've selected a movie that has a rating of R or MA, then the results returned should be something with a similar classification (ie: M, MA or R).

So far, this is what my SQL and PHP look like.

$rating = $data->rating;
if($rating === "MA" || $rating === "R"){
    $rating = "M, MA, R";
}

$rating = "rating IN (" . $rating . ")";
$sql = "SELECT * FROM movies WHERE unique_id <> ? AND " . $rating . " LIMIT 10";

If the SQL is output to the page, it should look like SELECT * ... AND rating IN (M, MA, R) ..., which it does. However, instead of the returned results being of all those movies that have a rating of M, MA or R (not including the selected movie), it will return only the selected movie.

Here's an example of my current database table:

+----+--------+--------+-----------+
| id | title  | rating | unique_id |
+----+--------+--------+-----------+
|  1 | movieA | R      | idA       |
|  2 | movieB | MA     | idB       |
|  3 | movieC | M      | idC       |
|  4 | movieD | MA     | idD       |
|  5 | movieE | PG     | idE       |
+----+--------+--------+-----------+

For movieA, it should be returning all movies apart from movieE. If it was movieC, it should be returning 2, 4 and 5.

I feel like I have used the IN clause correctly, so I'm confused as to why this is not working.

All help is appreciated, Cheers.

score 1 · Accepted Answer · answered Sep 22 '17 at 05:25

1

Since the ratings are strings, they need to be quoted.

if($rating === "MA" || $rating === "R"){
    $rating = "'M', 'MA', 'R'";
} else {
    $rating = "'$rating'";
}

See When to use single quotes, double quotes, and backticks in MySQL

answered Sep 22 '17 at 05:25

Barmar

741,623
53
500
612

I'll test now! Thanks :) – GROVER. Sep 22 '17 at 05:26
Will accept answer in 3 minutes :) thanks for the reference btw – GROVER. Sep 22 '17 at 05:27

score 1 · Answer 2 · answered Sep 22 '17 at 05:27

1

If the rating column is of type varchar, then you have to surround single quotes on each of the search strings.

Change:

$rating = "M, MA, R";

To:

$rating = "'M', 'MA', 'R'";

And the resulting query would look like the following:

SELECT * FROM movies 
 WHERE unique_id <> ? 
   AND rating IN ('M', 'MA', 'R')
 LIMIT 10

And applying it, you should be getting the expected result.

answered Sep 22 '17 at 05:27

Ravinder Reddy

23,692
6
52
82

He also needs to add the quotes when he's not assigning a list of ratings. – Barmar Sep 22 '17 at 05:29
That is the basic. So long as the column being searched is string, using single quotes is mandatory for correct results. – Ravinder Reddy Sep 22 '17 at 05:30

score 0 · Answer 3 · edited Sep 22 '17 at 05:36

0

If you're using strings instead of integers, add quotes (') in between each element. For example:

IN(M, MA, R)

Would then become, instead:

IN('M','MA','R')

edited Sep 22 '17 at 05:36

GROVER.

4,071
2
19
66

answered Sep 22 '17 at 05:27

Michael M.

189
1
7

sql IN clause only returning the current row, instead of selected ones

3 Answers3