php - 无法使用 PHP 将大数组传递给 mySQL 数据库

Question

我目前有一个相对较大的 HTML 表单(100 多个字段)。我想从该表单中获取数据并在使用点击提交时将其上传到 mySQL 数据库。我已经在下面创建了 PHP 代码，并且一直在慢慢添加字段并进行测试以查看连接是否成功。一切都通过 $skilled_nursing 进行，但是当我添加下一组值时，我不再成功创建数据库条目。我的所有 echo 命令都显示出来，我的错误日志中没有出现故障，但数据库中没有收到数据。

谁能看出哪里出了问题？我已经多次检查拼写错误，但我没有看到任何拼写错误。我想知道我是否以某种方式连接超时，或者我是否试图将太多值粘贴到执行命令中。

<?php

echo 'started ok';
// configuration
$dbtype     = "mysql";
$dbhost     = "localhost";
$dbname     = "dbname";
$dbuser     = "dbuser";
$dbpass     = "userpass";
echo 'variables assigned ok';
// database connection
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);
echo 'connection established';
// new data
$facility_name = $_POST['facility_name'];
$facility_street = $_POST['facility_street'];
$facility_county = $_POST['facility_county'];
$facility_city = $_POST['facility_city'];
$facility_state = $_POST['facility_state'];
$facility_zipcode = $_POST['facility_zipcode'];
$facility_phone = $_POST['facility_phone'];
$facility_fax = $_POST['facility_fax'];
$facility_licensetype = $_POST['facility_licensetype'];
$facility_licensenumber = $_POST['facility_licensenumber'];
$facility_email = $_POST['facility_email'];
$facility_administrator = $_POST['facility_administrator'];
$skilled_nursing = $_POST['skilled_nursing'];
$independent_living = $_POST['independent_living'];
$assisted_living = $_POST['assisted_living'];
$memory_care = $_POST['memory_care'];
$facility_type_other = $_POST['facility_type_other'];
$care_ratio = $_POST['care_ratio'];
$nurse_ratio = $_POST['nurse_ratio'];



// query
$sql = "INSERT INTO Facilities (facility_name, facility_street, facility_county, facility_city, facility_state, facility_zipcode, facility_phone, facility_fax, facility_licensetype, facility_licensenumber, facility_email, facility_administrator, skilled_nursing, independent_living, assisted_living, memory_care, facility_type_other, care_ratio, nurse_ratio) VALUES (:facility_name, :facility_street, :facility_county, :facility_city, :facility_state, :facility_zipcode, :facility_phone, :facility_fax, :facility_licensetype, :facility_licensenumber, :facility_email, :facility_administrator, :skilled_nursing, :independent_living, :assisted_living, :memory_care, :facility_type_other, :care_ratio, :nurse_ratio)";
$q = $conn->prepare($sql);
$q->execute(array(':facility_state'=>$facility_name,
':facility_street'=>$facility_street,
':facility_county'=>$facility_county,
':facility_city'=>$facility_city,
':facility_state'=>$facility_state,
':facility_name'=>$facility_name,
':facility_zipcode'=>$facility_zipcode,
':facility_phone'=>$facility_phone,
':facility_fax'=>$facility_fax,
':facility_licensetype'=>$facility_licensetype,
':facility_licensenumber'=>$facility_licensenumber,
':facility_email'=>$facility_email,
':facility_administrator'=>$facility_administrator,
':skilled_nursing'=>$skilled_nursing,
':independent_living'=>$independent_living,
':assisted_living'=>$assisted_living,
':memory_care'=>$memory_care,
':facility_type_other'=>$facility_type_other,
':care_ratio'=>$care_ratio,
':nurse_ratio'=>$nurse_ratio));

echo 'query parsed';

?>

Answer 1

这并不能完全回答您的代码出了什么问题，但它可能有助于解决问题。

我会做一些不同的事情。你说你有很多领域。您的代码可能会变得很长且重复。由于看起来您的表单字段名称已经与您的表格列对应，我会做更多类似的事情(未测试):

// get a list of column names that exist in the table
$sql = "SELECT column_name FROM information_schema.columns WHERE table_name = 'Facilities'";
$q = $conn->prepare($sql);
$q->execute();
$columns = $q->fetchAll(PDO::FETCH_COLUMN, 0);

$cols = array();
foreach ($_POST as $key=>$value)
{
    // if a field is passed in that doesn't exist in the table, remove it
    if (!in_array($key, $columns)) {
        unset($_POST[$key]);
    }
}

$cols = array_keys($_POST);
$sql = "INSERT INTO Facilities(". implode(", ", $cols) .") VALUES (:". implode(", :", $cols) .")";
$q = $conn->prepare($sql);
array_walk($_POST, "addColons");
$q->execute($_POST);

function addColons($value, &$key)
{
    $key = ":{$key}";
}

这样，您可以拥有 10、100 或 1000 个字段，并且此代码根本不需要更改。您还可以减少出现拼写错误的机会，因为只有一个地方指定了列名。您不必担心列名上的 SQL 注入(inject)，因为您会检查以确保该列在允许它用于查询之前存在。

当然，这确实假设通过 $_POST 传入的所有字段都与表中的列名相对应。如果不是这种情况，最简单的方法可能是将那些不是列的特定字段值存储在单独的变量中，并从 $_POST 中 unset() 它们数组。