带有列数组的MySQL INSERT的语法

问题描述：

I'm new to PHP and MySQL query construction. I have a processor for a large form. A few fields are required, most fields are user optional. In my case, the HTML ids and the MySQL column names are identical. I've found tutorials about using arrays to convert $_POST into the fields and values for INSERT INTO, but I can't get them working - after many hours. I've stepped back to make a very simple INSERT using arrays and variables, but I'm still stumped. The following line works and INSERTs 5 items into a database with over 100 columns. The first 4 items are strings, the 5th item, monthlyRental is an integer.

$query = "INSERT INTO `$table` (country, stateProvince, city3, city3Geocode, monthlyRental) VALUES ( '$country', '$stateProvince', '$city3', '$city3Geocode', '$monthlyRental')";

When I make an array for the fields and use it, as follows:

$colsx = array('country,', 'stateProvince,', 'city3,', 'city3Geocode,', 'monthlyRental');
$query = "INSERT INTO `$table` ('$colsx') VALUES ( '$country', '$stateProvince', '$city3', '$city3Geocode', '$monthlyRental')";

I get a MySQL error - check the manual that corresponds to your MySQL server version for the right syntax to use near ''Array') VALUES ( 'US', 'New York', 'Fairport, Monroe County, New York', '(43.09)' at line 1. I get this error whether the array items have commas inside the single quotes or not. I've done a lot of reading and tried many combinations and I can't get it. I want to see the proper syntax on a small scale before I go back to foreach expressions to process $_POST and both the fields and values are arrays. And yes, I know I should use mysql_real_escape_string, but that is an easy later step in the foreach. Lastly, some clues about the syntax for an array of values would be helpful, particularly if it is different from the fields array. I know I need to add a null as the first array item to trigger the MySQL autoincrement id. What else?

I'm pretty new, so please be specific.

我是PHP和MySQL查询构建的新手。我有一个大型的处理器。需要一些字段，大多数字段是用户可选的。在我的例子中，HTML ID和MySQL列名称是相同的。我找到了关于使用数组将$ _POST转换为INSERT INTO的字段和值的教程，但是我不能让它们工作 - 几个小时之后。我已经退回到使用数组和变量进行非常简单的INSERT，但我仍然难过。以下行工作并将5个项目插入到包含100多列的数据库中。前4项是字符串，第5项，monthlyRental是整数。 p>

  $ query =“INSERT INTO` $ table`（country，stateProvince，city3，city3Geocode，monthlyRental  ）VALUES（'$ country'，'$ stateProvince'，'$ city3'，'$ city3Geocode'，'$ monthlyRental'）“; 
  code>  pre> 
 
 当我制作时 字段的数组并使用它，如下所示： p> 
 
 
  $ colsx = array（'country，'，'stateProvince，'，'city3，'，'city3Geocode，'  ，'monthlyRental'）; 
 $ query =“INSERT INTO` $ table`（'$ colsx'）VALUES（'$ country'，'$ stateProvince'，'$ city3'，'$ city3Geocode'，'$ monthlyRental'  ）“; 
  code>  pre> 
 
 我收到MySQL错误 - 检查与MySQL服务器版本对应的手册，以便在''Array'附近使用正确的语法）VALUES（  'US'，'New York'，'Fairport，Monroe County，New York'，'（43.09）'在第1行。我得到这个错误，数组项是否在单引号内有逗号。我做了一个 很多阅读和尝试了许多组合，我无法得到它。我想看到 在我回到foreach表达式处理$ _POST并且字段和值都是数组之前，他是小规模的正确语法。 是的，我知道我应该使用mysql_real_escape_string，但这在foreach中是一个简单的后续步骤。 最后，关于值数组的语法的一些线索将是有帮助的，特别是如果它与fields数组不同。 我知道我需要添加一个null作为第一个数组项来触发MySQL自动增量id。 还有什么？ p> 
 
 
我很新，所以请具体说明。 p> 
  div>

答

$query = "INSERT INTO `$table` ('$colsx') etc...

isn't going to work. $colsx is an array, so what you're going to end up producing is literally

$query = "INSERT INTO `sometable` ('Array')
                                    ^^^^^---yes, it'll literally say "Array"

You'll have to preprocess the array into a string before doing this, e.g.

$colsx = array(...);
$col_string = implode(',', $colsx);
$query = "INSERT INTO `$table` ($col_string) etc...";

带有列数组的MySQL INSERT的语法

相关推荐