Geek asks and answers

How to use prepared statements in queries with an IN clause in PHP

I need to make a simple request

$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 an array, what is the correct way to handle this? Do I need to manually create a query using 200 %d? I need this query because I have to “synchronize” my database with facebook data, and I need to check if I have a user in db, update those that are present, insert new users and delete those that are not my friends .

+5
source share
5 answers

If you know for sure that the elements of the array are numeric:

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

vsprintf prepare :

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

, , :

$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);

%d, call_user_func_array .

, , , , , .

+1

, sql . , .

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

, , . , sql.

0

, 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')).")";


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


$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')).")";


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

, is_int $_GET, is_numeric

0
source

You can do it:

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

More articles:


All Articles