Using Form to Upload PDF to Server and Data to SQL Database

Question

I am trying to use a modal form to upload and retrieve PDF files. The form includes a file upload and descriptive text (Vendor Name, Category, and Notes) which gets sent to MySQL database through AJAX/PHP. I end up with a table with last column including a button (which I will want a click to open the PDF related to the row of text. The issue I am having is I can only upload to the database or upload the file when submitting the form, unable to do both at the same time. Can anyone provide thought based on the below code and images?

Also, I am uploading directly to the webserver.. is there a "best practice" when it comes to uploading file (i.e. different fileserver)?

<!-- The Modal/Form -->

   <div class="modal-body">
    <form method="post" action="upload-manager.php" id="insert_form" enctype="multipart/form-data">
     <label>Vendor</label>
     <input type="text" name="vendor" id="vendor" class="form-control" />
     <br />
     <label>Category</label>
     <input type="text" name="category" id="category" class="form-control" />
     <br /> 
     <label>Notes</label>
     <textarea name="notes" id="notes" class="form-control"></textarea>
     <br />  
     <label>Date</label>
     <input type="date" name="date" id="date" class="form-control"></textarea>
<script>
    $('#date').val(new Date().toJSON().slice(0,10));
</script>
     <br />  
     <label>Import File</label>
     <input type="file" name="file" id="fileSelect" class="form-control-file" />
     <br />
     <input type="submit" name="insert" id="insert" value="Done" class="btn btn-success" />

    </form>
   </div>
   <div class="modal-footer">
    <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
   </div>
  </div>
 </div>
</div>

<!-- PHP for uploading file to server -->

<?php

if(isset($_FILES['file'])) {

    $file = $_FILES['file'];

    //file details

    $name = $file['name'];
    $tmp_name = $file['tmp_name'];


    $tmp_file_name = "{$name}";
    $tmp_file_path = "files/{$tmp_file_name}";
    

    // mode the file

    move_uploaded_file($tmp_name, $tmp_file_path);

}

?>

<!-- Script for sending to SQL database -->

<script>  
$(document).ready(function(){
 $('#insert_form').on("submit", function(event){  
  event.preventDefault();  
  if($('#vendor').val() == "")  
  {  
   alert("Vendor is required");  
  }  
  else if($('#category').val() == '')  
  {  
   alert("Categoy is required");  
  }  
  else if($('#date').val() == '')
  {  
   alert("Date is required");  
  }
   
  else  
  {  
   $.ajax({  
    url:"insert.php",  
    method:"POST",  
    data:$('#insert_form').serialize(),  
    beforeSend:function(){  
     $('#insert').val("Inserting");  
    },  
    success:function(data){  
     $('#insert_form')[0].reset();  
     $('#add_data_Modal').modal('hide');  
     $('#dataTable').html(data);
     $('#insert').val("Done"); 
     $('#date').val(new Date().toJSON().slice(0,10));   
    }  
   });  
  }  
 });

insert.php

<?php
//insert.php  
include_once 'db.php';
if(!empty($_POST))
{
 $output = '';
    $vendor = mysqli_real_escape_string($connect, $_POST["vendor"]);  
    $category = mysqli_real_escape_string($connect, $_POST["category"]);  
    $notes = mysqli_real_escape_string($connect, $_POST["notes"]);
    $date = mysqli_real_escape_string($connect, $_POST["date"]);
    $query = "
    INSERT INTO pdfFiles(Vendor, Category, Notes, Date)  
     VALUES('$vendor', '$category', '$notes', '$date')
    ";
    if(mysqli_query($connect, $query))
    {
     $output .= '<label class="text-success">Data Inserted</label>';
     $select_query = "SELECT * FROM pdfFiles ORDER BY id DESC";
     $result = mysqli_query($connect, $select_query);
     $output .= '
      <table class="table table-bordered">  
                    <tr>  
                         <th width="25%">Vendor Name</th>
                         <th width="25%">Category</th>  
                         <th width="25%">Notes</th>  
                         <th width="15%">Date</th>      
                         <th width="10%">View</th>  
                    </tr>

     ';
     while($row = mysqli_fetch_array($result))
     {
      $output .= '
       <tr>  
                         <td>' . $row["Vendor"] . '</td> 
                         <td>' . $row["Category"] . '</td> 
                         <td>' . $row["Notes"] . '</td>
                         <td>' . $row["Date"] . '</td>    
                         <td><input type="button" name="view" value="view" id="' . $row["ID"] . '" class="btn btn-info btn-xs view_data" /></td>  
                    </tr>
      ';
     }
     $output .= '</table>';
    }
    echo $output;
}
?>

There is absolutely no reason that you should not be able to upload the file and store data from other form fields in a db within the same operation. You talk of best practises - use Prepared Statements is a big one here as your code, despite the use of mysqli_real_escape_string is potentially vulnerable to sql injection. — Professor Abronsius
– Professor Abronsius, Commented Sep 2, 2022 at 7:33
<input type="date" name="date" id="date" class="form-control"></textarea> is clearly incorrect — Professor Abronsius
– Professor Abronsius, Commented Sep 2, 2022 at 7:39
@ProfessorAbronsius Agreed when it comes to prepared statements. I am still trying to learn, so the above came from an online guide (with some changes I made) but I was planning to fix and use prepared statements once I had it all functioning. As for the date issue, the original guide had that input as <textarea> and I must have missed deleting the </textarea> Good catch! — ac4570
– ac4570, Commented Sep 6, 2022 at 0:10

Professor Abronsius · Accepted Answer · 2022-09-02 09:36:07Z

As an example of how you might upload a file and log to the database targeting two different endpoints. This is most commonly performed by the same script - it is simpler to do so!

The initial process is to upload the file and send back a reference to the uploaded file for the ajax callback to use in the next request where the database is targeted.

As I do not use jQuery this is all performed with vanilla js and the fetch api.

index.html

<!DOCTYPE html>
<html lang='en'>
    <head>
        <meta charset='utf-8' />
        <title></title>
        <script src='//cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js'></script>
        <style>
            form{padding:1rem;width:80%;margin:auto;float:none;border:1px solid black}
            label{display:block;width:80%;margin:0.5rem auto;float:none}
            label *{float:right;width:80%}
        </style>
    </head>
    <body>
    
        <div class='modal-body'>
            <form name='modal' method='post' enctype='multipart/form-data'>
                <label>Vendor: <input type='text' name='vendor' class='form-control' required /></label>
                <label>Category: <input type='text' name='category' class='form-control' required /></label>
                <label>Notes: <textarea name='notes' class='form-control'></textarea></label>
                <label>Date: <input type='date' name='date' class='form-control' required ></label>
                <label>Import File: <input type='file' name='file' class='form-control-file' required /></label>
          
                <input type='button' name='insert' value='Submit' class='btn btn-success' />
            </form>
        </div>
      
        <div class='modal-footer'>
            <button type='button' class='btn btn-default' data-dismiss='modal'>Close</button>
        </div>
        
        <div id='dataTable'></div>
        
        <script>
            $('[name="date"]').val( new Date().toJSON().slice(0,10) );

            let d=document;
            let q=(e,n=d)=>n.querySelector(e);
            let form=d.forms.modal;
            let bttn=form.insert;
            


            bttn.addEventListener('click',e=>{
              let errors={};
              if( q('input[name="vendor"]').value=='' )errors['vendor']='Vendor is required';
              if( q('input[name="category"]').value=='' )errors['category']='Category is required';
              if( q('input[name="date"]').value=='' )errors['date']='Date is required';
              if( q('input[name="file"]').value=='' )errors['file']='File is required';
              
              if( Object.keys( errors ).length > 0 ){
                alert( Object.values( errors ).join( String.fromCharCode( 10 ) ) )
                return
              }
              
              bttn.value='Inserting';
              
              let fd=new FormData();
                  fd.set('file', q('input[name="file"]').files[0] );
                  
              // upload the file
              fetch('upload-manager.php',{ method:'post',body:fd })
                .then( r=>r.text() )
                .then( pdfpath=>{
                
                  console.log( pdfpath )
                  
                  // now send the other form data to the other script. Remove the file first though!
                  fd=new FormData( form );
                  fd.delete('file');
                  fd.set('pdf',pdfpath);
                  
                  fetch('insert.php',{ method:'post',body:fd })
                    .then( r=>r.json() )
                    .then( json=>{
                        console.log( json );
                        
                        let tblContainer=q('#dataTable');
                            tblContainer.innerHTML='';
                            
                        let oTmpl=q('template[data-id="success"]').content.cloneNode( true );
                        
                        tblContainer.appendChild( oTmpl );
                        let tbody=q('tbody',tblContainer);
                        
                        
                        Object.keys( json ).forEach( key=>{
                            let obj=json[ key ];
                            
                            let tr=tbody.insertRow();
                                tr.insertCell().append( d.createTextNode( obj.vendor ) );
                                tr.insertCell().append( d.createTextNode( obj.category ) );
                                tr.insertCell().append( d.createTextNode( obj.notes ) );
                                tr.insertCell().append( d.createTextNode( obj.date ) );
                                tr.insertCell().append( d.createTextNode( obj.pdf ) );
                                tr.insertCell().insertAdjacentHTML('afterbegin',`<input type='button' data-id='${obj.id}' name='view' value='view' class='btn btn-info btn-xs view_data' />`);
                        })
                        
                        form.reset();
                        bttn.value='Done';
                      
                      // do other tasks
                    })
                })
            })
        </script>
        
        <template data-id='success'>
            <table class="table table-bordered">
                <colgroup>
                    <col width='25%' />
                    <col width='25%' />
                    <col width='15%' />
                    <col width='15%' />
                    <col width='10%' />
                    <col width='10%' />
                </colgroup>
                <thead>
                    <tr>  
                        <th>Vendor Name</th>
                        <th>Category</th>  
                        <th>Notes</th>  
                        <th>Date</th>
                        <th>PDF</th>
                        <th>View</th>  
                    </tr>
                </thead>
                <tbody>
                </tbody>
            </table>
        </template>
    </body>
</html>

upload-manager.php

<?php
    if( $_SERVER['REQUEST_METHOD']=='POST' && isset( $_FILES['file'] ) ){
        
        $name=$_FILES['file']['name'];
        $tmp=$_FILES['file']['tmp_name'];
        
        $path=sprintf('%s/files/%s',__DIR__,$name);
        $status=move_uploaded_file( $tmp, $path );
        
        http_response_code(200);
        exit( $name );
    }
    
    http_response_code(404);
    exit();
?>

insert.php

<?php
    #insert.php
    if( $_SERVER['REQUEST_METHOD']=='POST' && isset(
        $_POST['vendor'],
        $_POST['category'],
        $_POST['notes'],
        $_POST['date'],
        $_POST['pdf']
    )){

        # ensure these vars exist!
        $db=new mysqli( $dbhost, $dbuser, $dbpwd, $dbname );
        
        $sql='insert into `pdffiles` (`vendor`, `category`, `notes`, `date`, `pdf` ) values ( ?, ?, ?, ?, ? )';
        $stmt=$db->prepare( $sql );
        $stmt->bind_param('sssss', $_POST['vendor'], $_POST['category'], $_POST['notes'], $_POST['date'], $_POST['pdf'] );
        $stmt->execute();
        $stmt->close();
        
        $sql='select `id`,`vendor`,`category`,`notes`,`pdf`,`date` from `pdffiles` order by `id` desc';
        $res=$db->query( $sql );
        $data=$res->fetch_all( MYSQLI_ASSOC );
        
        http_response_code(200);
        exit( json_encode( $data ) );
    }
    
    http_response_code(404);
    exit();
?>

Using a mocked up table schema:

mysql> describe pdffiles;
+----------+------------------+------+-----+------------+----------------+
| Field    | Type             | Null | Key | Default    | Extra          |
+----------+------------------+------+-----+------------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL       | auto_increment |
| vendor   | varchar(64)      | YES  |     | NULL       |                |
| category | varchar(64)      | YES  |     | NULL       |                |
| notes    | text             | YES  |     | NULL       |                |
| pdf      | varchar(128)     | YES  |     | NULL       |                |
| date     | date             | YES  |     | 0000-00-00 |                |
+----------+------------------+------+-----+------------+----------------+

After a couple of test runs:

mysql> select * from pdffiles;
+----+-------------------------------+--------------------------+------------------------+----------------------------------------+------------+
| id | vendor                        | category                 | notes                  | pdf                                    | date       |
+----+-------------------------------+--------------------------+------------------------+----------------------------------------+------------+
|  1 | Geronimos Hunting Emporium    | hunting knives           | Mexican bandit special | SIEG__X3_MODS_Bruce_Murray.pdf         | 2022-09-02 |
|  2 | Buffalo Bills Bourbon Bonanza | premium sourmash bourbon | subtle                 | Aquatic Ecology Survey Report 2021.pdf | 2022-09-09 |
+----+-------------------------------+--------------------------+------------------------+----------------------------------------+------------+
2 rows in set (0.00 sec)

The resultant HTML:

I hope that illustrates how you might target two different endpoints using AJAX to both upload a file and populate a database.

Thank you! I figured it would be possible to do both in same script, but couldn't figure out how to approach it. Trying my best to learn everything on my own, but can't always find the right resources!

Collectives™ on Stack Overflow

Using Form to Upload PDF to Server and Data to SQL Database

1 Answer 1

index.html

upload-manager.php

insert.php

Using a mocked up table schema:

After a couple of test runs:

The resultant HTML:

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

index.html

upload-manager.php

insert.php

Using a mocked up table schema:

After a couple of test runs:

The resultant HTML:

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related