How to put multiple values in WHERE row = (?) SQL php statement

Question

So my query works on the actual phpmysql server when I manually type in some values but in php I am having some difficulty.

This is my SQL table:

userID | forename | surname  |      email         | age    | 
------------------------------------------------------------
    1  |  Jack    |  Wolf    |   dj@rave.com      |  19    | 
    2  |  Mark    |  Smith   |   mark@rave.com    |  18    | 
    3  |  Ben     |  Cas     |   sex@club.com     |  21    | 
    4  |  Jos     |  Jis     |   jis@jos.com      |  19    | 
    5  |  Luke    |  Kils    |  kils@kiss.com     |  23    | 
------------------------------------------------------------

Basically, I want to pass in some UserID values like this 1,3,5 and it should display:

userID | forename | surname  |      email         | age    | 
------------------------------------------------------------
    1  |  Jack    |  Wolf    |   dj@rave.com      |  19    | 
    3  |  Ben     |  Cas     |   sex@club.com     |  21    | 
    5  |  Luke    |  Kils    |  kils@kiss.com     |  23    | 
------------------------------------------------------------

The userID values can vary depending on what the user selects so it can be 2 or even 1,2,3,4 or even 1,2,3,4,5

This is my php code:

<?php
require "init.php";
if(!empty($_POST['userID'])){
    $userID = $_POST['userID']; 
    echo $_POST['userID'];  
    $stmt = "SELECT userID, forename, surname, email, age
            FROM users
            WHERE userID IN (?)";   
    $result = $conn-> prepare($stmt);
    $result->bind_param('i', $userID);
    $result->execute(); 
    $outcome=$result->get_result();
    $response = array();
    if(($outcome->num_rows)>0){
        while($row = $outcome->fetch_assoc()){
            $response[] = array
            (
                "userID" => $row["userID"],
                "forename" => $row["forename"],
                "surname" => $row["surname"],
                "email" => $row["email"],
                "age" => $row["age"]
            );
        }
    echo json_encode($response); 
    }
    else{
        echo json_encode("None found");
    }
}

?>

When I echo $userID = $_POST['userID']; I get 1,2,3 or 1, but then these are not being passed properly to the SELECT STATEMENT. How do I fix it?

Thanks

In short, you can't use a single prepared parameter with `in` and a variable amount of array members. — apokryfos, Mar 07 '16 at 10:54

score 1 · Answer 1 · answered Mar 07 '16 at 12:03

Something along these lines

...
 $userIDs = implode(",",$_POST['userID']); //Array of user ids
 $qs = array_fill(0,count($userIds),"?");
 $stmt = "SELECT userID, forename, surname, email, age
        FROM users
        WHERE userID IN (".implode(",",$qs).")";   
 $bindParams = $userIDs; //Array for the bind parameters
 $bindParams = array_unshift($bindParams, "i"); //Prefix with "i" 
 call_user_func_array([$result, 'bind_param'],$bindParams);
 ...

The idea is that your statement will need as many "?" as there are user ids since you're binding that many integers.

You'll use call_user_func_array to call the bind_param function with a variable amount of arguments. Note that if you're using PHP 5.6+ you could just do something like: $result->bind_param("i",...$userIDs)

score -1 · Answer 2 · answered Mar 07 '16 at 10:52

-1

select userName from Table where user_id in (1, 2, 3);.

you can use $_POST['userID'] in this format or convert this format and use it.

answered Mar 07 '16 at 10:52

Domain

11,562
3
23
44

How do I convert`$_POST['userID']` to do precisely that? Like when I run your `sql` statement on my server it works because I manually typed in `1,2,3`. It is just how do I do the same with `$_POST['userID'] ` – Lukazs Pioetrszci Mar 07 '16 at 11:00

How to put multiple values in WHERE row = (?) SQL php statement

2 Answers2