更有效地绑定大插入或更新的方法？ [英] A more efficient way of binding a big insert or update?

问题描述

好，所以im new to binding，这里有一些代码可以工作。我从一个教程中学到了这种格式，但我想，有更有效的方式来做。在我的例子中有4个名字，但实际上我将在一个项目中进行大量的插入和更新，这个项目将有20个左右的字段。我喜欢这种方法，因为它的清晰度，但显然，当你说20个领域或更多它确实需要很多房地产。让我们先看看我的代码。

这是它使用的功能：

  //准备语句
 public function query（$ query）{
 $ this-> stmt = $ this-> dbh-> prepare（$ query）; 
} 
 
 public function bind（$ param，$ value，$ type = null）{
 if（is_null（$ type））{
 switch（true） {
 case is_int（$ value）：
 $ type = PDO :: PARAM_INT; 
 break; 
 case is_bool（$ value）：
 $ type = PDO :: PARAM_BOOL; 
 break; 
 case is_null（$ value）：
 $ type = PDO :: PARAM_NULL; 
 break; 
 default：
 $ type = PDO :: PARAM_STR; 
} 
} 
 //运行绑定进程
 $ this-> stmt-> bindValue（$ param，$ value，$ type）; 
} 
 
 //执行准备的语句
 public function execute（）{
 return $ this-> stmt-> execute（）; 
} 
  

现在的实际代码

$ b $ （
user_name，
user_password_hash，
user_email，
user_activation_hash
）

VALUES（
：user_name，
：user_password_hash，
：user_email，
：user_activation_hash
）
）;

//绑定值
$ database-> bind（：user_name，$ this-> user_name）;
$ database-> bind（：user_password_hash，$ this-> user_password_hash）;
$ database-> bind（：user_email，$ this-> user_email）;
$ database-> bind（：user_activation_hash，$ this-> user_activation_hash）;

//执行该语句并将值插入数据库
$ database-> execute（）;

它只是为了循环而哭泣，特别是因为我习惯于调用帖子字段，输入字段，变量和占位符相同的名称，不知道这是否是一个好的或坏的事情，但是我在处理我将要使用的大型表单时对我有帮助。

无论如何我可以这样做：

  $ placeholder_array = array（
user_name=> \ $ this-> user_name，
user_password_hash=>\ $ this-> user_password_hash，
user_email=>\ $ this-> user_email，
user_activation_hash=>\ $ this-> user_activation_hash
）; 
 
 //很好地使用此副本来编辑数组键并保留原来的绑定
 $ placeholder_copy = $ placeholder_array; 
 
 
 //将数组转换成一个字符串，即user_name，user_password_hash .... 
 $ fields = implode（，，array_keys（$ placeholder_array））; 
 
 // foreach来添加：用于绑定的占位符前缀
 foreach（$ placeholder_copy as $ key => $ value）{
 $ placeholder_copy ['：'。$ key] = $ value; 
 unset（$ placeholder_copy [$ key]）; 
} 
 
 //将具有前缀：user_name的复制数组转换为字符串
 $ placeholder = implode（，，array_keys（$ placeholder_copy））; 
 
 $ database-> query（
 INSERT INTO users（$ fields）
 VALUES（$ placeholder）
）; 
 
 //绑定值
 foreach（$ placeholder_copy as $ bind_values => $ value）{
 echo'$ database-> bind（'。$ bind_values。 '，'。$ value。'）;'。 < br />; 
} 
 
 //执行该语句并将值插入数据库
 $ database-> execute（）; 
  

然后我可以将其转换为带有参数的函数，用于传递关联数组和表名保持我的主要代码更清洁。

现在我想要做这些任务，因为项目涉及到大量的大型表单向用户提交数据。我是新来的PDO，并试图抓住它，所以可能一个更简单的方式来构造这些类型的查询，我看了谷歌和堆栈流，但我没有真正得到他们在做什么，所以我认为做自己的一个会允许人们更好地向我解释发生了什么，我宁愿得到这个权利开始我的项目，而不是回去改变一切。那么有更好的方法还是这个可以吗？

真的感谢任何反馈，我很高兴现在我在这里提出了人们的建议，并将其转移到PDO。 p>

解决方案

我最近所做的工作不断完善，正在构建一个帮助类，以简化应用程序中基于PDO的SQL语句。 / p>

让我们来看看你的例子。您要将数据插入到用户表中：

  INSERT INTO用户（
 user_name，
 user_password_hash， 
 user_email 
 user_activation_hash 
）VALUES（
：user_name，
：user_password_hash，
：user_email，
：user_activation_hash 
） 
  

为了插入单个记录，我的SQL构造如下：

  function myInsertSingle（$ PDO，$ table，$ ins_array）{
 $ SQL =INSERT INTO`$ table。 dbFieldList（$ ins_array）
。）VALUES（.dbValuePList（$ ins_array）。）; 
  

...继续准备。

• $ PDO ：我的PDO连接。


• $ table ：我要插入的表。


• $ ins_array ：用于插入数据的结构化数组。

对于您的示例，数组 $ ins_array 将如下所示： / p>

  $ ins_array = array（
user_name=>your_user，
user_password_hash= >your_pw_hash，
user_email=>[e-mail]，
user_activation_hash=>your_other_Hash
）; 
  

注意与阵列的相似性

涉及两个功能。第一个给我一个（转义）字段名称列表。

 函数dbFieldList（$ fields）{
 $ set =''; 
 foreach（$ fields as $ field => $ item）{
 $ set。=`。$ field。`，; 
} 
 return rtrim（$ set，'，'）; 
} 
  

从上面的数组示例中，函数返回

 `user_name`，`user_password_hash`，`user_email`，`user_activation_hash` 
  

...这是 INSERT -statement中所需字段的列表。

另一个对于值类似。

 函数dbValuePList（$ fields）{
 $ set =''; 
 foreach（$ fields as $ field => $ item）{
 $ set。=：$ field。，; 
} 
 return rtrim（$ set，'，'）; 
} 
  

你猜到了，结果如下：

 ：user_name，：user_password_hash，user_email，user_activation_hash 
  

现在你有你可以准备的SQL语句。要绑定值，如果我正确地理解你的类，只需使用一个如下所示的循环：

  foreach（$ ins_array as $ field => $ item）{
 $ PDO-> bind（'：'。$ field，$ item）; 
} 
  

现在您可以执行该语句。

总结。基本上与我的方法一个单一的INSERT语句减少为：

  $ ins_array = array（
user_name= "your_user，
user_password_hash=>your_pw_hash，
user_email=>[e-mail]，
user_activation_hash=>your_other_Hash 
）; 
 
 myInsertSingle（$ PDO，'your_table'，$ ins_array）; 
  

如果这看起来像另一段开销，请记住，我在其他SQL中使用这些功能语句如SELECT，UPDATE，DELETE等。

Ok so im new to binding, here is some code that works. I learned this format from a tutorial but i imagine there is more efficent ways to do it. In my example there is 4 names but in reality i will be doing a lot of inserts and updates in a project im working on that will have 20 or so fields. I like this approach for its clarity but obviously when your talking 20 fields or more it does take a lot of real estate. Lets look at my code first.

Here are the functions it uses:

// prepare the statement
public function query($query){
    $this->stmt = $this->dbh->prepare($query);
}

public function bind($param, $value, $type = null){
    if (is_null($type)) {
        switch (true) {
            case is_int($value):
                $type = PDO::PARAM_INT;
                break;
            case is_bool($value):
                $type = PDO::PARAM_BOOL;
                break;
            case is_null($value):
                $type = PDO::PARAM_NULL;
                break;
            default:
                $type = PDO::PARAM_STR;
        }
    }
// run the binding process
$this->stmt->bindValue($param, $value, $type);
}

// execute the prepared statement
public function execute(){
    return $this->stmt->execute();
}

and now the actual code

$database->query("
    INSERT INTO users(
        user_name,
        user_password_hash,
        user_email,
        user_activation_hash
)

    VALUES(
        :user_name,
        :user_password_hash,
        :user_email,
        :user_activation_hash
    )
");

// bind the values
$database->bind(":user_name", "$this->user_name");
$database->bind(":user_password_hash", "$this->user_password_hash");
$database->bind(":user_email", "$this->user_email");
$database->bind(":user_activation_hash", "$this->user_activation_hash");

// execute the statement and insert the values into the database
$database->execute();

it just cries out for a loop, especially since i have a habit of calling post fields, input fields, variables and placeholders the same name, not sure if that is a good or a bad thing but i find its helpful for me when dealing with large forms which i will be.

in any case i could do something like this:

$placeholder_array = array(
    "user_name"               => "\$this->user_name",
    "user_password_hash"      => "\$this->user_password_hash",
    "user_email"              => "\$this->user_email",
    "user_activation_hash"    => "\$this->user_activation_hash"
);

// well use this copy to edit the array keys and keep original for the binding
$placeholder_copy = $placeholder_array;


// turn the array into a string i.e user_name, user_password_hash....
$fields = implode (", ", array_keys($placeholder_array));

// foreach to add : placeholder prefix for binding
foreach ($placeholder_copy as $key => $value){
$placeholder_copy [':'.$key] = $value;
unset($placeholder_copy[$key]);
}

// turn the copy array which has prefix :user_name into a string 
$placeholders = implode (", ", array_keys($placeholder_copy));

$database->query("
    INSERT INTO users($fields)
    VALUES($placeholders) 
");

// bind the values
foreach ($placeholder_copy as $bind_values => $value){
    echo '$database->bind("'.$bind_values.'", "'.$value.'");' . "<br />";
}

// execute the statement and insert the values into the database
$database->execute();

i could then turn this into a function with parameters for passing in the associative array and the table name to keep my main code much cleaner.

Now imagine i am going to be doing any amount of these as the project im working on involves tons of big forms submitting data to users. I'm new to PDO and trying to grasp it so there maybe a simpler way of structuring these types of queries, i had a look on google and stackflow but i didnt really get what they were doing so i thought doing my own one would allow people to explain to me better what is going on, i would rather get this right starting my project than have to go back and change everything later. So is there a better approach or is this one ok?

Really appreciate any feedback and im glad now i took peoples advice on here and made the move to PDO.

解决方案

What I've done recently and continuing to improve, is building a helper class to simplify PDO based SQL statements in my applications.

Lets take a tour with your example. You want to insert data into a user table:

INSERT INTO users(
    user_name,
    user_password_hash,
    user_email,
    user_activation_hash
) VALUES(
    :user_name,
    :user_password_hash,
    :user_email,
    :user_activation_hash
)

For inserting a single record, my SQL is constructed like this:

function myInsertSingle($PDO, $table, $ins_array) {
    $SQL = "INSERT INTO `".$table."` (".dbFieldList($ins_array)
            .") VALUES (".dbValuePList($ins_array).")";

...continued with preparing.

• $PDO: My PDO connection.
• $table: The table I want to insert to.
• $ins_array: A structured array for inserting data.

For your example, the array $ins_array will look like this:

$ins_array = array(
    "user_name"               => "your_user",
    "user_password_hash"      => "your_pw_hash",
    "user_email"              => "[e-mail]",
    "user_activation_hash"    => "your_other_Hash"
);

Notice the similarity to your array!

Two functions are involved. The first one gives me a list of (escaped) fieldnames.

function dbFieldList($fields) {
    $set = '';
    foreach ($fields as $field => $item) {
        $set .= "`".$field."`,";
    }   
    return rtrim($set, ',');
}

Basically from the above array example, the function returns

`user_name`, `user_password_hash`, `user_email`, `user_activation_hash`

...which is the list of the fields needed in the INSERT-statement.

The other one does something similar for the values.

function dbValuePList($fields) {
    $set = '';
    foreach ($fields as $field => $item) {
        $set .= ":".$field.",";
    }   
    return rtrim($set, ',');
}

You guessed it, the result looks like this:

:user_name, :user_password_hash, :user_email, :user_activation_hash

Now you have your SQL statement which you can prepare. To bind the values, if I understand your class correctly, just use a loop which looks like this:

foreach ($ins_array as $field => $item) {
    $PDO->bind(':'.$field,$item);
}

Now you can execute the statement.

Summary. Basically with my approach a single INSERT statement is reduced to this:

$ins_array = array(
    "user_name"               => "your_user",
    "user_password_hash"      => "your_pw_hash",
    "user_email"              => "[e-mail]",
    "user_activation_hash"    => "your_other_Hash"
);

myInsertSingle($PDO, 'your_table', $ins_array);

If this looks just like another piece of overhead, keep in mind, I use these functions in other SQL statements like SELECT, UPDATE, DELETE and so on.

这篇关于更有效地绑定大插入或更新的方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！