PDO with an "in" clause

PHP
1

Found a couple of posts on the site regarding this, all of which were several years old so wanted to verify how to do this currently. I have an update query such as

update table1 set price = price*10 where xmasterid in :valuearray

If I want to prepare the statements, does pdo now handle arrays? Or do I need to insert (?,?,?) as placeholders instead of the :arrayname?

And if I insert the ? as placeholders, can I then use a numeric array with that many values in the prepare statement?

Thanks

2

In this case it is also pretty easy and relatively safe for me just to modify the sql statement and insert the (1,2,3) clause directly in there. But would rather stay with prepared statements whenever possible.

3

Reading through this http://stackoverflow.com/questions/1586587/pdo-binding-values-for-mysql-in-statement

I put this together, using the users table for my site, this gets the details of the users listed in the $users array

 $users = array(1,2,3,4,5,6,7,8);
            
            $sql="
                SELECT
                    *
                FROM
                    ue_user
                WHERE
                    id
                        IN (".rtrim( str_repeat('?,', count($users)), ',')
                .")";
            
            $stmt = $this->db->prepare($sql);

            $stmt->execute($users);
            $the_users= $stmt->fetchAll(PDO::FETCH_ASSOC);
1 Like
4

Thanks for the input. That works great. I had gotten used to using named parameters which I really like but in this case I think this appears to be the way to go.

5

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.