how to create a dynamic query using 'IN' in mysql

Question

I want to pass a string that contains many usernames, seperated by a comma,and want to pass it to query

   $name_list='kesong,nicholas,jane';   //this value would change
$select_occupy=mysql_query("SELECT * FROM user_data WHERE `username` IN('$name_list') ORDER BY `occupy_date` ");// i think this would just search for a username which is 'kesong,nicholas,jane', instead searching them seperately
    echo mysql_num_rows($select_occupy); //would echo 0

I know it works only when you specify like IN('$name1','$name2','$name3'), but I need a dynamic one

depends on the format and source of the original name list, array is easiest, you can just use implode — , Mar 04 '14 at 01:29
1) *do not* use `mysql_*` 2) build an SQL string *with placeholders* 3) bind the placeholders to values (use `mysqli_*` or PDO) - there are *examples for this task* in the documentation — user2864740, Mar 04 '14 at 01:37
yes, as a beginner, I code almost entire website using mysql_, including select delete couples months ago, that's frustrated to change it into mysqli_,may be it's time for modification, thanks for suggestion@user2864740 — kesong, Mar 04 '14 at 01:50

Ali MasudianPour · Answer 1 · 2014-03-04T02:08:36.787

As a suggestion, just hold your names into an array and do like below:

$names=array('name1','name2','name3');
$namesToCheck="";
foreach($names as $name){
    $namesToCheck.="'$name',";
}
//to remove the last ,
$namesToCheck=substr($namesToCheck,0,-1);

Now, you can put $namesToCheck into your IN query.

EDIT:

In this answer, this is assumed that you will prevent any possible SQL injections as current answer is just an idea about your question. The minimum suggestion to perform preventing SQL injections would be using mysql_real_escape_string function, which escapes special characters in a string for use in an SQL statement. For example:

$namesToCheck.="'".mysql_real_escape_string($name)."',";
//OR DO THIS ON FINAL STRING

NOTE THAT This extension is deprecated as of PHP 5.5.0. You can take a look at the PHP's official document in the following link:

mysql_real_escape_string

I think this would check the username=$name1+','+$name2+','+$name3 instead check them username=name1||username=name2||username=name3 — kesong, Mar 04 '14 at 01:35

Uours · Answer 2 · 2014-03-04T03:04:34.690

0

You can do it like this :

    $namesToCheck = "'" .implode( "','" ,explode( ',' ,$name_list ) ) ."'";

And then use the $namesToCheck in the IN clause of your query .

The above code would convert :

    kesong,nicholas,jane

to :

    'kesong','nicholas','jane'

edited Mar 04 '14 at 03:04

answered Mar 04 '14 at 02:53

Uours

2,517
1
16
21

how to create a dynamic query using 'IN' in mysql

2 Answers2