如何在PHP中使用IN子句的查询中使用预准备语句[重复]

I need to make a simple query

$array_of_ids = array();
//poulate $array_of_ids, they don't come from another db but from Facebook
//so i can't use a subquery for the IN clause
$wpdb->prepare("SELECT id from table where id IN (%d, %d)", $array_of_ids [0], $array_of_ids [1]);

The question is, if i have 200 elements in the array, what is the correct way to handle this?Do i have to manually build the query with 200 %d? I need this query because i must "sync" my database with facebook data and i have to check if the user i have in the db are present, update those that are present, insert new users and delete those that are not my friend.

</div>

If you know for certain that the array elements are numeric:

$wpdb->prepare("SELECT id FROM table WHERE id IN ("
  . implode(',',$array_of_ids) . ")");

Otherwise, you can use the vsprintf form of prepare to pass in the array of parameters:

$wpdb->prepare("SELECT id FROM table WHERE id IN ("
  . str_repeat("%d,", count($array_of_ids)-1) . "%d)" , $array_of_ids);

Yes, dynamic sql is the way here. Fortunately, integers are easy to not screw up with.

$vals = array_filter(array_map('intval', $vals));

make sure you have at least one value and then implode it. Not need for a prepared statement here, just execute the sql.

I'm not sure that this is a good approach, but you could do it in this fashion:

$sql = "SELECT id from table where id IN (" 
     . implode(',', array_fill(0, count($array_of_ids), "%d"))
     . ")";

call_user_func_array(array($wpdb, 'prepare'), $array_of_ids);

This builds a string with the appropriate number of %d, then uses call_user_func_array to do it dynamically.

That said, I'm not sure this is really a case where prepared statements are worth the hassle, given how easy it is to sanitise integers.

You can do this :

$query = $wpdb->prepare("SELECT id from table where id IN :param");
$query->bindParam("param", "(".implode(',', array_map('intval', $array_of_ids)).")");

Since this has no accepted answer yet I'll go with my approach with array_filter

$array_of_ids = array(0,1,1,2,3,5,8,13);

echo "SELECT id from table where id IN (".implode(',', array_filter($array_of_ids,'is_int')).")";

will output

SELECT id from table where id IN (0,1,1,2,3,5,8,13)

while

$array_of_ids = array('zero',1,true,2,3,5,8,'thirteen');

echo "SELECT id from table where id IN (".implode(',', array_filter($array_of_ids,'is_int')).")";

will output

SELECT id from table where id IN (1,2,3,5,8)

Please note that is_int doesn't work with $_GET variables so use is_numeric instead