問題描述

我有一個(gè)包含大約 25 個(gè)輸入字段的大表單.

I have a large form with about 25 input fields.

我正在嘗試將它們插入我的表格中，而我知道如何使用以下內(nèi)容的唯一方法...

Im trying to insert them into my table and the only way i know how is using the following...

$count = $dbh->exec("INSERT INTO directory(field1, field2) VALUES (':value1', ':value2')");

由于我有這么多帖子變量，有沒有比在我的查詢中輸入每個(gè)人更好的方法呢?

As I have so many post variables, is there a better way to do this than type each and everyone into my query?

推薦答案

動(dòng)態(tài)準(zhǔn)備查詢

您可以從 $_POST 數(shù)組動(dòng)態(tài)構(gòu)建查詢:

Dynamic prepared queries

You can build your query dynamically from $_POST array:

但是，永遠(yuǎn)不要相信用戶輸入，這意味著您不能相信 $_POST 中的數(shù)據(jù)將包含有效的列名.

But, NEVER trust user input, which means you cannot trust that data in $_POST will contain valid column names.

1.清理帖子數(shù)據(jù)

可以定義一個(gè)白名單列名數(shù)組$whitelist = array('field1', 'field2', ...)，然后使用:

You can define an array of whitelisted column names $whitelist = array('field1', 'field2', ...), and then use:

$data = array_intersect_key($_POST, array_flip($whitelist));

找到列入白名單的列和您的 $_POST 數(shù)組之間的交集.(感謝@BillKarwin)

to find the intersection between the whitelisted columns and your $_POST array. (Thanks @BillKarwin)

2.構(gòu)建查詢

private function buildInsertSql($data, $table) {
    $columns = "";  
    $holders = "";  
    foreach ($data as $column => $value) {  
       $columns .= ($columns == "") ? "" : ", ";  
       $columns .= $column;  
       $holders .= ($holders == "") ? "" : ", ";  
       $holders .= ":$column";  
    }  
    $sql = "INSERT INTO $table ($columns) VALUES ($holders)";  
    return $sql; 
}

這將為您提供以下形式的 SQL 語句:

This will give you a SQL statement of the form:

$sql = INSERT INTO directory (field1, field2) VALUES (:field1, :field2)

并準(zhǔn)備聲明:

$stmt = $dbh->prepare($sql);

3.綁定參數(shù)

然后您可以將參數(shù)動(dòng)態(tài)綁定到占位符:

You can then dynamically bind parameters to the placeholders:

foreach ($data as $placeholder => $value) {
    $stmt->bindValue(":$placeholder", $value);
 }

并執(zhí)行它:

$stmt->execute();

<小時(shí)>

更高級(jí)一點(diǎn)...

• 看看這個(gè)鏈接 綁定到相同的占位符有關(guān)如何使您的動(dòng)態(tài)準(zhǔn)備好的語句更加健壯的信息.
• 看看這個(gè)鏈接:綁定參數(shù)內(nèi)部循環(huán) 有關(guān)在循環(huán)中綁定參數(shù)與值的警告.

---

A little more advanced...

• Take a look at this link Binding to the same placeholder For information about how to make your dynamic prepared statement more robust.
• Take a look at this link: Bind Params Inside Loop For a caveat regarding binding paramaters vs values in a loop.

這篇關(guān)于使用 PDO 將大量變量插入表中的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！

【網(wǎng)站聲明】本站部分內(nèi)容來源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問題，如果有圖片或者內(nèi)容侵犯了您的權(quán)益，請聯(lián)系我們刪除處理，感謝您的支持！