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 >
<form method="post" action="upload-manager.php" id="insert_form" enctype="multipart/form-data">
<label>Vendor</label>
<input type="text" name="vendor" id="vendor" />
<br />
<label>Category</label>
<input type="text" name="category" id="category" />
<br />
<label>Notes</label>
<textarea name="notes" id="notes" ></textarea>
<br />
<label>Date</label>
<input type="date" name="date" id="date" ></textarea>
<script>
$('#date').val(new Date().toJSON().slice(0,10));
</script>
<br />
<label>Import File</label>
<input type="file" name="file" id="fileSelect" />
<br />
<input type="submit" name="insert" id="insert" value="Done" />
</form>
</div>
<div >
<button type="button" 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 >Data Inserted</label>';
$select_query = "SELECT * FROM pdfFiles ORDER BY id DESC";
$result = mysqli_query($connect, $select_query);
$output .= '
<table >
<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"] . '" /></td>
</tr>
';
}
$output .= '</table>';
}
echo $output;
}
?>
CodePudding user response：
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 >
<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.