I have several rows in a table that have 5 fields that are populated with strings. pic1, pic2, pic3, pic4, pic5

I want to read each of the fields mentioned from the each row, implode into and array with comma separator and write it back to a different field in the same row it came from. It's not working like it should

`

        $Link = mysqli_connect ($Host, $User, $Password, $DBName);
         
         $query = 'SELECT pic1,pic2,pic3,pic4,pic5 FROM tbl_reports';
         
         $Result = mysqli_query ($Link, $query);

         while ($row = mysqli_fetch_array($Result))
                                                     
                        {
  
                       $array = array();
                       $array = array($row);
                       $array1 = implode(',', $array[0]);
                       $query1 ="UPDATE tbl_reports SET pics = '$array1'";
                       $Result1 = mysqli_query ($Link, $query1);
                       

                          }`

    always always always check the result of mysqli_connect and mysqli_query before you try to use them for anything. They could produce an error and return boolean values.

      What I see happening here is that if the table has five rows the UPDATE will update every row of the table five times, because there's nothing in the command to restrict which rows get updated with what values. Is that what you mean by "not working as it should"?

      Fixing that UPDATE though would make everything else look silly, because it means a lot of repeated work for no benefit. Instead, the SELECT and following loop can all be replaced by a single UPDATE that constructs the new value for the pics field from the others (in other words, have the database do the work); this would not only be a lot faster but avoid the problem in the previous paragraph.

      On a broader view though, I strongly suspect that the problem you're actually trying to solve has a completely different and more useful solution than the one you're trying to implement. I'm only guessing at why you're doing this in the first place, but it may be something that's better handled by adding a new table.

        Well this a 1 off situation. These are filenames for images. I have many rows that have these fields. I want to combine all these fields into 1 new field still in same record so I can read and write this data easier via array, etc. Once this runs once like it should, I'm done with it. Just don't know where to go from what I posted earlier.

        sks1024
        So maybe the other two paragraphs of my post are more relevant (hint: SQL can do string concatenation).

          Seems unnecessary denormalization of your data, since if you ever need those columns in an "array", you could just do so at select time:

          select whatever, something_else, concat_ws(',', pic1, pic2, pic3, pic4, pic5) as pics
          from the_table
          

          Then if you need to treat it as a PHP array, just implode it:

          $pics = implode(',', $row['pics']);
          

            And if it's for a one-off, I don't see the benefit of taking information out of the database and putting it back in again: if you're doing it for anything you'll only be taking it out of the database again.

              8 days later

              I'll be doing away with all of the ''pic' fields and consolidating all data to one field once this is complete.

                That seems like it's a step backwards. Why did you make them separate fields to begin with, and how will combining them into one help?

                Weedpacket Took over site where it was done originally. Combining them will help when I output them. I can ''explode' the field and output each of them where needed instead of referencing each field separately.

                `

                         while ($row = mysqli_fetch_array($result) )
                            {
                                $pics = $row['pics'];
                            }
                                         
                                 $pics_array = explode(',', $pics);
                
                                 foreach(array_keys($pics_array) as $key) 
                							{
                				print"
                				      <li>
                						<a class=thumb name=pic href=/thumb/phpThumb.php?w=800&src=/upload_pics/".$pics_array[$key].">
                						<img src=/thumb/phpThumb.php?w=100&src=/upload_pics/".$pics_array[$key]." />
                						</a>
                                                      </li>";
                                                       }

                `

                See, at that point I'd be thinking about storing them in a separate table.

                  sks1024 Combining them will help when I output them. I can ''explode' the field and output each of them where needed instead of referencing each field separately.

                  Don't do that. As Weedpacket suggested, put the data into a separate table and pull it from there. Do some reading on database normalization - I haven't read completely through this page but skimming it, it seems solid enough at least on the concepts - https://www.edureka.co/blog/normalization-in-sql/.

                    Write a Reply...