Comma-separated values doesn't work in PHP MYSQL [duplicate]

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.

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
– Barclick Flores Velasquez, Commented Aug 26, 2017 at 5:59

cwallenpoole · Accepted Answer · 2017-08-26 06:06:03Z

1

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.

answered Aug 26, 2017 at 6:06

cwallenpoole

82.4k26 gold badges132 silver badges174 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Strawberry Over a year ago

It kind of kills the point of prepared and bound statements though, right?

cwallenpoole Over a year ago

@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.

vladatr · Accepted Answer · 2017-08-26 07:22:46Z

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, 2017 at 7:22

answered Aug 26, 2017 at 6:58

vladatr

6266 silver badges15 bronze badges

Collectives™ on Stack Overflow

Comma-separated values doesn't work in PHP MYSQL [duplicate]

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related