PHP MySQL query for insert with Concatenation assignment variable

Question

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/

@user3783243 Yes, that one is insert post query, so after succesful insert post, i get the last id for upload the image with the id — Tommy Chong
– Tommy Chong, Commented Nov 17, 2018 at 15:19
Output the actual query, it looks like its going to be pretty mangled. I think it'd come out as INSERT INTO test (file_name, asd) VALUES ('filename'),,last_id_value which isn't going to work. Use the mysqli error reporting function so you get the actual error. This also is open to SQL injections. I'd recommend doing this with parameterized queries. — user3783243
– user3783243, Commented Nov 17, 2018 at 15:22
@user3783243 sorry my bad, I was typed wrong test (file_name, asd) — Tommy Chong
– Tommy Chong, Commented Nov 17, 2018 at 15:28
This is vulnerable to SQL injection. Don’t ignore the advice to use parameterized queries. — Ry-
– Ry- ♦, Commented Nov 17, 2018 at 20:16

Barmar · Accepted Answer · 2018-11-17 15:44:11Z

2

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");

answered Nov 17, 2018 at 15:44

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3783243 Over a year ago

LAST_INSERT_ID() can create race condition though, can't it?

Barmar Over a year ago

No, it's exactly the same as mysqli_insert_id(). They both get the last ID inserted by the current connection.

Collectives™ on Stack Overflow

PHP MySQL query for insert with Concatenation assignment variable

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related