問題描述

目前我正在嘗試創建一個 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) VALUES ('4','test')";

$query = "INSERT INTO roles (idRol, NomRol) VALUES ('4','test')";

或

$query = "插入電影 (movName, movLength, movArg) VALUES ('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;
}

這個簡單的函數將運行任何查詢.您可以在我專門介紹 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

這篇關于如何制作 PDO 類方法，用于在 arg 中使用未知數量的參數插入/更新/刪除的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！