php - 使用 SQL 查询将值数组插入数据库？

Question

我的 SQL 表中有一个列名的 PHP 数组。我还有一组要分配给这些列的值。我如何将其放入 SQL 查询中。目前我是这样写出每一列的标题的:

$query = "INSERT INTO `first_page_data`(`a`, `b`, `c`, `d`, `e`, `f`, `g`, `h`) 
VALUES ('$1','$2','$3','$4','$5','$6','$7','$8')";

但一定有一种方法可以只使用数组吗？

另外，有没有一种方法可以定义键/值对以将两对数据保持在一起，然后使用它们插入到数据库中？这在 SQL 查询中是如何格式化的？

Answer 1

这是另一个类似的解决方案。

代码:

<?php
function mysql_insert_array($table, $data, $exclude = array()) {

    $fields = $values = array();

    if( !is_array($exclude) ) $exclude = array($exclude);

    foreach( array_keys($data) as $key ) {
        if( !in_array($key, $exclude) ) {
            $fields[] = "`$key`";
            $values[] = "'" . mysql_real_escape_string($data[$key]) . "'";
        }
    }

    $fields = implode(",", $fields);
    $values = implode(",", $values);

    if( mysql_query("INSERT INTO `$table` ($fields) VALUES ($values)") ) {
        return array( "mysql_error" => false,
                      "mysql_insert_id" => mysql_insert_id(),
                      "mysql_affected_rows" => mysql_affected_rows(),
                      "mysql_info" => mysql_info()
                    );
    } else {
        return array( "mysql_error" => mysql_error() );
    }

}
?>

示例用法:

<?php

// Open database here

// Let's pretend these values were passed by a form
$_POST['name'] = "Bob Marley";
$_POST['country'] = "Jamaica";
$_POST['music'] = "Reggae";
$_POST['submit'] = "Submit";

// Insert all the values of $_POST into the database table `artists`, except
// for $_POST['submit'].  Remember, field names are determined by array keys!
$result = mysql_insert_array("artists", $_POST, "submit");

// Results
if( $result['mysql_error'] ) {
    echo "Query Failed: " . $result['mysql_error'];
} else {
    echo "Query Succeeded! <br />";
    echo "<pre>";
    print_r($result);
    echo "</pre>";
}

// Close database

?>

来源:Inserting An Array into a MySQL Database Table