Using variables in multiple "NOT IN" clauses

Question

I just can't get this right. Doing a hobby project as a hobby programmer. Here is a MySQL statement that I'm using on a php web page:

$stmt = $mysqli->prepare("SELECT t1.* FROM laptimes t1
JOIN (
SELECT gamertag, track, vehicle, vehicleclass, platform, controller, setup, 
MIN(laptime) AS min_laptime
FROM laptimes 
WHERE track LIKE ? AND vehicle LIKE ? AND vehicleclass LIKE ? AND platform 
NOT IN ('PS4','XB1') AND controller NOT IN ('Gamepad','Keyboard') AND setup 
NOT IN ('Custom') 
GROUP BY gamertag, vehicle, vehicleclass
) AS t2 ON t1.gamertag = t2.gamertag AND t1.laptime = t2.min_laptime AND 
t1.track = t2.track AND t1.vehicle = t2.vehicle AND t1.vehicleclass = 
t2.vehicleclass AND t1.platform = t2.platform AND t1.controller = 
t2.controller AND t1.setup = t2.setup
ORDER BY laptime ASC LIMIT ? OFFSET ?");
$stmt->bind_param("sssii", $trackselect, $carselect ,$classelect, $limit, $offset); 
$stmt->execute();

Above code, with some hardcoded values, works fine. It gets best laptimes for each player, for a leaderboard. And it excludes results with platform value 'PS4' or 'XB1', controller values 'Gamepad' or 'Keyboard', and setup values 'Custom'. But what I want to do, is replace the hardcoded values in the WHERE clause with variables. I can use ? for track, vehicle, vehicleclass and limit and offset, using bind parameters, no problem. But for platform, controller and setup, I can't use variables, although the code works fine with hardcoded values.

The three NOT IN clauses are excluding records with theses values. In the example, the platform NOT IN ('PS4',XB1') excludes results with PC or PS4 values in the column 'platform'. Instead of hardcoded values, I want to use $platform, $controller, $setup. I've tried all combinations of curly brackets, parenthesis, back tick, apostrophes, double quotes; nothing seems to work.

If I print the values on for example $platform, it shows: 'PS4','XB1' (just an example, value is set by checkboxes. So it looks equal to my hardcoded values.

If I replace the hardcoded values with ? (adjusting the bind_param accordingly), nothing gets excluded from the result, even if I tick checkboxes (I print the value of $platform, $controller, $setup and it looks correct).

If I instead do it like this: platform NOT IN ($platform) then it works. But if I do the same to controller and setup, controller NOT IN ($controller) AND setup NOT IN ($setup) I get this error message: Fatal error: Call to a member function bind_param() on a non-object pointing to the line with bind_param. I have adjusted the bind_param line to correct number of "sss" and $variables.

So how can I use variables instead of hardcoded values?

I'm way out of my league here, I can't understand it. Printed variable values are exactly like the hardcoded examples. Do I need a special kind of parenthesis, or some kind of magic sauce? I don't know. Hopefully I've explained the issue well enough. If not, let me know and I'll try again.

*"But for platform, controller and setup, I can't use variables, although the code works fine with hardcoded values."* i very much doubt that, because the inner query is not correct MySQL's [Handling of GROUP BY](https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html), you can't trust platform, controller and setup columns to have the correct value in this query if MySQL executes it without a error. — Raymond Nijland, Mar 19 '19 at 21:27
Thank you for answering. Do you think that the inner query issues is the reason for my problems? Or is that a different issue? It does work just as expected with hardcoded values. Not sure how I can demonstrate that, if it's important. — Martin, Mar 19 '19 at 21:35
`NOT IN (?, ?, ?)` works just fine. You likely have a typo somewhere else in your code. I'd strongly recommend using PDO or a full database abstraction layer rather than mysqli. That API is a low-level mapping of the MySQL C API and is not great for direct use. — miken32, Mar 19 '19 at 21:36
*"That API is a low-level mapping of the MySQL C API and is not great for direct use"* mysqli is fine @miken32 by the way is also supports OOP interface.. — Raymond Nijland, Mar 19 '19 at 21:38
You can use `NOT FIND_IN_SET(controller, ?)` and then provide a string like `'Gamepad,Keyboard'` for the parameter. — Barmar, Mar 19 '19 at 21:38
@RaymondNijland I'm not saying it won't work, but it's very verbose and so is more prone to coding errors. Having to count your parameters and put in siisssii is nonsense, and don't get me started on fetching results! PDO can do the same stuff with half as many lines of code. — miken32, Mar 19 '19 at 21:41
i agree with the "siisssii" in bind_param() @miken32 but also with PDO you have to count if your parameters match *"PDO can do the same stuff with half as many lines of code."* if you have alot of params $stmt->bind_param("sssii", ..) is less code then repeating PDO's `bindParam()` all the time *"and don't get me started on fetching results"* Pretty sure the fetching is more or less the same in most cases, PDO has extra options. Don't start about PDO's execute() function that one can only handling string types it's not all types. But don't get me wrong i would also choose PDO over MySQLi — Raymond Nijland, Mar 19 '19 at 22:00

score 2 · Accepted Answer · answered Mar 19 '19 at 21:42

2

If you have a comma-separated list of values in a string, you can use FIND_IN_SET() to match one of them. You use a bound parameter as the string with the list.

So use:

$controller = 'Gamepad,Keyboard';
$platform = 'PS4,XB1';
$setup = 'Custom';

and then make your query use:

AND NOT FIND_IN_SET(platform, ?) 
AND NOT FIND_IN_SET(controller, ?) 
AND NOT FIND_IN_SET(setup, ?)

and bind those parameters to the above variables.

answered Mar 19 '19 at 21:42

Barmar

741,623
53
500
612

Looks like my comment from yesterday disappeared? Anyway, this works great, fantastic! Thank you! – Martin Mar 20 '19 at 08:15

score 0 · Answer 2 · answered Mar 19 '19 at 21:47

You are attempting to bind multiple values inside an IN() with one variable. Parameterization requires a one-to-one match of literal value to binded value. Therefore, your below attempt:

$platform = "'PS4','XB1'"
...

WHERE ... platform NOT IN (?)

Should be replaced as:

$platform1 = 'PS4'
$platform2 = 'XB1'
...

WHERE ... platform NOT IN (?, ?)

Likely, you want to dynamically place qmarks inside IN() and dynamically bind multiple variables which requires use of manipulating an array. This type of question has been asked and answered multiple times in PHP on SO:

Using variables in multiple "NOT IN" clauses

2 Answers2