如何有效地将多个表单值插入到MySQL表中 - How can I efficiently insert multiple form values into my MySQL table?

我有一长串表单值需要插入数据库。有没有一种有效的方法可以将所有这些值插入数据库中的单独列中，而不是有一长串庞大的条件语句？

这是我的代码：

PHP:

// I've removed most of them to simplify demonstration
$title = isset($_POST['title']) ? $_POST['title'] : "";
$name = isset($_POST['name']) ? $_POST['name'] : "";
$description = isset($_POST['description']) ? $_POST['description'] : "";
$how_hear = isset($_POST['how_hear']) ? $_POST['how_hear'] : "";
// I have to do this for every single form element:
if (!empty($title)) {
    DB::query('INSERT INTO `table` (`title`) VALUES (?);', array($title));
}

$usrData = array(
    "user" => "hello",
    "pass" => "pass1",
    "mail" => "test@email.com"
);
$cols = "";
$vals = "";
foreach($usrData as $col => $val) {
    $col = mysql_real_escape_string($col);
    $val = mysql_real_escape_string($val);
    $cols.= "`{$col}`, ";
    $vals.= "'{$val}', ";
}
$query = "INSERT INTO `myTable` (". rtrim($cols, ", ") .") VALUES (". rtrim($vals, ", ") .");";   
mysql_query($query);

这样尝试

$usrData = array(
    array(
        "user" => "hello",
        "pass" => "pass1",
        "mail" => "test@email.com"
    ),
    array(
        "user" => "user2",
        "pass" => "pass2",
        "mail" => "user2@email.com"
    )
);
$cols = "";
$bindQuery = "";
$i = 0;
foreach($usrData as $udata) {
    $vals = "";
    foreach($udata as $col => $val){    
        $col = mysql_real_escape_string($col);
        $val = mysql_real_escape_string($val);
        $vals.= "'{$val}', ";
        if($i == 0)
            $cols.= "`{$col}`, ";       
    }
    $bindQuery.= "(" . rtrim($vals, ", ") . "), ";
    $i++;
}
echo $query = "INSERT INTO `myTable` (". rtrim($cols, ", ") .") VALUES ". rtrim($bindQuery, ", ") .";";   
mysql_query($query);

假设

数据库文件名和表单字段名不相同
所有数据都应插入数据库中的同一记录中
即使一个或多个字段为空，也应将所有数据输入数据库

示例发布数据

下面的代码在输出示例查询时使用以下示例数据（就好像它是通过POST方法从站点发送的一样）。

$_POST["title"] = "Mr.";
$_POST["name"] = "Joe Bloggs";
$_POST["description"] = "Whatever it is this field is for, presumably some text...";
$_POST["how_hear"] = "google / search engine";

代码

$column_names = array( 
// Structure:           db-field-name   => form-field-name                        
                        "title"         => "title",
                        "customer_name" => "name",          // Example: Database field name would be "customer_name" and the form field name would be "name"
                        "content"       => "description",
                        "pointer"       => "how_hear"
                    ); 
$insert_columns = "";
$insert_values  = "";
foreach($column_names as $db_name=>$ws_name){ // Loop through fields specified in $column_names
    $value           = empty($_POST[$ws_name]) ? '' : $_POST[$ws_name];
    $insert_columns .= ", `".$db_name."`"; // Backticks to denote field names
    $insert_values  .= ", '".$value."'";   // Single quotes to denote values
}
// Generate the query
// substr function removes ", " from start of strings
$insert_query = "INSERT INTO `table_name` (".substr($insert_columns, 2).") VALUES (".substr($insert_values, 2).")";

echo $insert_query; // Show example output
// INSERT INTO `table_name` (`title`, `customer_name`, `content`, `pointer`) VALUES ('Mr.', 'Joe Bloggs', 'Whatever it is this field is for, presumably some text...', 'google / search engine')

快速点数

短期if状态：

$value = empty($_POST[$ws_name]) ? '' : $_POST[$ws_name];
// Is equivalent to:
if( empty($_POST[$ws_name]) ) {
    $value = "";
}
else {
    $value = $_POST[$ws_name];
}

上面的代码对清除输入没有任何作用，所以不要忘记清除数据等。
您可以根据需要向$column_names数组中添加任意数量的字段，并且顺序不相关。
我已经使用echo来显示代码生成的查询作为示例。你显然不会这么做。。。相反，您将把查询$insert_query传递给数据库连接。类似于：
```
DB::query($insert_query);
```
当然，这可能会（或者更确切地说：会）根据您连接到数据库的方式而改变。。。