CODEWITHSUNDEEP
phpmysqlsql
Tommy Chong
Tommy Chong

Reputation: 386

PHP MySQL query for insert with Concatenation assignment variable

I was facing the problem with php insert multiple image and the only post id to the database. In database I have image table and post table, when I upload multiple image the image will store in image table with the current post id.

The problem is the insert syntax is wrong, because the

$insertValuesSQL

is from for each with using .= concatenation assignment to upload the image.

The thing is for I was to add addtional post id easy for me to get the image from which post id, so I have to insert with two thing which is $insertValuesSQL and the $last_id from the post id.

Can someone help me to correct the syntax and able to upload the image with the post id? Aprreciate and thank you.

Error at:

 $insert = $conn->query("INSERT INTO test (file_name, post_id) VALUES $insertValuesSQL,$last_id");

PHP full code:

$targetDir = "uploads/";
$allowTypes = array('jpg','png','jpeg','gif');

$statusMsg = $errorMsg = $insertValuesSQL = $errorUpload = $errorUploadType = '';
if(!empty(array_filter($_FILES['submit-image']['name']))){
    foreach($_FILES['submit-image']['name'] as $key=>$val){
        // File upload path
        $fileName = basename($_FILES['submit-image']['name'][$key]);
        $targetFilePath = $targetDir . $fileName;

        // Check whether file type is valid
        $fileType = pathinfo($targetFilePath,PATHINFO_EXTENSION);

        if(in_array($fileType, $allowTypes)){
            // Upload file to server
            if(move_uploaded_file($_FILES["submit-image"]["tmp_name"][$key], $targetFilePath)){
                // Image db insert sql
                $insertValuesSQL .= "('".$fileName."'),";
            }else{
                $errorUpload .= $_FILES['submit-image']['name'][$key].', ';
            }
        }else{
            $errorUploadType .= $_FILES['submit-image']['name'][$key].', ';
        }
    }

    if (mysqli_query($conn, $sql)) {
        $last_id = mysqli_insert_id($conn);
        echo "New record created successfully. Last inserted ID is: " . $last_id;
        if(!empty($insertValuesSQL)){
            $insertValuesSQL = trim($insertValuesSQL,',');
            // Insert image file name into database
            $insert = $conn->query("INSERT INTO test (file_name, post_id) VALUES $insertValuesSQL,$last_id");
            if($insert){
                $errorUpload = !empty($errorUpload)?'Upload Error: '.$errorUpload:'';
                $errorUploadType = !empty($errorUploadType)?'File Type Error: '.$errorUploadType:'';
                $errorMsg = !empty($errorUpload)?'<br/>'.$errorUpload.'<br/>'.$errorUploadType:'<br/>'.$errorUploadType;
                $statusMsg = "Files are uploaded successfully.".$errorMsg;
            }else{
                $statusMsg = "Sorry, there was an error uploading your file.";
            }
        }
    } else {
        echo "Error: " . $sql . "<br>" . mysqli_error($conn);
    }

}else{
    $statusMsg = 'Please select a file to upload.';
}

Adding one more

if I using the code below :

                $insert = $conn->query("INSERT INTO test (file_name) VALUES $insertValuesSQL");

It successful to upload multiple image but there no post id for the image

Reference from : https://www.codexworld.com/upload-multiple-images-store-in-database-php-mysql/

Upvotes: 0

Views: 268

Answers (1)

Barmar
Barmar

Reputation: 781721

You need to put the $last_id into every list of values being inserted, not as a separate parameter. But you can't do this because you're creating $insertValuesSQL before you set $last_id.

You can move the loop that creates $insertValuesSQL to after you set $last_id, but another way is to use MySQL's built-in function LAST_INSERT_ID():

$insertValuesSQL .= "('".$fileName."', LAST_INSERT_ID()),";

Then you can take $last_id out of the later INSERT query:

$insert = $conn->query("INSERT INTO test (file_name, post_id) VALUES $insertValuesSQL");

Upvotes: 2

Related Questions