作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我正在尝试为数组是混合数组的 IN 子句编写准备好的语句。我能够为“call_user_func_array()”构建数组,但我无法准备语句。没有输出显示。
这是我的 php 代码:
$search1 = array('pune','india','2014','mumbai','2015');
print_r($search1);
echo $param = implode(",",$search1);
$ids = array_flip(array_flip(explode(',', $param)));
var_dump($ids);
$type1 = array();
foreach ($ids as $element) {
if(is_string($element)) {
$type1[] ='s';
}elseif(is_int($element)) {
$type1[] ='i';
}
}
print_r($type1);
echo $type=implode("",$type1);
$inputArray[] = &$type;
$j = count($ids);
for($i=0;$i<$j;$i++){
$inputArray[] = &$ids[$i];
}
print_r($inputArray);
echo $clause = implode(',', array_fill(0, count($ids), '?'));
$construct .="(city in ('.$clause.') or state in ('.$clause.')
or country in ('.$clause.')) AND year in ('.$clause.') order by year desc";
$constructs ="SELECT * FROM info WHERE $construct";
if($stmt1 = mysqli_prepare($conn, $constructs)){
call_user_func_array(array($stmt1,'bind_param'),$inputArray);
if(mysqli_stmt_execute($stmt1)){
$result = mysqli_stmt_get_result($stmt1);
if(mysqli_num_rows($result) > 0){
echo $foundnum = mysqli_num_rows($result);
while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
echo $id = $row['id'];
echo $name = $row['name'];
}
}
}
}
mysqli_stmt_close($stmt1);
最佳答案
100% 测试成功的代码 :
$search1 = ['2014','pune','india','2014','mumbai','2015','mumbai'];
// Separate logically and remove duplicates
foreach ($search1 as $value) {
if (strlen($value)==4 && ctype_digit($value)) { // qualifies for the year column
$years[$value] = null;
} else {
$strings[$value] = null;
}
}
$years = array_keys($years); // move keys to values
$years_count = sizeof($years);
$strings = array_keys($strings); // move keys to values
$strings_count = sizeof($strings);
if (!$years_count || !$strings_count) { // this is a matter of program logic
echo "A minimum of one year value and one non-year value is required for search functionality.";
}elseif (!$conn = new mysqli("host", "user","pass","db")) {
echo "Database Connection Error: " , $conn->connect_error; // don't show to the public
} else {
$years_csph = implode(',', array_fill(0, $years_count, '?')); // comma-separated placeholders
$strings_csph = implode(',', array_fill(0, $strings_count, '?')); // comma-separated placeholders
$total_count = $strings_count * 3 + $years_count;
$total_params = array_merge($strings, $strings, $strings, $years);
$param_string = str_repeat('s', $strings_count * 3) . str_repeat('i', $years_count); // write s chars before i chars
if(!$stmt = $conn->prepare("SELECT id, name FROM info WHERE (city IN ($strings_csph) OR state IN ($strings_csph) OR country IN ($strings_csph)) AND year IN ($years_csph) ORDER BY year DESC")) {
echo "Syntax Error @ prepare: " , $conn->error; // don't show to public
}else{
array_unshift($total_params, $param_string); // prepend the type values string
$ref = []; // add references
foreach ($total_params as $i => $v) {
$ref[$i] = &$total_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($id, $name)) {
echo "Error @ bind_result: " , $stmt->error; // don't show to public
} else {
while ($stmt->fetch()) {
echo "<div>$id : $name</div>";
}
$stmt->close();
}
}
}
'.$clause.'
看起来不太好,因为它在占位符周围写了单引号和点。占位符永远不需要单引号,即使这样做,语法也会不正确。
关于php - 如果有混合类型数组,如何为 IN 子句准备语句?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/51034485/
我是一名优秀的程序员,十分优秀!