Help to UPDATE table with LOOP from query

sks1024

I have a simple script that queries db and puts results in am array with commas between each element. Works great. What I want to do with this is UPDATE each row that this array was made from by INSERT the array output in another empty field in the same row that the array was made from. It seems to only write the array output from the last row to this field.

pic1, pic2, pic3, pic4, pic5 - makes array called $picarray which is imploded with commas to array called $output

$query = "SELECT * FROM test1"; 

$result = mysql_db_query ($DBName, $query, $Link); 
$total = mysql_num_rows($result);


while($row = mysql_fetch_assoc($result)) {
$picarray = array("$row[pic1]","$row[pic2]","$row[pic3]","$row[pic4]","$row[pic5]");


$output = implode(',', array_filter($picarray));


//Here is what I am unsure of how to do.  Wants to overwrite each time

$query1 = "UPDATE test1 SET pics1 = '$output';"; 
$result1 = mysql_db_query ($DBName, $query1, $Link); 
}

sfullman

you are not specifying the unique key of the row, instead of:

$query1 = "UPDATE test1 SET pics1 = '$output';";

you need

$query1 = "UPDATE test1 SET pics1 = '$output' WHERE id='" . $row['id']."';";

Again, I've never once in my career ever done something that obvious 😉 Cheers

anoopmail

$result = mysql_db_query ($DBName, $query, $Link);
while($row = mysql_fetch_assoc($result)) {
$picarray = array("$row[pic1]","$row[pic2]","$row[pic3]","$row[pic4]","$row[pic5]");


$output = implode(',', array_filter($picarray));


//Here is what I am unsure of how to do.  Wants to overwrite each time
$pic1 = $row[pic1] ;

$query1 = "UPDATE test1 SET pics1 = '$output' WHERE pic1='$pic1';";
$result1 = mysql_db_query ($DBName, $query1, $Link);
}

bradgrafelman

My suggestion would be to first normalize your DB schema so that you don't have these numbered columns. Storing multiple values (e.g. imploding an array into a comma-separated list) in a single column (a "multi-valued" column) violates the First Normal Form of database design.

EDIT: Note one of the benefits of database normalization is that it makes your data easier to work with in the DB. We'd have to know more about your current DB schema (such as showing us the results of a 'SHOW CREATE TABLE' query) in order to make a suggestion as to how it should be normalized, though.