multiple files upload form records duplicating

[ianhaney]

I have a hopefully small issue on a form submitting data to the mysql database table and email. The email side works fine as does the adding the data to the database table but if I upload two files, it stores the support ticket twice in the database table where as I want to store just once and the files be stored as a array in the database table. I got the code from the link https://www.codexworld.com/upload-multiple-images-store-in-database-php-mysql/

The code I have is below

 

<?php

require_once "registerconfig.php";

if (isset($_POST['submit'])) {
		
		// File upload configuration 
    $targetDir = "support-ticket-images/"; 
    $allowTypes = array('pdf','doc','docx','jpg','png','jpeg','gif');
	
	$statusMsg = $errorMsg = $insertValuesSQL = $errorUpload = $errorUploadType = '';
	
	// Escape user inputs for security
$ticket_subject = htmlentities($_POST['ticket_subject'], ENT_QUOTES);
$ticket_message = strip_tags($_POST['ticket_message'], ENT_QUOTES);
$ticket_status ='PENDING SUPPORT'; 
$username = htmlentities($_SESSION["user_name"], ENT_QUOTES);
$user_id = htmlentities($_SESSION["user_id"], ENT_QUOTES);
	
	$fileNames = array_filter($_FILES['files']['name']);
	if(!empty($fileNames)){ 
        foreach($_FILES['files']['name'] as $key=>$val){ 
            // File upload path 
            $fileName = basename($_FILES['files']['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["files"]["tmp_name"][$key], $targetFilePath)){ 
                    // Image db insert sql 
                    $insertValuesSQL .= "('".$ticket_subject."','".$ticket_message."','".$fileName."','".$ticket_status."','".$username."', '".$user_id."'),";
                }else{ 
                    $errorUpload .= $_FILES['files']['name'][$key].' | '; 
                } 
            }else{ 
                $errorUploadType .= $_FILES['files']['name'][$key].' | '; 
            } 
        }

if(!empty($insertValuesSQL)){ 
            $insertValuesSQL = trim($insertValuesSQL, ','); 
            // Insert image file name into database 
            $insert = $link->query("INSERT INTO DB TABLE NAME (ticket_subject, ticket_message, file_name, ticket_status, user_name, user_id) VALUES $insertValuesSQL");
			
            if($insert){
				
				$to = "emailaddress";

    $subject = "A new support ticket has been submitted";
    $message = " <strong>$username</strong> has just created a support ticket, below is the support ticket
    <br /><br />
    <u>Support Ticket Details</u>
    <br /><br>
    <strong>Support Ticket Subject</strong>: $ticket_subject
    <br/><br><strong>Support Ticket Message</strong>: $ticket_message
    <p><strong><u>Support Ticket Files</u></strong>
    <br>
    <img src='$fileName'>
    ";
    // Always set content-type when sending HTML email
    $headers = "MIME-Version: 1.0" . "\r\n";
    $headers .= "Content-type:text/html;charset=iso-8859-1" . "\r\n";
    // More headers
    $headers .= 'From: <noreply@emailaddress>' . "\r\n";
    $mail=mail($to,$subject,$message,$headers);
				
                $errorUpload = !empty($errorUpload)?'Upload Error: '.trim($errorUpload, ' | '):''; 
                $errorUploadType = !empty($errorUploadType)?'File Type Error: '.trim($errorUploadType, ' | '):''; 
                $errorMsg = !empty($errorUpload)?'<br/>'.$errorUpload.'<br/>'.$errorUploadType:'<br/>'.$errorUploadType;
header("location: support-ticket-confirmation?user=$username");
            }else{ 
                $statusMsg = "Sorry, there was an error uploading your file."; 
            } 
        } 
    }else{ 
        $statusMsg = 'Please select files to upload.';
    } 
     
    // Display status message 
    echo $statusMsg; 
}
?>

The structure of the db table column is file_name, VARCHAR(255), latin1_swedish_ci, NOT NULL

[gw1500se]

What you are asking for is a one-to-many table for your filename. Create a separate table for the file names and use the 'user_id' or something unique as the key that defines who belongs to that filename. Then query that table by 'user_id' and it will return all the filenames associated with that user.

Edited March 17, 2020 by gw1500se

[ianhaney]

Can it be done with the file names in just the one db table rather than putting them in a separate table?

[ginerjm]

Proper RDBMS structure dictates that you do NOT do it that way.  You place the many portion of a one-to-many relationship (what you are doing) into a separate table, linked to the parent table.  Then you write your queries against the 2 tables and loop thru the data.  You will have multiple rows for a single key but you will have all of the details/filenames.

One doesn't store arrays in a db.  Nor does one create multiple columns in any table in order to store multiple "same" values onto one record.

[ianhaney]

Have you got a example ginerjm please of the best way to do it as I am not 100% on PHP to be honest and unsure of the way to do it

[ginerjm]

The way to do what?  Make the table?

Edited March 17, 2020 by ginerjm

[ianhaney]

Store the files uploaded into a seperate table and link to the support tickets so the uploaded files are related to the user who has uploaded them

[ginerjm]

Not sure what you want from me.  You store things into this new table the same way you store other things into tables.  You must define the new table with a key that matches the parent table so that you can connect the dots afterwards.  If your #1 table has a column such as "customer_id" or "user_id" be sure to define a similarly namesd column in the #2 table and place that value into every record that you create with that customer's filenames   Make sense?

Edited March 17, 2020 by ginerjm

[ianhaney]

Ahh ok got it, think I understand that, I'll give it a go

[ianhaney]

I have updated the code to insert into two tables but is not working properly. It inserts the data to the first table but the files are not added to the second db table, below is my code

 

<?php
$fileNames = array_filter($_FILES['files']['name']);
	if(!empty($fileNames)){ 
        foreach($_FILES['files']['name'] as $key=>$val){ 
            // File upload path 
            $fileName = basename($_FILES['files']['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["files"]["tmp_name"][$key], $targetFilePath)){ 
                    // Image db insert sql 
                    $insertValuesSQL .= "('".$fileName."','".$username."','".$user_id."'),";
                }else{ 
                    $errorUpload .= $_FILES['files']['name'][$key].' | '; 
                } 
            }else{ 
                $errorUploadType .= $_FILES['files']['name'][$key].' | '; 
            } 
        }

if(!empty($insertValuesSQL)){ 
            $insertValuesSQL = trim($insertValuesSQL, ','); 
            // Insert image file name into database 			
            $insert = "INSERT INTO support_tickets (ticket_subject, ticket_message, ticket_status, user_name, user_id) VALUES ('$ticket_status', '$ticket_message', '$ticket_status', '$username', '$user_id');";
			$insert .= "INSERT INTO support_ticket_files (file_name, user_name, user_id) VALUES('$insertValuesSQL')";

if(mysqli_multi_query($link, $insert)){
				
				$to = "emailaddress";

    $subject = "A new support ticket has been submitted";
    $message = " <strong>$username</strong> has just created a support ticket, below is the support ticket
    <br /><br />
    <u>Support Ticket Details</u>
    <br /><br>
    <strong>Support Ticket Subject</strong>: $ticket_subject
    <br/><br><strong>Support Ticket Message</strong>: $ticket_message
    <p><strong><u>Support Ticket Files</u></strong>
    <br>
    <img src='$fileName'>
    ";
    // Always set content-type when sending HTML email
    $headers = "MIME-Version: 1.0" . "\r\n";
    $headers .= "Content-type:text/html;charset=iso-8859-1" . "\r\n";
    // More headers
    $headers .= 'From: <noreply@emailaddress.co.uk>' . "\r\n";
    $mail=mail($to,$subject,$message,$headers);
				
                $errorUpload = !empty($errorUpload)?'Upload Error: '.trim($errorUpload, ' | '):''; 
                $errorUploadType = !empty($errorUploadType)?'File Type Error: '.trim($errorUploadType, ' | '):''; 
                $errorMsg = !empty($errorUpload)?'<br/>'.$errorUpload.'<br/>'.$errorUploadType:'<br/>'.$errorUploadType; 
                //$statusMsg = "Files are uploaded successfully.".$errorMsg;
				header("location: support-ticket-confirmation?user=$username");
            }else{ 
                $statusMsg = "Sorry, there was an error uploading your file."; 
            } 
        } 
    }else{ 
        $statusMsg = 'Please select files to upload.';
    } 
     
    // Display status message 
    echo $statusMsg; 
}
      ?>

 

[gw1500se]

Did you echo $insertValuesSQL to make sure it contains what you expect?

[ianhaney]

Just done that and it's outputting the following

('customer-agreement-may-2018.docx','ianhaney35new','180'),('IT DONE RIGHT NOTES.docx','ianhaney35new','180')

It looks ok to me but unsure why it's not adding the files to the second db table

[ginerjm]

Why do you have 3 fields in the new table?  You only need the filename and the key value to connect.  And I would suggest that the key field be the first record.  And if that key is the primary key of the first table it should be first as well.

As for the echo - show us the whole query that is supposed to be building the filenames table.

[ianhaney]

I can remove the user_name column then and link the new table to the other table using user_id or is ticket_id better. I currently have the id as the primary on the new table and on the other table, the primary key is ticket_id so should I make the id on the new table renamed to ticket_id?

[ianhaney]

This is the query for the new table for the file names, it's called support_ticket_files

 

<?php
$insert .= "INSERT INTO support_ticket_files (file_name, user_name, user_id) VALUES('$insertValuesSQL')";
?>

Is that what you meant by showing the whole query?

[ginerjm]

I wanted to see what you actually execute.  That would be an echo of $insert

As for the key - you have to decide what is the best key for your application.  Are we more concerned with tracking tickets or tracking customers?

[ianhaney]

Ahh ok sorry, the echo of $insert is below

INSERT INTO support_tickets (ticket_subject, ticket_message, ticket_status, user_name, user_id) VALUES ('test twelve', 'test twelve two tables files ', 'PENDING SUPPORT', 'ianhaney35new', '180');INSERT INTO support_ticket_files (file_name, user_name, user_id) VALUES('('customer-agreement-may-2018.docx','ianhaney35new','180'),('IT DONE RIGHT NOTES.docx','ianhaney35new','180')')

I guess the id is the best one but guessing I may need to rename it to ticket_id so it matches the column name of the other table. To be honest this is where I struggle as I just want the files uploaded in the new table to be linked to the ticket in the first table so each user can see their own support tickets and the files attached to them if they uploaded any

Edited March 17, 2020 by ianhaney
corrected text

[ginerjm]

Why do you have a user_id as well as a user_name in this table?    Do you have a 3rd table somewhere with user data?

As for your second query - you have too many parens and quotes.  Look at it.

[ianhaney]

I got the users table that stores the users info and then got the support tickets then the support_ticket_files db table

 

Ahh yeah looks like I got extra brackets not needed, I'll look at that now as that could be the issue why it's not adding the data to the new db table

[ianhaney]

I have managed to get the files uploaded all ok now in the support_ticket_files db table and the support ticket info is added to the support_tickets db table. the updated code is below

[ginerjm]

YOu should have php error checking turned on.  YOu would have probably gotten a warning message at least if you did.

As for the user info - put it all in the users table.  Then only use the user_id in any other table and make your connections using that alone.

In a properly-designed database you should never have to duplicate a value in a second table which is what you are doing with user_name.