如何在mysql查询的where子句中传递数组值? [英] How to pass array values in where clause of mysql query?
问题描述
我有一个变量$element
,其值为:
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"]. "<br>";
}
}
实际上,我想从$ element变量的每个元素中进行解析,并分别呈现不同的输出.
Actually I want to parse from each element of $element variable and present different output respectively.
推荐答案
首先,您应该在$element
上调用array_unique()
.
First, you should call array_unique()
on $element
.
如果这是您正在使用的受信任数据,并且值从不包含单引号,则可以将其插入IN
子句中:
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) . "')";
如果这不是受信任的数据,则建议使用带占位符的预备语句.对于IN
子句,这有点令人费解.
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.如果您想从question_name
所在的行中知道type
值,请确保同时选择两列,以便它们都在结果集中.
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.
这篇关于如何在mysql查询的where子句中传递数组值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!