使用for循环构建数据库查询 [英] building db query with a for loop
问题描述
我已经做了一个查询数据库的功能.此函数接受一个数组,即我要更新的用户的ID 和查询操作.
I've made a function to query the database. This function takes an array, the id of the user I want to update and a query operation.
如果查询操作是UPDATE
if the query operation is UPDATE
如果您看下面的代码,这是一个好的编码实践还是这个不好的代码?
if you look at the code below, would this be a good coding practice or is this bad code?
public function query($column, $search_value, $query_operation = "SELECT"){
if(strtoupper($query_operation == "UPDATE")){
$query = "UPDATE users SET ";
if(is_array($column)){
$counter = 1;
foreach($column as $key => $value){
if($counter < count($column)){
$query .= $key . ' = ?, ';
}else{
$query .= $key . ' = ? ';
}
$counter++;
}
$query .= "WHERE id = ?";
$stmt = $this->database->prepare($query);
$counter = 1;
foreach($column as $key => &$value){
$stmt->bindParam($counter, $value);
$counter++;
}
$stmt->bindParam($counter, $search_value);
if($stmt->execute()){
$stmt = $this->database->prepare("SELECT* FROM
users WHERE id = ?");
$stmt->bindParam(1, $search_value, PDO::PARAM_INT);
$stmt->execute();
return $this->build_array($stmt);
}
}
}
}
希望听到一些反馈.
推荐答案
我不会不在同一函数中混合 SELECT 和 UPDATE .
I would NOT mix SELECT and UPDATE in the same function.
以下更新功能将数组用于列名和值$columnNames
& $values
使用未命名的参数.
The following update function uses arrays for column names and values $columnNames
& $values
using unnamed parameters.
function update($tableName,$columnNames,$values,$fieldName,$fieldValue){
$sql = "UPDATE `$tableName` SET ";
foreach($columnNames as $field){
$sql .= $field ." = ?,";
}
$sql = substr($sql, 0, -1);//remove trailing ,
$sql .= " WHERE `$fieldName` = ?";
return $sql;
}
由于不能在PDO中将表名和列名作为参数传递,因此我已经证明了对表名的白名单.
As table and column names cannot be passed as parameters in PDO I have demonstrated whitelistng of table names.
$tables = array("client", "Table1", "Table2");// Array of allowed table names.
也array_push()
将最后一个参数(WHERE)的值添加到$ values数组中
Also array_push()
to add value for last parameter (WHERE) into $values array
使用
if (in_array($tableName, $tables)) {
$sql = update($tableName,$columnNames,$values,$fieldName,$fieldValue);
array_push($values,$fieldValue);
$STH = $DBH->prepare($sql);
$STH->execute($values);
}
您可以对 SELECT
这篇关于使用for循环构建数据库查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!