Geek Answers Handbook

Use prepared statement in query with sentence "IN (.....)"?

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

There will be something like

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

work? Therefore, if I wanted to find a variable number of identifiers, can 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 this works, it is still not very useful to use multiple datasets with the same prepared statement, unless the search in $ids is repeated every time and it does not work with name placeholders.

I guess what 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 designed so that it looked for the same id value and "1", "2", "3", "4", "5", "6", "7" 't match ?

I am sure there is a better answer than just not using IN(...) clauses. Should I just clear the term $ids manually and include it in the request without placeholders?

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

A way to avoid using IN-clauses is to populate the temporary table with values ​​that go into IN (...) as a comma-separated list; then either INNER-JOIN in the temp-table column, or type nested-select:

  inner join temptable T2 on T1.mycol = T2.col1

OR

  ... where mycol in ( Select col1 from temptable)
-2
source

MySql has a FIND_IN_SET function that 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();
+10
source

More articles:


All Articles