How to update multiple rows with an array

PHP
1

I am trying to update a SQL table from a PHP array using foreach() .
I noticed that only the last array value is written to all table fields.
here is my Code.

$property_img[]=$image;
                 if (empty($property_img)){
                return null;}

             $count=Count($property_img);
           if(Count($property_img)===0)
                                            
            { var_dump($count);
           $property_img=$property_img[0];
                                                                                                     
         $sql2="UPDATE `property_img` set property_img=:property_img where property_img.pro_id=".$id;
      }else {
          $sql2="UPDATE `property_img`\n set property_img=:property_img\n case";
                                                        
               foreach($property_img as $property_img):
                             $property_img=$property_img[$count];
                            $sql2.="where property_img.pro_id=".$id;
             endforeach;
             return $sql2;   
                                       
                                         }
                                 
                                                       
             $params2=[':property_img'=>$property_img];
            $inserting2=$db->runQuery($sql2,$params2);
2

the $id does not change.

3

I’m confused about the apparent use of $property_img as both an array and a scalar variable. If it’s allowed, and I’m not sure it is, it doesn’t seem to be a good choice, unless I’m misinterpreting what I am reading.

Nor does $count inside the loop for that matter:

$property_img=$property_img[$count];

Where does $id come from anyway? And why go to the trouble of using prepared statements for the image, but not for the id?

In this bit:

  if(Count($property_img)===0)
                                        
        { var_dump($count);
       $property_img=$property_img[0];

if count() returns zero, doesn’t that mean that there are no elements in the $property_img array, so trying to use one won’t work properly?

And in this bit

$sql2="UPDATE `property_img`\n set property_img=:property_img\n case";
                                                    
           foreach($property_img as $property_img):
                         $property_img=$property_img[$count];
                        $sql2.="where property_img.pro_id=".$id;
         endforeach;

should you be adding another where clause for each image? Especially without spaces around them to separate keywords.

There must be some reason for this that I’m not picking up.

4

in this section of the code am trying to loop through they array and update the db here if the count is more than 1.

5

it’s a foreign key. it is constant.

6

Danger, Will Robinson.

"UPDATE `property_img` set property_img="wark" where property_img.pro_id=1

This query will update ALL rows where pro_id = 1. Running it multiple times, with different property_img’s, will overwrite the previous execution.

7

yeah, that is exactly what am experiencing please what do I do?

8

Without knowing the structure of your database, it’s hard to say. But if i assume that the intention is:

This table before:

pro_id property_img
1 image1
1 image2
1 image3

and after:

pro_id property_img
1 image4
1 image5

Then the appropriate action is DELETE FROM property_img WHERE pro_id = 1; INSERT INTO property_img(pro_id,property_img) VALUES (1,image4), (1,image5)

1 Like
9

Thanks m_huntley. you made it look so easy now. i will do that and get back to you.

10

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.