iterate through column names while updating table in mysql /multiple file uploads

Question

This is my code for uploading multiple images in columns with names img0,img1,img2,img3 etc. How to update table by iterating through column names? Do i need to concatenate?

if(isset($_POST['submit'])){        
        $uploadsDir = "images/property/";
        $allowedFileType = array('jpg','png','jpeg');
        $error="";
        // Velidate if files exist
        if (!empty(array_filter($_FILES['fileUpload']['name']))) {
           $total= count($_FILES['fileUpload']['name']);
           if($total > 6){
               $error="please select less than 6 pictures";
           }
           // Loop through file items
            for($i=0; $i<$total; $i++){
                // Get files upload path
                $fileName        = $_FILES['fileUpload']['name'][$i];
                $tempLocation    = $_FILES['fileUpload']['tmp_name'][$i];
                $targetFilePath  = $uploadsDir . $fileName;
                $fileType        = strtolower(pathinfo($targetFilePath, PATHINFO_EXTENSION));
                $uploadOk = 1;
                if(in_array($fileType, $allowedFileType)){
                        if(move_uploaded_file($tempLocation, $targetFilePath)){
                            $sqlVal = "('".$fileName."')";
                        } else {
                            $error="uploading error";
                        }
                 } else {
                    $error="please select valid image";
                }
                // Add into MySQL database
                if(!empty($sqlVal)) {
          //the problem is here????????   
               $insert = $conn->query("UPDATE property (img???) VALUES $sqlVal");
                    if($insert) {
                        $error="success";
                    } else {
                        $error="database error";
                    }
                }
            }

        } else {
            $error="please select  pictures to upload";
        }
    }

my table has column with names img0,img1,img2,img3,img4.I want to update each image in particular column can i do it with loop

You would be better of redesigning your database. Having repeated columns like that is usually the sign you should have a another table containing an image and a key to the base item. — Nigel Ren, Jan 15 '22 at 11:32

user3783243 · Accepted Answer · 2022-01-15T12:18:43.930

Here's a starting point. You'll need to make some changes as question doesn't contain enough information.

$query = 'UPDATE property set ';
if(empty($_FILES['fileUpload']['name']) || !is_array($_FILES['fileUpload']['name'])) {
    exit('Invalid Update');
} else {
    foreach($_FILES['fileUpload']['name'] as $key => $value){
        $query .= ' img' . $key . ' = ?, ';
    }
}
$query = rtrim($query, ', ');
$query .= ' WHERE ... = ?'; <--- fix this (Don't replace the ?, that is placeholder for value that the column should equal)
$insert = $conn->prepare($query);
$params = array_merge($_FILES['fileUpload']['name'], array('WHERE IDENTIFER')); <--- fix this
$stmt->bind_param(str_repeat('s', count($_FILES['fileUpload']['name']) . 's(WHERE DATA TYPE HERE)', ...$params); <-- fix this
$stmt->execute();

Other notes:

This database design is going to cause issues later. See:

https://en.wikipedia.org/wiki/First_normal_form

You should do more than checking an extension to verify file integrity/security:

Full Secure Image Upload Script

(If not viewing full answer navigate to instead of just relying on the Content-type header for bit about file extension)

For more info on prepared statements with MySQLi see https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php. If just starting out you might consider PDO. It is usable across multiple database systems, and requires less code to create/execute prepared statements. Considerably less when retrieving data, and execution process is a lot clearer than MySQLi's.

iterate through column names while updating table in mysql /multiple file uploads

1 Answers1