[RESOLVED] UPDATE table from array with loop

dabdura

Hello all
I'm already pulling my hair out on this

I want to UPDATE column from array, i've tried the 2 codes below but it inserts the last entry from array in all rows

// code 1
foreach ($keywords as $key){

mysql_query("UPDATE table_name SET column_name='".$key."' ");

}

//code 2
for ($i = 0; $i <= 29; $i++) {

mysql_query("UPDATE table_name SET column_name='".$keywords[$i]."'");

}

if I put REPLACE INTO instead of UPDATE it inserts all entries from array correctly but the problem with REPLACE INTO is that it adds new rows on each run.

can anybody explain why does UPDATE insert only last entry from array in all rows?

what i'm trying to accomplish is that I want to update a column every 15 minutes with new entries from array

thanks in advance

bradgrafelman

dabdura wrote:
can anybody explain why does UPDATE insert only last entry from array in all rows?

Because that's exactly what you're telling MySQL to do in both versions of your code above.

For every keyword in the array, you're executing an UPDATE query without a WHERE clause, meaning you're telling MySQL to set that column's value equal to (whatever) for every row. And you keep doing that for the entire array, meaning every iteration through the loop is just overwriting what you did in the previous iteration.

dabdura

thanks for help

I figured it out just before reading your reply, but I was doing it wrong way(I think).
I was building an array manually like: $id_arr = array(1, 2, 3, 4, 5, 6, etc. and then added WHERE id='".$id_arr[$i]."' in query

I got better idea while reading your comment

// code 1 
foreach ($keywords as $number => $key){ 

mysql_query("UPDATE table_name SET column_name='".$key."' WHERE id='".number_format($number+1)."' "); 

} 

//code 2 
for ($i = 0; $i <= 29; $i++) { 

mysql_query("UPDATE table_name SET column_name='".$keywords[$i]."' WHERE id='".number_format($i+1)."' "); 

}

Is there anything wrong with these???
Which one of these would be more efficient???