我需要一个良好的结构来构建查询,其中搜索参数是使用mysqli准备的语句条件的。$query -> bind_param('sss',$date,$time,$place);
我不知道以后如何按顺序应用'sss'
和'$date,$time,$place'
参数。你能把它们作为变量传递吗?
旧MySQL方式:
<?php
// date is obligatory
$date = mysql_real_escape_string($_GET["date"]);
$query="SELECT * FROM dbase WHERE date='$date'";
// time field is custom
if(!empty($_GET["time"])) {
$time= mysql_real_escape_string($_GET["time"]);
$buildQuery[] = "time='$time'";
}
// place field is also custom
if(!empty($_GET["place"])) {
$place= mysql_real_escape_string($_GET["place"]);
$buildQuery[] = "place='$place'";
}
// building query
if(!empty($build)) {
$query .= ' AND '.implode(' AND ',$build).' ORDER BY date';
}
?>
这是一个很好的例子,其中PDO比MySQLi:容易得多
$query="SELECT * FROM dbase";
$terms = array("date" => $date);
$params = array();
// time field is custom
if(isset($_GET["time"])) {
$terms["sType"] = $time;
}
// place field is also custom
if(isset($_GET["place"])) {
$terms["place"] = $place;
}
// building query
if(!empty($terms)) {
$query .= "WHERE " . implode(" AND ",
array_map(function($term) { return "$term = ?"; }, array_keys($terms));
$params = array_values($terms);
}
$query .= "ORDER BY date";
$stmt = $pdo->pepare($query);
$stmt->execute($params);
附言:我不得不质疑你的sType
列是否同时包含时间和地点。似乎您正在打破关系数据库的最佳实践。除非只是打字错误。
未经测试,但您应该能够执行类似的操作
$types = array();
$vals = array();
if(isset($_GET["time"])) {
$types[] = 's';
$vals[] = $_GET["time"];
$buildQuery[] = "sType = ?";
}
//...etc...
$args = array_merge(array(join($types)), $vals);
$callable = array($mysqli, 'bind_param');
call_user_func_array($callable, $args));
http://php.net/manual/en/language.types.callable.php
但是,还有另一种方法。只需在sql:中使用逻辑
select *
from tbl
where (date = ? or ? = '')
and (time = ? or ? = '')
and (place = ? or ? = '')
上面假设您将每个arg绑定两次,并将它们作为字符串绑定,但如果查询字符串param未设置,则绑定一个空字符串。。。如果需要,也可以通过(date = ? or ? is null)
之类的方法将它们绑定为null。
$mysqli->bind_param('ssssss', $date,$date, $time,$time, $place,$place);
ps,mysql优化器将对这个简单的逻辑进行简短的处理。
基于goat的回复,这里有一个完整的、经过测试的答案,带有一个UPDATE语句,其中要更新的字段是有条件的。我使用了一个非常简单的表结构,包含3个字段:ID(自动递增)、Varchar1(varchar255)和Varchar2(varchar255.)。在这个脚本中,我想更新前三条记录的两个varchar字段。基于这个脚本,很容易有条件地添加或删除要更新的字段。
$mysqli = new mysqli(...);
$types = array();
$vals = array();
$query = array();
// varchar1
$types[] = 's';
$vals[] = 'foo1';
$query[] = "varchar1=?";
// varchar2
$types[] = 's';
$vals[] = 'foo2';
$query[] = "varchar2=?";
$sql = "UPDATE test SET ".implode(",", $query)." WHERE id IN (1,2,3)";
$stmt = $mysqli->prepare($sql);
$args = array_merge(array(implode($types)), $vals);
$callable = array($stmt, 'bind_param');
call_user_func_array($callable, refValues($args));
$stmt->execute();
function refValues($arr) {
if (strnatcmp(phpversion(),'5.3') >= 0) //Reference is required for PHP 5.3+
{
$refs = array();
foreach($arr as $key => $value)
$refs[$key] = &$arr[$key];
return $refs;
}
return $arr;
}