在 PHP 中使用关联数组构造 UPDATE 语句 [英] Constructing UPDATE statements using associative arrays in PHP
问题描述
在 PHP 中使用关联数组构造 UPDATE 语句的最佳方法是什么?
What is the best way to construct an UPDATE statement using associative arrays in PHP?
例如,假设我有一个这样的函数:
For example, say I have a function like so:
/**
* For update queries
*
* @param string $tableName Name of the table we're wanting to update.
* @param array $values Associative array of columns / values to update. e.g. array('name' => 'John', 'age' => 29)
* @param array $conditions Associative array of conditions. e.g. array('user_id' => 1) equates to "WHERE user_id = 1"
*/
public function update($tableName, $values, $conditions = array()){
//Construct SQL
}
到目前为止,我已经能够构建简单的 UPDATE 语句,例如:
So far, I've been able to construct simple UPDATE statements such as:
UPDATE `myTableName` SET `name` = :name, `age` = :age WHERE `user_id` = :user_id
现在我想知道:构建 WHERE 子句的最佳方法是什么?我可以研究的其他库和代码库中是否有类似的实现?例如:如何构造具有 OR 和 AND 以及 IN() 等的 WHERE 子句?
Now I'm left wondering: What is the best way to approach constructing the WHERE clause? Are there similar implementations in other libraries and code bases that I can look into? For example: How do I approach the construction of WHERE clauses that have OR and AND and IN() etc?
UPDATE example SET col = :val WHERE user_id = :user_id AND (age = :age OR name = :name)
推荐答案
public function update($tableName, $values, $conditions = array()) {
if (empty($values)) {
throw new Exception('Nothing to update');
}
$valueStrings = array();
foreach ($values as $name => $value) {
$valueStrings[] = $name . ' = :' . $name;
}
$conditionStrings = array();
foreach ($conditions as $column => $value) {
$conditionString = $column;
$conditionString .= is_array($value)
? ('IN ("' . implode('","', $value) . '")')
: (' = "' . $value . '"')
;
$conditionStrings[] = $conditionString;
}
$sql = 'UPDATE ' . $tableName
. ' SET ' . implode(', ', $valueStrings)
. ' WHERE ' . implode(' AND ', $conditionStrings)
;
// execute query
}
但实际上您应该为此使用 ORM:
But actually you should use an ORM for that:
这篇关于在 PHP 中使用关联数组构造 UPDATE 语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!