Get keys from php array and use SQL WERE .. IN .. clause to filter results

Question

I have following dynamic $array1:

    array(6) { [1]=> string(5) "false" 
               [2]=> string(5) "true" 
               [3]=> string(5) "false" 
               [4]=> string(5) "false" 
               [5]=> string(5) "true" 
               [10]=> string(5) "false" 
             }

What I would like to do to use loop to create $array2 with $array1 keys where values = "true". So in the end I have:

$array2 = (2, 5);

And then use this array (with bind_param) to filter my results with "WHERE ... IN ...":

if ($result = $link->prepare("SELECT sapp.appointment_id, sapp.time_start
    FROM ss_appointments sapp 
    WHERE sapp.service_id IN ? AND sapp.time_start >= ?
    ")) { 
                            $result->bind_param('ss', $array2, $period_from);
                            $result->execute();
                            $result->bind_result($app_id, $time_start);
                            while($result->fetch()){ 

                                echo '..results here..';

        }

I'm struggling with first one, so could not even check second one - if I can bind at all $array2 to string.

Hope it's clear.

`$array2 = array_keys(array_filter($array));` – AbraCadaver Nov 19 '19 at 18:33 — AbraCadaver, Nov 19 '19 at 18:33

score 2 · Accepted Answer · answered Nov 19 '19 at 18:43

The two parts to your question

To filter the array you can use array_filter(), as you are using strings, you will need to check if the value is "true" to filter out the "false" values...

$array2 = array_keys(array_filter($array1, function($data) { return $data == "true"; }));

Use array_keys() to get the list of indices..

The next part is building the SQL and then binding the values. You should be building a query with the appropriate number of ? placeholders for each value

SELECT sapp.appointment_id, sapp.time_start
    FROM ss_appointments sapp 
    WHERE sapp.service_id IN (?,?) AND sapp.time_start >= ?

also bind would then be (for example)...

$result->bind_param('iis', 2, 5, $period_from);

So it builds the iis data types dynamically and the list of fields to bind. These are then put in using the argument unpacking (...) operator...

// Build comma separater list of placeholders
$in = trim(str_repeat("?,", count($array2)),",");
// Create appropriate bind data type string
$type= str_repeat("i", count($array2));
$sql = "SELECT sapp.appointment_id, sapp.time_start
    FROM ss_appointments sapp 
    WHERE sapp.service_id IN (".$in.") AND sapp.time_start >= ?";
if ($result = $link->prepare($sql)) {
    // Add the period to the bind data
    $array2[] = $period_from;
    $result->bind_param($type.'s', ...$array2);
    $result->execute();
    $result->bind_result($app_id, $time_start);
    while($result->fetch()){

        echo '..results here..';

    }
}

Thanks a lot, love your solution with dynamic list of field to bind! — Devy, Nov 19 '19 at 19:01

score 1 · Answer 2 · answered Nov 19 '19 at 18:47

It's been a while, not tested:

//filter out false, space and 0 and get keys
$params = array_keys(array_filter($array));

//add other variables in order of ? appearence
$params[] = $period_from;    

//create a comma separated list of ?
$list = implode(',', array_fill(0, count($params), '?'));

//use $list in the IN clause
$result = $link->prepare("SELECT sapp.appointment_id, sapp.time_start
    FROM ss_appointments sapp 
    WHERE sapp.service_id IN ($list) AND sapp.time_start >= ?";

//create types of string for each variable
$result->bind_param(str_repeat('s', count($params)), $params);

Get keys from php array and use SQL WERE .. IN .. clause to filter results

2 Answers2