准备好具有动态数量标准的陈述

Question

I have a query which looks something like this:

$query = "SELECT icon.web_id, 
image.web_id, image.base64_data, 
theme.title, theme.themepath, 
theme.description, filepath.filepath, 
filepath.filename
FROM theme

INNER JOIN icon ON icon.fk_theme_id = theme.id
INNER JOIN filepath ON icon.fk_filepath_id = filepath.id
INNER JOIN image ON icon.fk_image_id = image.id
LEFT JOIN junction_icon_icontag ON junction_icon_icontag.fk_icon_id = icon.id

WHERE 1
" . $filepaths . " 
" . $themes . "
" . $icontags . "

GROUP BY icon.id";

The variables cointains matching criterions like "AND (filepath = 'foo' OR filepath = 'foo2')".

I now want to convert this into a prepared statement. How do I work with the variable number of criterions? From what I have seen in examples I should replace them with ? but since I don't know how many there are before the query is built, I don't know how many questionmarks I should add. Currently I'm checking all parameters in a huge switch statement to validate them. I was hoping that PDO's would simplify this.

Answer 1

create your where part like this:

where (filepath = :filepath0 or filepath = :filepath1 ....)

your $filepaths will be an array:

$filepaths = array('foo', 'foo2' ....);

Then loop over $filepaths binding using named param:

foreach($filepaths as $i => $value)
    $stmt->bindValue(':filepath' . $i, $value);