mysql查询where条件可以是数组,如何在MySQL查询的where子句中传递数组值？

I have a variable $element whose value is:

Array ( [4] => easy [5] => easy [7] => easy [8] => will [9] => easy [10]

=> will )

I want to use this variable in my query :

$sql = "SELECT * FROM questions where type='$element'";

$result = $conn->query($sql);

if ($result->num_rows > 0) {

// output data of each row

while($row = $result->fetch_assoc()) {

echo "id: " . $row["question_name"]. "
";

}

}

Actually I want to parse from each element of $element variable and present different output respectively.

解决方案

First, you should call array_unique() on $element.

If this is trusted data that you are using and the values never contain single quotes, then you can just insert it into an IN clause:

$sql = "SELECT question_name FROM questions WHERE `type` IN ('" . implode("','", $element) . "')";

If this is not trusted data then a prepared statement with placeholders is advisable. This is slightly convoluted for an IN clause.

$params = $element;

$count = count($params);

$csph = implode(',', array_fill(0, $count, '?')); // comma-separated placeholders

if(!$stmt = $conn->prepare("SELECT question_name FROM questions WHERE `type` IN ($csph);")){

echo "Syntax Error @ prepare: " , $conn->error; // don't show to public

}else{

array_unshift($params, str_repeat('s', $count)); // prepend the type values string

$ref = []; // add references

foreach ($params as $i => $v) {

$ref[$i] = &$params[$i]; // pass by reference as required/advised by the manual

}

call_user_func_array([$stmt, 'bind_param'], $ref);

if (!$stmt->execute()) {

echo "Error @ bind_param/execute: " , $stmt->error; // don't show to public

} elseif (!$stmt->bind_result($question_name)) {

echo "Error @ bind_result: " , $stmt->error; // don't show to public

} else {

while ($stmt->fetch()) {

// do something with $question_name

}

$stmt->close();

}

}

p.s. If you want to know the type value from the same row as the question_name be sure to SELECT both columns so that they are in your resultset.