Use prepared statement in query with 'IN(.....)' clause?

Question

Is there a (tricky/non-standard) way to do this?

Would something like

$stmt = $db->prepare( 'SELECT title FROM episode WHERE id IN (?, ?, ?, ?);

work? So if I wanted to search for a variable amount of ids, could I do

$ids = array(1,2,3,4,5,6,7);
$idSection = implode(array_pad(array(), count($ids), '?')) //To give ?,?,?,?,?,?,?
$stmt = $db->prepare( 'SELECT title FROM episode WHERE id IN ($idSection);
$stmp->execute($ids);

Even if that would work, it still isn't really useful for running multiple sets of data with the same prepared statement unless the set of $ids being searched for is the same length every time and it doesn't work with name placeholders.

I'm assuming if you do

$stmt = $db->prepare( 'SELECT title FROM episode WHERE id IN (:ids);
$ids = implode('","' array(1,2,3,4,5,6,7));
$stmt->bindParam( ':ids', $ids);

It fails because the prepared statement was constructed so that it will search for a single id value and "1","2","3","4","5","6","7" wouldn't match?

I'm sure there's a better answer than just not using IN(...) clauses. Would I just have to sanitize the $ids term manually and include it in the query without placeholders?

$stmt = $db->prepare( "SELECT title FROM episode WHERE id IN $ids AND genre like :genre");
$stmt->bindParam( ':genre', '%$genre%);

Answer 1

MySql has a FIND_IN_SET function, which you can use to achieve the same result:

$ids = array(1,2,3,4,5,6,7);
$stmt = $db->prepare( 'SELECT title FROM episode WHERE FIND_IN_SET(id,?)' );
$param = implode(',',$ids);
$stmt->bind_param('s',$param); 
$stmt->execute();