Upload file to MySQL database using PHP

Question

I have a webpage users can upload documents with. Right now, the code can successfully upload the document to a directory on the server without any issues. However, I need to upload the document to a MySQL table as a new inserted row and then display the document as a link back on the original webpage. However, every time I try to upload to MySQL, it fails and I'm not sure why that is. I receive 0 errors in debugging mode and I can log in and connect successfully to the database. My Query is what fails, but I can successfully run the query in MySQL without errors.

MY CODE:

HTML:

<body>

<br/>

<div id="bodydiv">

<fieldset id='title'>

  <span style='color:aliceblue'>Uploaded SG Documents</span>

</fieldset>

<br/>


<fieldset id='docTypeWO'>

  <span>Scanned Work Orders:</span>

  <div id='responseWO'>

  </div>

</fieldset>


<br/>


<fieldset id='docTypeCS'>

  <span>Cut Sheets:</span>

  <div id='responseCS'>

  </div>

</fieldset>


<br/>


<fieldset id='docTypeOther'>

  <span>Others:</span>

  <div id='responseOther'>

  </div>

</fieldset>


<br/>


<form name="sgFileUpload" id="sgFileUpload" action='sg_addupload.php' method="POST" enctype="multipart/form-data">




<fieldset id='uploadBtnField'>

  <input type="hidden" name="MAX_FILE_SIZE" value="50000000"/> 


  <input type='file' name='searchFile' id='searchFile' multiple>

  <input type='submit' name='startUpload' id='startUpload' value='Upload'>

  <!-- <input type='reset' name='cancelUpload' id='cancelUpload' value="Cancel Upload">

  <input type='button' name='deleteFile' id='deleteFile' value='Delete'> -->

</fieldset>

<!-- The table listing the files available for upload/download -->
    <table><tbody></tbody></table>



  </form> <!-- End Form Input -->

</div>

</body> 
</html>

My AJAX:

                j('#startUpload').on('click', function() {
                    var file_data = j('#searchFile').prop('files')[0];   
                    var form_data = new FormData();                  
                    form_data.append('file', file_data);
                    alert(form_data);                             
                    j.ajax({
                            url: 'sg_addupload.php', // point to server-side PHP script 
                            dataType: 'text',  // what to expect back from the PHP script, if anything
                            cache: false,
                            contentType: false,
                            processData: false,
                            data: form_data,                         
                            type: 'POST',
                            success: function(data){
                                j('#responseWO').html(data); // display response from the PHP script, if any
                            }
                     });
                });

My PHP:

include('inc.php');


//This section works successfully to upload to a directory on the server.

if ( 0 < $_FILES['file']['error'] ) {
    echo 'Error: ' . $_FILES['file']['error'] . '<br>';
}
else {
    move_uploaded_file($_FILES['file']['tmp_name'], 'uploads/' . $_FILES['file']['name']);
}





//This section fails...


    //This is the directory where images will be saved
    $target = "uploads/";
    $target = $target . basename( $_FILES['file']['name']);


    //This gets all the other information from the form
    $fileName = basename( $_FILES['file']['name']);
    $tmpName  = $_FILES['file']['tmp_name'];
    $fileSize = $_FILES['file']['size'];
    $fileType = $_FILES['file']['type'];

    $fp      = fopen($tmpName, 'r');
    $content = fread($fp, filesize($tmpName));
    $content = addslashes($content);
    fclose($fp);


    if(!get_magic_quotes_gpc())
    {
        $fileName = addslashes($fileName);
    }



    //Writes the Filename to the server
    if(move_uploaded_file($_FILES['file']['tmp_name'], $target)) {

        //Tells you if its all ok
        echo "The file ". basename( $_FILES['file']['name']). " has been uploaded, and your information has been added to the directory";


        //connect  to the database 
        $conn = new mysqli($servername, $username, $password, $dbname);

        // Check connection
        if(mysqli_connect_errno() ) {
            printf('Could not connect: ' . mysqli_connect_error());
            exit();
            }

        $conn->select_db($dbname);

        if(! $conn->select_db($dbname) ) {
            echo 'Could not select database. '.'<BR>';
        }


        //Writes the information to the database
        mysqli_query("INSERT INTO sg_uploads(sgref,file,type,size,content,doctype) VALUES('4','$fileName','$fileType','$fileSize','$content','Other')");
        } else {
            //Gives an error if its not
            echo "Sorry, there was a problem uploading your file.";
        }

All help is appreciated. Thank you!

WARNING: When using mysqli you should be using parameterized queries and bind_param to add user data to your query. DO NOT use string interpolation or concatenation to accomplish this because you will create severe SQL injection bugs. NEVER put $_POST data directly into a query. — tadman
– tadman, Commented Dec 12, 2016 at 15:50
I'm still new to bind-param... How would I accomplish this doing a MySQL Insert statement? — rdimouro
– rdimouro, Commented Dec 12, 2016 at 15:53
What do you mean "new to"? It's a method. You call it. The documentation has dozens of examples, and the comments even more. Replace all $... type values in your query with ? and then use bind_param with the appropriate type. It takes literally ten minutes to figure out and will save you hours and hours of frustrating debugging, and possibly even your career if it keeps your site secure. — tadman
– tadman, Commented Dec 12, 2016 at 15:54
so like this: $sql= "INSERT INTO sg_uploads(sgref,file,type,size,content,doctype) VALUES(?,?,?,?,?,?)"; $stmt = $conn->prepare($sql); $stmt->bind_param('issibs', 4, $fileName, $fileType, $fileSize, $content, 'Other'); $stmt->execute(); — rdimouro
– rdimouro, Commented Dec 12, 2016 at 16:03
I've tried editing the code to use prepared statements and now my php script breaks altogether. I really am not sure how to get the file to upload to the table, let alone use prepared statements to do it and every technet I've read so far, references the old mysql_ extension... — rdimouro
– rdimouro, Commented Dec 12, 2016 at 16:10

Onwu Bishop Gideon · Accepted Answer · 2017-02-18 17:25:15Z

1

<?php
error_reporting(E_ALL ^ E_DEPRECATED);
if(isset($_POST['btn-upload'])) {
include '../includes/dbcon.php';   

    $file = $_FILES['file']['name'];
    $file_loc = $_FILES['file']['tmp_name'];
    $file_size = $_FILES['file']['size'];
    $file_type = $_FILES['file']['type'];
    $title = mysqli_real_escape_string($con, $_POST['title']);
    $keywords = mysqli_real_escape_string($con, $_POST['keywords']);
    $categ = mysqli_real_escape_string($con, $_POST['categ']);
    $email = mysqli_real_escape_string($con, $_POST['email']);
    $art_info = mysqli_real_escape_string($con, $_POST['art_info']);
    $folder="../uploads_art_jou/";

$allowed =  array('pdf','doc' ,'docx');
    $file = $_FILES['file']['name'];
    $ext = pathinfo($file, PATHINFO_EXTENSION); 
$file = $folder."$file";    
    //$location =mysqli_real_escape_string ($con, $_POST['location']);

    // new file size in KB
    $new_size = $file_size/1024;  
    // new file size in KB

    // make file name in lower case
    $new_file_name = strtolower($file);

//checks file extension for images only

        if(!in_array($ext,$allowed) ) 
            { 
?>
<script>
       alert('file extension not allowed');
       window.location.href='art_jou_add.php?file_type_not_allowed_error';
</script>

<?php 
    }

//check whether file exist in said folder

        elseif (file_exists($file))
            { 
?>
<script>
       alert('file already exist');
       window.location.href='art_jou_add.php?file_exist';
</script>
<?php
    }

//if file does not exist, move it to folder and save details to table
    else(move_uploaded_file($file_loc,$folder.$file));
    {

    $sql="INSERT INTO art_jou(file,type,size,title,keywords,categ,email,art_info)
             VALUES('$file','$file_type','$file_size','$title','$keywords','$categ','$email','$art_info')";
    mysqli_query($con,$sql);
    echo "it is done";
?>

<?php
    }

    }

?>

this works for me

answered Feb 18, 2017 at 17:25

Onwu Bishop Gideon

968 bronze badges

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

2 Comments

luk2302 Over a year ago

Please add a little explanation to that code, why is it working, what did you do differently?

Onwu Bishop Gideon Over a year ago

I created a database with fields (file, type, size, title, keywords, categ, email, art_info) then I created a folder uploads. That pretty much worked. The code is properly commented to serve a a guide

Collectives™ on Stack Overflow

Upload file to MySQL database using PHP

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