PHP array for an SQL “IN” clause?

Question

i am trying to search multiple roll numbers from database with html form e.g 2345,7654,8976 in a single field now for php i am trying

$query = "SELECT `firstname`, `lastname`, `roll`, `board`, `city` FROM `result` WHERE 1=1";

if(isset($_POST['mroll']))
{
$mroll=$_POST['mroll'];
$query.="and roll IN ('$mroll')";
}

but $_POST['mroll'] will be like this => 2345,7654,8976 for sql i have to quote them like this => '2345','7654','8976' before using it in query please help.

Integers don't need to be quoted. You are open to SQL injections though. I'm not recommending this but `and roll IN ($mroll)` would work. — chris85, Oct 07 '16 at 19:57
Doesn't even need to implode/explode/any PHP function, if it really is `2345,7654,8976`. Just get rid of quotes and it will work (but open the DB up to injections). — chris85, Oct 07 '16 at 20:00
Great to see this suppose if value already available in comma separated than y u suggest explode() or if its an array as mentioned in title than just implode it. Correct me if I m wrong — devpro, Oct 07 '16 at 20:02
@chris85 thanks i was using quotes for $mroll now removed them and its working.. — Malik Ali Jamil, Oct 08 '16 at 06:42
@RajdeepPaul That is incorrect, http://sqlfiddle.com/#!9/af44d/1. — chris85, Oct 08 '16 at 14:44

score 2 · Accepted Answer · answered Oct 08 '16 at 14:51

Since the values are integers if you unquote the variable the query will be valid. This will open you to SQL injections though. You should use a parameterized query and pass each value in as a placeholder. Try something like:

$query = "SELECT `firstname`, `lastname`, `roll`, `board`, `city` FROM `result` WHERE 1=1";
if(isset($_POST['mroll'])) {
     $mroll=$_POST['mroll'];
     foreach(explode(',', $mroll) as $int) {
         $placeholders .= '?, ';
         $params[] = $int; 
     }
     $placeholders = rtrim ($placeholders, ', ');
     $query .= " and roll IN ($placeholders)";
}

Demo: https://eval.in/657610

Technically you could just change:

$query.="and roll IN ('$mroll')";

to

$query.="and roll IN ($mroll)";

highly discourage that approach though.

Your query when quoted becomes:

SELECT `firstname`, `lastname`, `roll`, `board`, `city` FROM `result` WHERE 1=1and IN ('2345,7654,8976')

which makes 2345,7654,8976 one value, not three values.

PHP array for an SQL “IN” clause?

1 Answers1