如何使arg中的参数数量未知的PDO类方法用于插入/更新/删除 [英] How to make a PDO class method for inserting/updating/deleting with an unknown number of parameters in the arg
问题描述
目前,我正在尝试创建一个PDO类,在该类中,我将有一种方法来运行诸如INSERT,UPDATE或DELETE之类的查询.
Currently Im trying to create a PDO class where I will have a method to run a query like INSERT, UPDATE, or DELETE.
例如,这是我的SELECT方法
For examaple this is my method for a SELECT
public function getPreparedQuery($sql){
$stmt = $this->dbc->prepare($sql);
$stmt->execute([5]);
$arr = $stmt->fetchAll(PDO::FETCH_ASSOC);
if(!$arr) exit('No rows');
$stmt = null;
return $arr;
}
我只是这样称呼它:
$stmt = $database->getPreparedQuery($sql2);
var_export($stmt);
到目前为止,我知道runQuery的工作方式应与此类似:
And so far I know that a runQuery should work something similar to this:
不使用方法插入示例:
$idRol = "6";
$nomRol = "test6";
$stmt = $database->dbc->prepare("insert into roles (idRol, NomRol) values (?, ?)");
$stmt->execute(array($idRol,$nomRol));
$stmt = null;
但是我想使其成为通用方法,在这里我可以简单地传递sql语句,如下所示:
But I want to make it into an universal method where i simply can pass the sql sentence, something like this:
$database->runQuery($query);
但查询可能恰好是
$ query =将角色插入(idRol,NomRol)值('4','test')"";
$query = "INSERT INTO roles (idRol, NomRol) VALUES ('4','test')";
或
$ query =将影片插入(movName,movLength,movArg)值('name1','15','movie about ...'))";
$query = "INSERT INTO movies (movName, movLength, movArg) VALUES ('name1','15','movie about...')";
那么我该如何对arg $ query进行切片,以便获得所有用于创建通用runQuery的变量?
So how do I slice the arg $query so I can get all the variables that are being used in order to make an universal runQuery?
因为我可以想象我的方法应该是这样的
Because I can imagine that my method should be something like this
runQuery([$var1 , $var2, $var3....(there can be more)] , [$var1value, $var2value, $var3value...(there can be more)]){
$stmt = $database->dbc->prepare("insert into movies($var1 , $var2, $var3....(there can be more)) values (?, ? , ? (those are the values and there ca be more than 3)"); //how do i get those "?" value and amount of them, and the name of the table fields [movName, movLength, movArg can be variable depending of the sentence]?
$stmt->execute(array($var1,$var2, $var3, ...)); //this is wrong , i dont know how to execute it
$stmt = null;
}
推荐答案
您需要在函数中添加第二个参数.只是所有这些变量都将经过的数组.根据定义,数组可以具有任意数量的元素,这可以完全解决您的问题:
You need to add a second parameter to your function. Simply an array where all those variables would go. An array by definition can have an arbitrary number of elements, which solves your problem exactly:
public function runQuery($sql, $parameters = []) {
$stmt = $this->dbc->prepare($sql);
$stmt->execute($parameters);
return $stmt;
}
此简单功能将运行ANY查询.您可以在我的文章中看到有关 PDO辅助函数:
this simple function will run ANY query. You can see the usage example in my article dedicated to PDO helper functions:
// getting the number of rows in the table
$count = $db->runQuery("SELECT count(*) FROM users")->fetchColumn();
// the user data based on email
$user = $db->runQuery("SELECT * FROM users WHERE email=?", [$email])->fetch();
// getting many rows from the table
$data = $db->runQuery("SELECT * FROM users WHERE salary > ?", [$salary])->fetchAll();
// getting the number of affected rows from DELETE/UPDATE/INSERT
$deleted = $db->runQuery("DELETE FROM users WHERE id=?", [$id])->rowCount();
// insert
$db->runQuery("INSERT INTO users VALUES (null, ?,?,?)", [$name, $email, $password]);
// named placeholders are also welcome though I find them a bit too verbose
$db->runQuery("UPDATE users SET name=:name WHERE id=:id", ['id'=>$id, 'name'=>$name]);
// using a sophisticated fetch mode, indexing the returned array by id
$indexed = $db->runQuery("SELECT id, name FROM users")->fetchAll(PDO::FETCH_KEY_PAIR);
如您所见,现在您的函数可以用于具有任意数量参数的任何查询
As you can see, now your function can be used with any query with any number of parameters
这篇关于如何使arg中的参数数量未知的PDO类方法用于插入/更新/删除的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!