我对PHP相当陌生。我需要通过编写一个自定义函数来绑定PDO中的参数。
假设这是我的2个sql。
sample_sql_1="select f_name, age, address from table1 where l_name=? and dob >= ? and cty =?"
sample_sql_2="select * from table2 where cty=?"
我想写一个函数，接受有问题的sql查询&将要绑定的参数绑定到问号，而不管我通过了多少个参数。
示例：我想打电话给
bind_params(sample_sql_1,array($name,$dob,$cty));
bind_params(sample_sql_2,array($cty));
这是我到目前为止写的只连接到DB 的函数
function pdo_db_query($query) {
try {
# MySQL with PDO_MYSQL
$DBH = new dbconn(); // Create DB connection
$DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$DBH->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );
$STH = $DBH->prepare($query);
// Please help to create a dynamic function to bind
bind_params(sample_sql_1,array($name,$dob,$cty));
bind_params(sample_sql_2,array($cty));
/ Execute the query
$STH->execute();
# setting the fetch mode
$STH->setFetchMode(PDO::FETCH_ASSOC);
// Create temporary array variable
$json_arr = array();
while ($row = $STH->fetch()) {
$json_arr[] = $row;
}
# Close the connection
$DBH = null;
// Return the result set as a json
echo json_encode($json_arr);
} catch (PDOException $e) {
echo $e->getMessage();
var_dump($e->getMessage());
}
}
我需要帮助编写函数"bind_params"。任何帮助都会使我受益匪浅。
您不一定需要bind_params()，只需将值作为数组提供给execute()即可。
请参阅文档中的示例：
/* Execute a prepared statement by passing an array of insert values */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
FROM fruit
WHERE calories < ? AND colour = ?');
$sth->execute(array($calories, $colour));
特别针对您的案例：
// add a parameter for the values
function pdo_db_query($query, $params = array()) {
try {
# MySQL with PDO_MYSQL
$DBH = new dbconn(); // Create DB connection
$DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$DBH->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );
$STH = $DBH->prepare($query);
// Execute the query with the given params
$STH->execute($params);
# setting the fetch mode
$STH->setFetchMode(PDO::FETCH_ASSOC);
// Create temporary array variable
$json_arr = array();
while ($row = $STH->fetch()) {
$json_arr[] = $row;
}
# Close the connection
$DBH = null;
// Return the result set as a json
echo json_encode($json_arr);
} catch (PDOException $e) {
echo $e->getMessage();
var_dump($e->getMessage());
}
}
将其与LIKE查询一起使用：
$query = "SELECT * FROM table WHERE field LIKE ?";
$params = array( '%' . $searchvalue . '%' );
$result = pdo_db_query( $query, $params );
以下代码使用"Lazy"绑定，通过数组将数据传递到execute中。它启用？要插入的占位符和要插入的and，具体取决于$columnArray中传递的列数。我已经注释掉了一些特定于数据库的代码，使您能够了解查询是如何形成的。您需要在WHERE子句之前传递一个列名数组以及sql语句的第一部分。
我添加了用于测试的示例数据和显示查询的代码，以及execute（）的参数。这些应该被删除，并恢复注释的代码以使用数据库进行测试。
样本结果
select f_name, age, address from table1 WHERE name = ? AND dob = ? AND cty = ?
阵列（[0]=>Tom[1]=>2014-11-11[2]=>伦敦）
function pdo_db_query($query,$columnArray) {
/* try {
# MySQL with PDO_MYSQL
$DBH = new dbconn(); // Create DB connection
$DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$DBH->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );
// Please help to create a dynamic function to bind
bind_params(sample_sql_1,array($name,$dob,$cty));
bind_params(sample_sql_2,array($cty));
*/
// Set the first clause to WHERE
$clause = " WHERE ";
foreach ($columnArray as $column) {
//Add column name and ? placeholder
$query .= "$clause $column = ?";
//Change WHERE to And for remaining conditions
$clause = " AND ";
}
//This echo is to show query
echo $query."<BR>";
// Execute query using Lazy Binding passing data into execute via array
/*$STH->execute($paramArray);
/*
// Create temporary array variable
$json_arr = array();
while ($row = $STH->fetch()) {
$json_arr[] = $row;
}
# Close the connection
$DBH = null;
*/
}
// Parameters for testing
$name ="Tom";
$dob ="2014-11-11";
$cty ="London";
$paramArray1 = array($name,$dob,$cty);
$paramArray2 = array($cty);
$columnArray1 = array("name","dob","cty");
$columnArray2 = array("cty");
$query = "select f_name, age, address from table1";
pdo_db_query($query,$columnArray2) ;
print_r($paramArray2);
?>