我正在创建一个自定义数据库类,以满足我正在开发的公司的需求。我现在有这个:
class DBC {
protected $Link;
protected $Results;
public function __construct($Host = null,$User = null ,$Pass = null,$Database = null){
if ($Host === null OR $User === null OR $Pass === null OR $Database === null){
trigger_error("Incorrect Parameters Passed In The Database Link", E_USER_WARNING);
}
if (is_string($Host) AND is_string($User) AND is_string($Pass) AND is_string($Database)){
$this->Link = new mysqli($Host,$User,$Pass,$Database);
}else{
trigger_error("Expecting String(s), Array passed in one or more connection parameters",E_USER_ERROR);
}
}
public function Query ($Query,$Params){
$Query = $this->Link->prepare($Query);
$Query->bind_param();
}
}
现在. .我有一个问题,如何成功地将参数绑定到准备好的语句。例如,一个查询将以如下方式提交:
$DB = new DBC("Host","User","pass","database");
$DB->Query("SELECT * FROM Test WHERE Col=?",array("SearchCriteria"));
我遇到了一个block与弄清楚如何bind_param和bind_result基于结果。更清晰的理解是MySQLi:
的正常过程$SearchCriteria = "String";
$Query = $Database->prepare("SELECT * FROM Test WHERE Col=?");
$Query->bind_param('s',$SearchCriteria);
$Query->execute();
$Query->bind_results(/* Variables to match the column set */);
$Query->fetch();
$Query->close();
如何将结果和参数绑定到准备好的语句?
下面是我在扩展mysqli类的类中使用的函数的副本,它做你所要求的。
function bind_placeholder_vars(&$stmt,$params,$debug=0) {
// Credit to: Dave Morgan
// Code ripped from: http://www.devmorgan.com/blog/2009/03/27/dydl-part-3-dynamic-binding-with-mysqli-php/
if ($params != null) {
$types = ''; //initial sting with types
foreach ($params as $param) { //for each element, determine type and add
if (is_int($param)) {
$types .= 'i'; //integer
} elseif (is_float($param)) {
$types .= 'd'; //double
} elseif (is_string($param)) {
$types .= 's'; //string
} else {
$types .= 'b'; //blob and unknown
}
}
$bind_names = array();
$bind_names[] = $types; //first param needed is the type string
// eg: 'issss'
for ($i=0; $i<count($params);$i++) { //go through incoming params and added em to array
$bind_name = 'bind' . $i; //give them an arbitrary name
$$bind_name = $params[$i]; //add the parameter to the variable variable
$bind_names[] = &$$bind_name; //now associate the variable as an element in an array
}
if ($debug) {
echo "'$bind_names:<br />'n";
var_dump($bind_names);
echo "<br />'n";
}
//error_log("better_mysqli has params ".print_r($bind_names, 1));
//call the function bind_param with dynamic params
call_user_func_array(array($stmt,'bind_param'),$bind_names);
return true;
}else{
return false;
}
}
function bind_result_array($stmt, &$row) {
// Credit to: Dave Morgan
// Code ripped from: http://www.devmorgan.com/blog/2009/03/27/dydl-part-3-dynamic-binding-with-mysqli-php/
$meta = $stmt->result_metadata();
while ($field = $meta->fetch_field()) {
$params[] = &$row[$field->name];
}
call_user_func_array(array($stmt, 'bind_result'), $params);
return true;
}
然而,听起来你正在做的事情类似于我已经做过的事情,并且已经在许多项目中使用了一段时间了。将这个pastebin (better_mysqli.php)的内容复制到一个新文件中,并命名为'better_mysqli.php'
然后在你的php程序中这样使用:
// include the class
include_once('better_mysqli.php');
// instantiate the object and open the database connection
$mysqli = new better_mysqli('yourserver.com', 'username', 'password', 'db_name');
if (mysqli_connect_errno()) {
die("Can't connect to MySQL Server. Errorcode: %s'n", mysqli_connect_error()), 'error');
}
// do a select query
$sth = $mysqli->select('select somecol, othercol from sometable where col1=? and col2=?', $row, array('col1_placeholder_value', 'col2_placeholder_value'));
while ($sth->fetch()) {
echo "somecol: ". $row['somecol'] ."<br />'n";
echo "othercol: ". $row['othercol'] ."<br />'n";
}
// the nice thing about this class is that the statement is only prepared once so if you use it again the already prepared statement is automatically used:
// do another select query with different placeholder values
$sth = $mysqli->select('select somecol, othercol from sometable where col1=? and col2=?', $row, array('other_col1_placeholder_value', 'other_col2_placeholder_value'));
while ($sth->fetch()) {
echo "somecol: ". $row['somecol'] ."<br />'n";
echo "othercol: ". $row['othercol'] ."<br />'n";
}
// the class supports the following methods: select, update, insert, and delete
// example delete:
$mysqli->delete('delete from sometable where col1=?', array('placeholder_val1'));