使用Foreach使用Array更新MySQL表

This is driving me potty so please help.

这让我很便利所以请帮忙。

I am trying to update a Mysql table with an array.

我试图用数组更新Mysql表。

Something like this

像这样的东西

$a = array('1', '2', '3');

foreach($a as $id){

mysql_query("UPDATE table SET id = '$id' WHERE column = 'something'") or die(mysql_error());

}

So after the update the id column should have values 1, 2, 3 Instead it updates with 1, 1, 1

所以在更新之后,id列应该具有值1,2,3而是以1,1,1更新

Not exactly what I want.

不完全是我想要的。

Can someone please showing what I am doing wrong.

有人可以表明我做错了什么。

Thanks in advance.

提前致谢。

3 个解决方案

#1

Each of your update statements in the foreach are acting on the same row or set of rows each time. In your example, you use "where column = 'something'". If that doesn't change with each iteration of the foreach loop, you'll keep updating the same rows.

foreach中的每个update语句每次都在同一行或一组行上。在您的示例中,您使用“where column ='something'”。如果在foreach循环的每次迭代中都没有改变,那么你将继续更新相同的行。

#2

Do you change your where-statement in the real code? Now you are overwriting every row where column = 'something' which would means every row would be updated every time and end up with the same content.

你是否在实际代码中更改了where语句?现在你要覆盖column ='something'的每一行,这意味着每一行都会每次更新并最终得到相同的内容。

EDIT: Answering comment

编辑:回答评论

Well, you would need a non-static WHERE-statement for this. You could do something like the edit in my post...

那么,你需要一个非静态的WHERE语句。你可以做我的帖子中的编辑...

$a = array('1' => 'something1', '2' => 'something2', '3' => 'something3');

foreach($a as $id => $where){
    mysql_query("UPDATE table SET id = '$id' WHERE column = '$where'") or die(mysql_error());
}

#3

I don't see the 'where' condition changing in the loop. Each time you do "WHERE column = 'something'" it will match and replace ALL the rows, overwriting the ID from each previous update.

我没有看到循环中'where'条件发生变化。每次执行“WHERE column ='something'”时,它将匹配并替换所有行,覆盖每个先前更新的ID。

UPDATE:

Some of us wrote similar responses at the same time. I should have hit 'refresh' one more time before 'add'

我们中的一些人同时写了类似的回答。我应该在“添加”之前再次点击'刷新'

For what it's worth, if this is a one-time fix to get sequential ids on a table, you can do that with straight mysql:

对于它的价值,如果这是一次性修复以获得表上的顺序id,你可以用直接的mysql来做到这一点:

mysql> select * from foo;
+------+------+
| id   | name |
+------+------+
|    0 | aaa  |
|    0 | bbb  |
|    0 | ccc  |
|    0 | ddd  |
+------+------+
4 rows in set (0.00 sec)

mysql> set @ct=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update foo set id=(@ct:=@ct+1);
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select * from foo;
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
|    2 | bbb  |
|    3 | ccc  |
|    4 | ddd  |
+------+------+
4 rows in set (0.00 sec)

Use an 'order by' if you like, for instance:

如果您愿意,请使用'order by',例如:

mysql> update foo set id=(@ct:=@ct+1) order by name

#1

#2