MySQL PHP - Search to see if column contains one of a list of variables

Question

Okay, so this is a little hard to explain but I have something like this:

$yourScripts = 777,1337, -- My variable

$yourData = mysqli_query($db,"SELECT * FROM scriptlog WHERE FIND_IN_SET('$yourScripts', scriptid)") -- My query

My scriptlog table looks like this:

scriptid | other stuff
-----------------------
1337     | xxxxxxx
456      | xxxxxxx
777      | xxxxxxx

How can I get my query to return the rows that have the correct scriptid? Currently it returns nothing.

I have also tried this but it also returns nothing:

$yourData = mysqli_query($db,"SELECT * FROM scriptlog WHERE scriptid LIKE '%{$yourScripts}%'")

Some reading for you https://stackoverflow.com/questions/4155873/find-in-set-vs-in — TimBrownlaw, Jul 14 '17 at 22:54
You have the arguments to `FIND_IN_SET` backwards. Otherwise it should work. — Barmar, Jul 14 '17 at 23:08

score 0 · Accepted Answer · answered Jul 14 '17 at 23:10

0

The comma-separated string is the second argument to FIND_IN_SET, not the first, so it should be:

$yourData = mysqli_query($db,"SELECT * FROM scriptlog WHERE FIND_IN_SET(scriptid, '$yourScripts')");

You could also use IN() which is better because it can use the index.

$yourData = mysqli_query($db,"SELECT * FROM scriptlog WHERE scriptid IN ($yourScripts)");

If $yourScripts is coming from the user, make sure you sanitize it first before substituting into the SQL -- it should only be integers.

answered Jul 14 '17 at 23:10

Barmar

741,623
53
500
612

Thank you for this. $yourScripts actually comes from another mySQL query, but I was sure to sanitize all user inputs just in case Thanks :) – questionMONSTER Jul 15 '17 at 01:09
If it comes from another query, why don't you just join the tables in a single query, instead of turning it into a string. – Barmar Jul 15 '17 at 04:37

MySQL PHP - Search to see if column contains one of a list of variables

1 Answers1