php 如何通过mysql查询插入包含逗号的字符串？

How to insert a string which contains commas via a mysql query?

提问by user1011444

I have a table with 3 columns : id, food_name, and category. For example, I have $food_name = "Amaranth grain, cooked"(there is a comma), and category = "Cereals".

我有3列的表：id，food_name，和category。例如，我有$food_name = "Amaranth grain, cooked"（有一个逗号），和category = "Cereals"。

I can't insert the values and have the following error:

我无法插入值并出现以下错误：

Could not connect: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
      VALUES ('Amaranth grain, cooked', )' at line 1

My SQL query is :

我的 SQL 查询是：

$results1 = mysql_query("
  INSERT INTO " . $table . " (food_en, category)
  VALUES ('" . $food_name . "', '" . $category . "')"
)or die(mysql_error());

My guess is that the comma in "Amaranth grain, cooked" is posing a problem, but I can't solve it. I tried str_replace(',', '\,', $food_name)and mysql_real_escape_string()without success. I probably didn't use them right.

我的猜测是“苋菜籽，煮熟”中的逗号造成了问题，但我无法解决。我试过了str_replace(',', '\,', $food_name)，mysql_real_escape_string()但没有成功。我可能没有正确使用它们。

Thanks in advance.

提前致谢。

EDIT: I did echo $category, and it's not null nor empty (the value is 'Cereals').

编辑：我确实回显了 $category，它既不为空也不为空（值为“谷物”）。

When I print instead of using mysql_query, I get:

当我打印而不是使用 mysql_query 时，我得到：

INSERT INTO calories (food_en, category)
      VALUES ('Amaranth grain, cooked', 'Cereal Grains and Pasta')
      INSERT INTO carbohydrates (food_en, category)
      VALUES ('Amaranth grain, cooked', 'Cereal Grains and Pasta')
      INSERT INTO fats_and_fatty_acids (food_en, category)
      VALUES ('Amaranth grain, cooked', 'Cereal Grains and Pasta')
      INSERT INTO protein_and_amino_acids (food_en, category)
      VALUES ('Amaranth grain, cooked', 'Cereal Grains and Pasta')
      INSERT INTO vitamins (food_en, category)
      VALUES ('Amaranth grain, cooked', 'Cereal Grains and Pasta')
      INSERT INTO minerals (food_en, category)
      VALUES ('Amaranth grain, cooked', 'Cereal Grains and Pasta')
      INSERT INTO sterols (food_en, category)
      VALUES ('Amaranth grain, cooked', 'Cereal Grains and Pasta')
      INSERT INTO other (food_en, category)
      VALUES ('Amaranth grain, cooked', 'Cereal Grains and Pasta')Could not connect: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
      VALUES ('Amaranth grain, cooked', )' at line 1

(the different tables are just different tables I want the line to be inserted into)

（不同的表只是我希望将行插入的不同表）

回答by Nishanthi Grashia

There is no problem in your queries because of comma. Since you are inserting records to different tables, you need to separate each query with a query-separatoror query-delimiter.

由于逗号，您的查询没有问题。由于您将记录插入到不同的表中，因此您需要用query-separator或分隔每个查询query-delimiter。

INSERT INTO calories (food_en, category)
      VALUES ('Amaranth grain, cooked', 'Cereal Grains and Pasta'); <-- Notice semi colon at end of query

INSERT INTO carbohydrates (food_en, category)
      VALUES ('Amaranth grain, cooked', 'Cereal Grains and Pasta'); <-- Notice semi colon at end of query

INSERT INTO fats_and_fatty_acids (food_en, category)
      VALUES ('Amaranth grain, cooked', 'Cereal Grains and Pasta'); <-- Notice semi colon at end of query

In the example fiddle, I have separated each query with a semi-colonand the queries get inserted without issues.

在示例小提琴中，我用 a 分隔了每个查询，semi-colon并且查询被插入没有问题。

Refer Fiddle here: http://sqlfiddle.com/#!2/ec94bc/1

在这里参考小提琴：http://sqlfiddle.com/#!2/ec94bc/1

回答by EmmanuelG

As long as the string is wrapped in quotes (as you have here in single quotes) then you can have commas in your string. The actual error probably has to do with the fact that $category seems to be either empty string or null since it says:

只要字符串用引号括起来（就像这里的单引号一样），那么您的字符串中就可以有逗号。实际错误可能与 $category 似乎是空字符串或 null 的事实有关，因为它说：

VALUES ('Amaranth grain, cooked', )

Make sure you echo out the sql query to make sure it's all exactly as you would expect.

确保您回显 sql 查询以确保它完全符合您的预期。

回答by Chandan Sharma

You can use php function addslashes or mysql_real_escape_string to put the slashes before every character which have any meaning in mysql.

您可以使用 php 函数 addlashes 或 mysql_real_escape_string 将斜杠放在每个在 mysql 中有意义的字符之前。