php 使用 SQL 查询将值数组插入到数据库中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21612933/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
insert array of values into a database using SQL query?
提问by Giovanni
I have a PHP array of the column names in my SQL table. I also have an array of the values I want to assign to these columns. How do I put this in an SQL query. At present im writing out each column title like so:
我的 SQL 表中有一个列名的 PHP 数组。我还有一个要分配给这些列的值的数组。如何将其放入 SQL 查询中。目前我像这样写出每列标题：
$query = "INSERT INTO `first_page_data`(`a`, `b`, `c`, `d`, `e`, `f`, `g`, `h`)
VALUES ('','','','','','','','')";
But there must be a way of just using the arrays?
但是必须有一种方法可以只使用数组吗？
As an extra, is there a way of defining key/value pairs to keep the two pairs of data together, and then using these to insert into the database? how is this formatted in the SQL query?
另外，有没有办法定义键/值对来将两对数据保持在一起，然后使用它们插入数据库？这是如何在 SQL 查询中格式化的？
回答by Slavko
Here's another similar solution.
这是另一个类似的解决方案。
Code:
代码：
<?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() );
}
}
?>
Example usage:
用法示例：
<?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
?>
回答by Mahmoud.Eskandari
//Insert ( var , Array )
function insert($table, $inserts) {
$values = array_map('mysql_real_escape_string', array_values($inserts));
$keys = array_keys($inserts);
return mysql_query('INSERT INTO `'.$table.'` (`'.implode('`,`', $keys).'`) VALUES (\''.implode('\',\'', $values).'\')');
}
/* Samples
insert('first_page_data', array(
'a' => 'Just Persian Gulf',
'b' => 'DB9',
'c' => '2009'
));
*/
it's good And Rapid!
它很好而且很快！
回答by dsimer
Using PHP:
使用 PHP：
Getting your values into an array as $key => $value depends on the situation, but manually it would happen like so:
将您的值作为 $key => $value 放入数组取决于情况，但手动它会像这样发生：
$array = array(`a` => '',`b` => '', ...and so on); //I am assuming that $ is not a variable indicator since it is inside single quotes.
There are a variety of array functionsthat can help you if you have existing arrays that you would rather manipulate to create the final array.
如果您拥有想要操作以创建最终数组的现有数组，则有多种数组函数可以为您提供帮助。
Once you have it, however:
但是，一旦拥有它：
$query = 'INSTERT INTO `first_page_data` (';
foreach ($array as $key => $value) {
$query .= '`' . $key . '`';
}
$query .= ') VALUES (';
foreach ($array as $value) {
$query .= '`' . $value . '`';
}
$query .= ')';
The code runs a foreach on the array twice, once to get the key and append it to the appropriate part of the string, and the other to add the corresponding values.
代码在数组上运行了两次 foreach，一次是获取键并将其附加到字符串的适当部分，另一次是添加相应的值。
回答by bug
Try serialize() before the INSERT and unserialize() to get the array after a SELECT.
在 INSERT 和 unserialize() 之前尝试 serialize() 以在 SELECT 之后获取数组。
You need only one field to insert all the data.
您只需要一个字段即可插入所有数据。
http://ca1.php.net/manual/fr/function.serialize.php
http://ca1.php.net/manual/fr/function.unserialize.php
http://ca1.php.net/manual/fr/function.serialize.php
http://ca1.php.net/manual/fr/function.unserialize.php
回答by Robert Sinclair
# Insert this array
$arr = array("sounds" => "one", "sound" => "two", "big" => "blue");
function addQuotes($str){
return "'$str'";
}
# Surround values by quotes
foreach ($arr as $key => &$value) {
$value = addQuotes($value);
}
# Build the column
$columns = implode(",", array_keys($arr));
# Build the values
$values = implode(",", array_values($arr));
# Build the insert query
$query = "INSERT INTO table (".$columns.") VALUES (".$values.")";
echo $query;
// returns
INSERT INTO table (sounds,sound,big) VALUES ('one','two','blue')
INSERT INTO table (sounds,sound,big) VALUES ('one','two','blue')
