编写SQL语句以推送此数组的数据行的正确方法是什么？

I am trying to push array data to a MySQL database using foreach(). I'm quite a novice at PHP & mysql, so been working through parsing this data to PHP from an Ajax script, now just need to manipulate the data.

我正在尝试使用foreach()将数组数据推送到MySQL数据库。我是PHP和mysql的新手,因此一直在努力将这些数据从Ajax脚本解析为PHP,现在只需要操作数据。

What is the correct syntax to push this data into SQL so that each dataset in the array goes to a seperate row?

将此数据推送到SQL中的正确语法是什么,以便数组中的每个数据集都进入单独的行?

i am trying to use foreach, but the complexity is that the array itself can change in size and the second complexity is that the data itself may be refreshed (i.e same id new values on a new day), so I want to build in some intelligence to update info not just append; and also to backup old data to another table.

我正在尝试使用foreach,但复杂性是数组本身可以改变大小,第二个复杂性是数据本身可以刷新(即在新的一天同样id新值),所以我想构建一些智能更新信息不仅仅是追加;并将旧数据备份到另一个表。

is this syntax correct?

这种语法是否正确?

$sql = "INSERT INTO table1 (col1, col2) VALUES ";
foreach($rows as $i=>$row) {
    if ($i>0) {
        $sql .= sprintf(",(%s,%s)", $row["col1_value"], $row["col2_value"]);
    } else {
        $sql .= sprintf("(%s,%s)", $row["col1_value"], $row["col2_value"]);
    }
}
mysql_query($sql);

The data in the array is as follows - this is only part of a multidimensional array, but I have figured out how to manipulate the rest of the array to my needs.

数组中的数据如下 - 这只是多维数组的一部分,但我已经想出如何根据我的需要操作数组的其余部分。

The only other thing I need to do is figure out a way to take the coords field and manipulate it as follows

我唯一需要做的就是找出一种方法来获取coords字段并按如下方式操作它

Extract x & y data from coords Multiple x & y by 600/386 Reverse x & y and take the first digit of each coordinate to create a new value y[1]x[1].

从坐标中提取x和y数据多次x和y乘以600/386反向x和y并取每个坐标的第一个数字以创建新值y [1] x [1]。

For this I tried just on the first data set, as follows, but I am inexperienced in data handling on PHP. Pretty sure it is wrong.

为此,我尝试了第一个数据集,如下所示,但我对PHP的数据处理缺乏经验。很确定这是错的。

$testcoords = $_POST['data'][0]['coords'];
list($x,$y) = explode(“:”,str_replace(“’”,“”,$testcoords));
$xtrans = number_format($x*600/386,$decimals=null);
$ytrans = number_format($y*600/386,$decimals=null);
$cont = “C”.$ytrans[0].$xtrans[0]

So to summarize, three questions

总结一下,三个问题

How do I transfer data into a table, with rows for each individual dataset in the [data] array?

如何将数据传输到表中,[data]数组中的每个数据集都有行?

How do overwrite and archive any existing values in the table rather than simply concatenating?

如何覆盖和归档表中的任何现有值而不是简单地连接?

How do I manipulate one specific string to return a custom variable as defined above?

如何操作一个特定的字符串以返回上面定义的自定义变量?

)

1 个解决方案

#1

The problems I see:

我看到的问题:

foreach($rows as $i=>$row) {
      $sql = "INSERT INTO table1 (col1, col2) VALUES ";  //reinitialize here.
      if ($i>0) {  //Why have the if statement, the results of the conditions are the same.
        $sql .= sprintf("('%s','%s')", $row["col1_value"], $row["col2_value"]);  //add quotation marks for string values, remove the comma.
      } else {
        $sql .= sprintf("(%s,%s)", $row["col1_value"], $row["col2_value"]);
      }
      mysql_query($sql); //execute the statement here.
   }

Your other questions:

你的其他问题:

How do overwrite and archive any existing values in the table rather than simply concatenating?

如何覆盖和归档表中的任何现有值而不是简单地连接?

To overwrite you do an update statement, the MySql website gives hints to syntax.

要覆盖您执行更新语句,MySql网站会提供语法提示。

To archive, you will need to create an archive table that is pretty much a duplicate of the table being archived and insert/update that table.

要进行归档,您需要创建一个归档表,该表几乎与正在归档的表重复并插入/更新该表。

In this context, I am not sure what you mean by concatenation, it does not really apply to databases that are normalized.

在这种情况下,我不确定串联是什么意思,它并不真正适用于规范化的数据库。

How do I manipulate one specific string to return a custom variable as defined above?

如何操作一个特定的字符串以返回上面定义的自定义变量?

Ah gonna skip that one, it is a lot to ask in one post.

啊,要跳过那一个,在一篇文章中要问很多。

#1