Comma-separated values doesn't work in PHP MYSQL

Question

I'm currently facing a difficulty where putting a comma-separated values to a MySQL NOT IN doesn't give me the result I was hoping for. There must be something I'm missing as I'm unsure what to search for this particular problem. Running only the MySQL code works, but passing the parameter from another PHP function didn't.

Here's the code that's giving me a problem:

$uid = 1;      
$selected_uids = '1,2';       
$result = $db->retrieveFollowingWithCondition($uid, $selected_uids);

...then somewhere along the code...

public function retrieveFollowingWithCondition($uid, $selected_uids) {
$stmt = $this->conn->prepare("SELECT *
        FROM `friendlist`
        WHERE `uid` = ? AND `buddy_uid` NOT IN (?)
        GROUP BY `buddy_uid`;");
        $stmt->bind_param("is", $uid, $selected_uids);
...}

I've tested just putting '2' in $selected_uids and it actually works. But once there's comma involved, the code runs but the $selected_uids are still in the result. Not sure this is a bad practice or just needing a minor adjustment to the code. Anyway, I'm really looking forward to understand why it's not working for me.

See this post if it helps you - [bind param with array](https://stackoverflow.com/questions/16236395/bind-param-with-array-of-parameters) — rikardo_paiva, Aug 26 '17 at 05:56
if(in_array($uid, $selected_uids )){ $stmt = $this->conn->prepare("SELECT * FROM `friendlist` WHERE `uid` = ? AND `buddy_uid` NOT IN (?) GROUP BY `buddy_uid`;"); $stmt->bind_param("is", $uid, $selected_uids); ...} } — Barclick Flores Velasquez, Aug 26 '17 at 05:59

score 1 · Accepted Answer · answered Aug 26 '17 at 06:06

By using s in bind_param you are telling PHP to treat the entire contents of $selected_uids as a string. Therefore, "1,2" is treated as ('1,2') instead of (1,2). Your problem is that bind_param doesn't support arrays, so support of IN queries is limited. There are a number of alternatives to get around this limitation, but since you are dealing with a list of ints, I would probably do a raw string concat.

// using is_numeric because is_int("1") === false
$filtered = array_filter('is_numeric', $selected_uids);

// You could also just call array_map('intval', $selected_uids);
// Depending on your needs.
if(!$filtered) {
    return; // No valid values
}
$filteredStr = implode(',', $filtered);

$stmt = $this->conn->prepare("SELECT *
    FROM `friendlist`
    WHERE `uid` = ? AND `buddy_uid` NOT IN ($filteredStr)
    GROUP BY `buddy_uid`;");

Should also be noted: if I were trying to use strings for an IN query, I would likely do the following:

 $filtered = array_map([$this->conn, 'escape_string'], $queried);
 $inQuery = '\'' . implode('\',\'', $filtered) . '\'';

I find that notation cleaner and easier than a dynamically generated bind_param format string.

It kind of kills the point of prepared and bound statements though, right? — Strawberry, Aug 26 '17 at 06:53
@Strawberry … yes, and no. Unfortunately, there are few alternatives to create `IN` queries otherwise. I'd argue that dynamically creating the format parameter to `bind_param` is just as ugly, and harder to read/debug. This results in a much more readable block of code around a single concatenation. To me, readability paired with good, secure practice is better than unreadable but slightly better practice. — cwallenpoole, Aug 26 '17 at 15:47

vladatr · Answer 2 · 2017-08-26T07:22:46.780

1

You should bind every parameter in IN(...) separately, but method bind_param doesn't support multiple calls. There is a nice class that can do this and you can find it on PHP documentation pages: Custom class for multiple bind_param

edited Aug 26 '17 at 07:22

answered Aug 26 '17 at 06:58

vladatr

616
6
15

Comma-separated values doesn't work in PHP MYSQL

2 Answers2