php添加到数据库表,使用PHP将可变数量的字段添加到数据库表

请注意,在下面的代码中,SANITIZE_ME是一个占位符,用于诸如

mysqli::real_escape_string之类的方法,或者适用于您的情况的任何方法.您需要根据需要调整此答案.

function generateSQLStatement($_POST) {

$fieldNames = "";

$values = "";

foreach ($_POST as $field => $value) {

if (!empty($value)) {

if (!empty($fieldNames)) {

$fieldNames .= ',';

$values .= ',';

}

$fieldNames .= SANITIZE_ME($field);

$values .= "'" . SANITIZE_ME($value) . "'";

}

}

return "($fieldNames) VALUES ($values)";

}

这种方法只使用一个循环,因此速度更快.但是,您可能希望根据预定义的可接受字段数组验证字段名称,以防有人编辑发布到您脚本的表单并放入无效的字段名称.

编辑

可以使用更通用的方法来创建一个实用程序函数,您可以在整个应用程序中轻松地重用其他表：

这个批次可以进入一些通用的包含文件：

// An array whose keys are valid table names and

// whose values are arrays of valid field names

// within the table named in the key

$acceptableFields = array(

'contacts' => array(

// valid fields in the 'contacts' table

'name', 'address' //...

)

// ... other mappings, if desired

);

function isValidField($table, $field) {

if (!isset($acceptableFields[$table]))

return false;

return in_array($field, $acceptableFields[$table]);

// Note that in_array is case-sensitive, so you may want

// to just manually loop through $acceptableFields[$table]

// and compare strings yourself.

}

function insertData($table, array $fieldValuesMap, mysqli $mysqli) {

// First, some self-explanatory validation:

if ($table === null)

throw new InvalidArgumentException('$table cannot be null');

if (!is_string($table))

throw new InvalidArgumentException('$table must be a String');

if (empty($table))

throw new InvalidArgumentException('$table cannot be an empty String');

if (!isset($acceptableFields[$table]))

throw new InvalidArgumentException("\"$table\" is an invalid table name");

$fieldNames = "";

$values = "";

foreach ($fieldValuesMap as $field => $value) {

// check the field name is valid for the given table

// and that the value is not empty. You may want to

// add a logging mechanism for invalid field names to

// help track bugs or even malicious use

if (isValidField($table, $field) && !empty($value)) {

// check to see whether there are any items in

// the lists already

if (!empty($fieldNames)) {

// yes, so add commas:

$fieldNames .= ',';

$values .= ',';

}

// no need to escape the field name as we have already

// checked that it is valid

$fieldNames .= $field;

// but we do need to escape the value

$values .= "'" . $mysqli->real_escape_string($value) . "'";

}

}

// check whether we've actually got anything to insert:

if (empty($fieldNames))

return NULL;

return $mysqli->query("INSERT INTO $table ($fieldNames) VALUES ($values)");

}

用于添加联系人的页面上的示例用法：

require_once "above.file"; // whatever it's called

if ($_POST) {

$mysqli = new MySQLi(/*...*/);

if (mysqli_connect_errno()) {

// handle connection error

} else {

// set your charset

$mysqli->set_charset("utf8"); // or whatever you use

$result = insertData('contacts', $_POST, $mysqli);

if ($result === NULL) {

// There was nothing to insert

} elseif ($result === FALSE) {

// An error occurred, handle it here

} else {

// Success! Use $mysqli->insert_id to get your new

// record's ID (if appropriate).

}

}

}

//

//==============================================

一点额外的工作,你最终得到灵活和可重复使用的东西.但就个人而言,我更喜欢更面向对象(主动记录)的方法.