1

This is driving me potty so please help.

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

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

Not exactly what I want.

Can someone please showing what I am doing wrong.

Thanks in advance.

Eric Petroelje
  • 59,820
  • 9
  • 127
  • 177
jamjam
  • 23
  • 1
  • 3
  • 8
  • 2
    If your `where` condition is **static**, aren't you gonna overwrite the same row/rows? – Nishant Jan 27 '11 at 16:11
  • 1
    In your example, it would update the same rows over and over, so at the end the ids would be 3,3,3. Can you post your actual code? – Eric Petroelje Jan 27 '11 at 16:12
  • Something is missing. If all lines have something in the column column all lines should end up with ID 3. Is this the exact code? Can you list the table rows before and after? – David Mårtensson Jan 27 '11 at 16:45

3 Answers3

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.

EDIT: Answering comment

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

$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());
}
  • Wow what a response. In the real code I only change the name of the field not much different from this. I have just removed the where clause all together and as you said it update all rows with the same value. So how can I re-write the whole thing so it does not update all rows with the same value? – jamjam Jan 27 '11 at 16:22
2

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.

Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
0

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.

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:

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:

mysql> update foo set id=(@ct:=@ct+1) order by name
john personna
  • 442
  • 3
  • 8
  • Thanks for trying to help me guys. I very confused I am going to have to think more about what I actually want to with is query. I will report back soon. Thanks again – jamjam Jan 27 '11 at 16:42