[Tut] PHP File Upload to Server with MySQL Database - Printable Version +- Sick Gaming (https://www.sickgaming.net) +-- Forum: Programming (https://www.sickgaming.net/forum-76.html) +--- Forum: PHP Development (https://www.sickgaming.net/forum-82.html) +--- Thread: [Tut] PHP File Upload to Server with MySQL Database (/thread-99124.html) |
[Tut] PHP File Upload to Server with MySQL Database - xSicKxBot - 04-02-2022 PHP File Upload to Server with MySQL Database <div style="margin: 5px 5% 10px 5%;"><img src="https://www.sickgaming.net/blog/wp-content/uploads/2022/03/php-file-upload-to-server-with-mysql-database.jpg" width="350" height="216" title="" alt="" /></div><div><div class="modified-on" readability="7.1304347826087"> by <a href="https://phppot.com/about/">Vincy</a>. Last modified on February 1st, 2022.</div> <p>File upload is an important component in building websites. This article will help you to implement a file upload to the server feature with PHP and a MySQL database.</p> <p>Example use cases of where the file upload may be needed in a website,</p> <p>Let us see how to code PHP file upload for a website. You can split this article into the following sections.</p> <ol> <li>PHP file upload – A quick example.</li> <li>File upload – server-side validation.</li> <li>Upload file to the server with database.</li> <li>Upload file as a blob to the database.</li> </ol> <div class="post-section-highlight" readability="38"> <h2>PHP file upload – Quick example</h2> <pre class="prettyprint language-php"><?php if (isset($_FILES['upload_file'])) { move_uploaded_file($_FILES["upload_file"]["tmp_name"], $_FILES["upload_file"]["name"]); } ?> <form name="from_file_upload" action="" method="post" enctype="multipart/form-data"> <div class="input-row"> <input type="file" name="upload_file" accept=".jpg,.jpeg,.png"> </div> <input type="submit" name="upload" value="Upload File"> </form> </pre> </div> <p>This quick example shows a simple code to achieve PHP file upload. It has an HTML form to choose a file to upload. Let the form with the following attributes for supporting file upload.</p> <ol> <li><em>method=post</em></li> <li><em>enctype=multipart/form-data</em></li> </ol> <p>By choosing a file, it will be in a temporary directory. The $_FILES[“upload_file”][“tmp_name”] has that path. The PHP move_uploaded_file() uploads the file from this path to the specified target.</p> <table class="tutorial-table"> <tbody readability="10"> <tr readability="4"> <td>$_FILES[‘<file-input-name>’][‘tmp_name’]</td> <td>PHP file upload temporary path</td> </tr> <tr readability="2"> <td>$_FILES[‘<file-input-name>’][‘name’]</td> <td>File name with extension</td> </tr> <tr readability="4"> <td>$_FILES[‘<file-input-name>’][‘type’]</td> <td>File MIME type. Eg: image/jpeg</td> </tr> <tr readability="2"> <td>$_FILES[‘<file-input-name>’][‘size’]</td> <td>File size (in bytes)</td> </tr> <tr readability="4"> <td>$_FILES[‘<file-input-name>’][‘error’]</td> <td>File upload error code if any</td> </tr> <tr readability="4"> <td>$_FILES[‘<file-input-name>’][‘full_path’]</td> <td>Full path as sent by the browser</td> </tr> </tbody> </table> <p>The form allows <a href="https://phppot.com/php/multiple-file-upload-using-fine-uploader/">multi-file upload</a> by having an array of file input.</p> <h2>PHP file upload configuration</h2> <p>Ensure that the server environment has enough <a href="https://www.php.net/manual/en/features.file-upload.post-method.php" target="_blank" rel="noopener">settings to upload files</a>.</p> <ul> <li>Check with the php.ini file if the <em>file_uploads = on</em>. Mostly, it will be on by default.</li> </ul> <h3>More optional directives to change the default settings</h3> <ul> <li><em>upload_tmp_dir</em> – to change the system default.</li> <li><em>upload_max_filesize</em> – to exceed the default limit.</li> <li><em>max_file_uploads</em> – to break the per-request file upload limit.</li> <li><em>post_max_size</em> – to breaks the POST data size.</li> <li><em>max_input_time</em> – to set the limit in seconds to parse request data.</li> <li><em>max_execution_time </em>– time in seconds to run the file upload script.</li> <li>memory_limit – to set the memory limit in bytes to be allocated.</li> </ul> <h2>File upload – server-side validation</h2> <p>When file uploading comes into the picture, then there should be proper validation. It will prevent <a href="https://owasp.org/www-community/vulnerabilities/Unrestricted_File_Upload" target="_blank" rel="noopener">unexpected responses from the server</a> during the PHP file upload.</p> <p>This code checks the following 4 conditions before moving the file to the target path. It validates,</p> <ul> <li>If the file is not empty.</li> <li>If the file does not already exist in the target.</li> <li>If the file type is one of the allowed extensions.</li> <li>If the file is within the limit.</li> </ul> <p>It shows only the PHP script for <a href="https://phppot.com/jquery/file-size-validation-using-jquery/">validating the uploaded file</a>. The form HTML will be the same as that of the quick example.</p> <p class="code-heading">file-upload-validation.php</p> <pre class="prettyprint language-php"><?php if (isset($_POST["upload"])) { // Validate if not empty if (!empty($_FILES['upload_file']["name"])) { $fileName = $_FILES["upload_file"]["name"]; $isValidFile = true; // Validate if file already exists if (file_exists($fileName)) { echo "<span>File already exists.</span>"; $isValidFile = false; } // Validate file extension $allowedFileType = array( 'jpg', 'jpeg', 'png' ); $fileExtension = strtolower(pathinfo($fileName, PATHINFO_EXTENSION)); if (! in_array($fileExtension, $allowedFileType)) { echo "<span>File is not supported. Upload only <b>" . implode(", ", $allowedFileType) . "</b> files.</span>"; $isValidFile = false; } // Validate file size if ($_FILES["upload_file"]["size"] > 200000) { echo "<span>File is too large to upload.</span>"; $isValidFile = 0; } if ($isValidFile) { move_uploaded_file($_FILES["upload_file"]["tmp_name"], $fileName); } } else { echo "No files have been chosen."; } } ?> </pre> <h2>Upload file to the server with database</h2> <p>This section gives a full-fledged PHP file upload example. It is with add, edit, preview, list images features. The add/edit allows users to choose an image file to upload to the server.</p> <p>The home page displays a list of uploaded <a href="https://phppot.com/css/image-editor-move-scale-skew-rotate-and-spin-with-css-transform/">images with edit</a>, delete action controls. The edit screen will show the preview of the existing file.</p> <h3>PHP file upload and add a new row to the database</h3> <p>This code is for showing a HTML form with a file upload option. This allows users to choose files to upload to the server.</p> <p>The PHP code receives the uploaded file data in $_FILES. In this code, it checks for <a href="https://phppot.com/php/php-image-upload-with-size-type-dimension-validation/">basic file validation</a> to make sure of its uniqueness.</p> <p>Then, it calls functions to upload and insert the file path to the database.</p> <p>The <em><code class="language-html">uploadImage</code></em> runs <a href="https://phppot.com/php/jquery-ajax-image-upload-with-animating-progress-bar/">PHP move_upload_files()</a> to put the uploaded file in a directory.</p> <p>The <code class="language-html"><em>insertImage</em></code> calls database handlers to insert the uploaded path to the database.</p> <p class="code-heading">image-upload-list-preview-edit/insert.php</p> <pre class="prettyprint"><code class="language-html"> <?php namespace Phppot; use Phppot\DataSource; require_once __DIR__ . '/lib/ImageModel.php'; $imageModel = new ImageModel(); if (isset($_POST['send'])) { if (file_exists('../uploads/' . $_FILES['image']['name'])) { $fileName = $_FILES['image']['name']; $_SESSION['message'] = $fileName . " file already exists."; } else { $result = $imageModel->uploadImage(); $id = $imageModel->insertImage($result); if (! empty($id)) { $_SESSION['message'] = "Image added to the server and database."; } else { $_SESSION['message'] = "Image upload incomplete."; } } header('Location: index.php'); } ?> <html> <head> <link href="assets/style.css" rel="stylesheet" type="text/css" /> </head> <body> <div class="form-container"> <h1>Add new image</h1> <form action="" method="post" name="frm-add" enctype="multipart/form-data" onsubmit="return imageValidation()"> <div Class="input-row"> <input type="file" name="image" id="input-file" class="input-file" accept=".jpg,.jpeg,.png"> </div> <input type="submit" name="send" value="Submit" class="btn-link"> <span id="message"></span> </div> </form> <script src="https://code.jquery.com/jquery-3.6.0.min.js" integrity="sha256-/xUj+3OJU5yExlq6GSYGSHk7tPXikynS7ogEvDej/m4=" crossorigin="anonymous"></script> <script src="assets/validate.js"></script> </body> </html> </code></pre> <p><img loading="lazy" class="alignnone size-full wp-image-16127" src="https://phppot.com/wp-content/uploads/2022/01/php-file-upload-form.jpg" alt="php file upload form" width="350" height="216" srcset="https://phppot.com/wp-content/uploads/2022/01/php-file-upload-form.jpg 350w, https://phppot.com/wp-content/uploads/2022/01/php-file-upload-form-300x185.jpg 300w" sizes="(max-width: 350px) 100vw, 350px"></p> <h3>List of uploaded images with edit, delete actions</h3> <p>This is an extension of a usual PHP file upload example. It helps to build a file library interface in an application.</p> <p>This page reads the uploaded images from the database using PHP. The <em><code class="language-html">getAllImages</code></em> function returns the image path data in an array format.</p> <p>The list page iterates this array and lists out the result. And it links the records with the appropriate edit, delete controls.</p> <p class="code-heading">image-upload-list-preview-edit/index.php</p> <pre class="prettyprint"><code class="language-html"> <?php namespace Phppot; use Phppot\DataSource; require_once __DIR__ . '/lib/ImageModel.php'; $imageModel = new ImageModel(); ?> <html> <head> <title>Display all records from Database</title> <link href="assets/style.css" rel="stylesheet" type="text/css" /> </head> <body> <div class="image-datatable-container"> <a href="insert.php" class="btn-link">Add Image</a> <table class="image-datatable" width="100%"> <tr> <th width="80%">Image</th> <th>Action</th> </tr> <?php $result = $imageModel->getAllImages(); ?> <tr> <?php if (! empty($result)) { foreach ($result as $row) { ?> <td><img src="<?php echo $row["image"]?>" class="profile-photo" alt="photo"><?php echo $row["name"]?> </td> <td><a href="update.php?id=<?php echo $row['id']; ?>" class="btn-action">Edit</a> <a onclick="confirmDelete(<?php echo $row['id']; ?>" class="btn-action">Delete</a></td> </tr> <?php } } ?> </table> </div> <script src="https://code.jquery.com/jquery-3.6.0.min.js" integrity="sha256-/xUj+3OJU5yExlq6GSYGSHk7tPXikynS7ogEvDej/m4=" crossorigin="anonymous"></script> <script type="text/javascript" src="assets/validate.js"></script> </body> </html> </code></pre> <p><img loading="lazy" class="alignnone size-large wp-image-16125" src="https://phppot.com/wp-content/uploads/2022/01/list-uploaded-files-from-server-550x172.jpg" alt="list uploaded files from server" width="550" height="172" srcset="https://phppot.com/wp-content/uploads/2022/01/list-uploaded-files-from-server-550x172.jpg 550w, https://phppot.com/wp-content/uploads/2022/01/list-uploaded-files-from-server-300x94.jpg 300w, https://phppot.com/wp-content/uploads/2022/01/list-uploaded-files-from-server.jpg 600w" sizes="(max-width: 550px) 100vw, 550px"></p> <h3>Edit form with file preview</h3> <p>This shows an edit form with an uploaded file preview. It allows replacing the file by uploading a new one.</p> <p>The <a href="https://phppot.com/php/access-form-data-from-php/">form action passes the id</a> of the record to update the file path in the database.</p> <p>The PHP code calls the <em><code class="language-html">updateImage</code></em> with the upload result and the record id.</p> <p class="code-heading">image-upload-list-preview-edit/update.php</p> <pre class="prettyprint"><code class="language-html"> <?php namespace Phppot; use Phppot\DataSource; require_once __DIR__ . '/lib/ImageModel.php'; $imageModel = new ImageModel(); if (isset($_POST["submit"])) { $result = $imageModel->uploadImage(); $id = $imageModel->updateImage($result, $_GET["id"]); } $result = $imageModel->selectImageById($_GET["id"]); ?> <html> <head> <link href="assets/style.css" rel="stylesheet" type="text/css" /> </head> <body> <div class="form-container"> <h1>View/Edit image</h1> <form action="?id=<?php echo $result[0]['id']; ?>" method="post" name="frm-edit" enctype="multipart/form-data" onsubmit="return imageValidation()"> <div class="preview-container"> <img src="<?php echo $result[0]["image"]?>" class="img-preview" alt="photo"> <div>Name: <?php echo $result[0]["name"]?></div> </div> <div Class="input-row"> <input type="file" name="image" id="input-file" class="input-file" accept=".jpg,.jpeg,.png" value=""> </div> <button type="submit" name="submit" class="btn-link">Save</button> <span id="message"></span> </div> </form> <script src="https://code.jquery.com/jquery-3.6.0.min.js" integrity="sha256-/xUj+3OJU5yExlq6GSYGSHk7tPXikynS7ogEvDej/m4=" crossorigin="anonymous"></script> <script src="assets/validate.js"></script> </body> </html> </code></pre> <p><img loading="lazy" class="alignnone size-full wp-image-16129" src="https://phppot.com/wp-content/uploads/2022/01/file-edit-form-with-preview.jpg" alt="file edit form with preview" width="350" height="380" srcset="https://phppot.com/wp-content/uploads/2022/01/file-edit-form-with-preview.jpg 350w, https://phppot.com/wp-content/uploads/2022/01/file-edit-form-with-preview-276x300.jpg 276w" sizes="(max-width: 350px) 100vw, 350px"></p> <p>The delete action is triggered after user confirmation. It removes the file path from the database.</p> <p class="code-heading">delete.php</p> <pre class="prettyprint"><code class="language-html"> <?php namespace Phppot; use Phppot\DataSource; require_once __DIR__ . '/lib/ImageModel.php'; $imageModel = new ImageModel(); $id=$_REQUEST['id']; $result = $imageModel->deleteImageById($id); header("Location: index.php"); ?> </code></pre> <h3>PHP model class to upload file</h3> <p>It contains functions to upload files to a directory and to the database. The PHP file upload function sets a target to put the uploaded file.</p> <p>It processes the PHP $_FILES array to get the file data. It prepares the database query by using the file parameter to perform read, write.</p> <p class="code-heading">imageModel.php</p> <pre class="prettyprint"><code class="language-html"> <?php namespace Phppot; use Phppot\DataSource; class ImageModel { private $conn; function __construct() { require_once 'DataSource.php'; $this->conn = new DataSource(); } function getAllImages() { $sqlSelect = "SELECT * FROM tbl_image"; $result = $this->conn->select($sqlSelect); return $result; } function uploadImage() { $imagePath = "uploads/" . $_FILES["image"]["name"]; $name = $_FILES["image"]["name"]; $result = move_uploaded_file($_FILES["image"]["tmp_name"], $imagePath); $output = array( $name, $imagePath ); return $output; } public function insertImage($imageData) { print_r($imageData); $query = "INSERT INTO tbl_image(name,image) VALUES(?,?)"; $paramType = 'ss'; $paramValue = array( $imageData[0], $imageData[1] ); $id = $this->conn->insert($query, $paramType, $paramValue); return $id; } public function selectImageById($id) { $sql = "select * from tbl_image where id=? "; $paramType = 'i'; $paramValue = array( $id ); $result = $this->conn->select($sql, $paramType, $paramValue); return $result; } public function updateImage($imageData, $id) { $query = "UPDATE tbl_image SET name=?, image=? WHERE id=?"; $paramType = 'ssi'; $paramValue = array( $imageData[0], $imageData[1], $_GET["id"] ); $id = $this->conn->execute($query, $paramType, $paramValue); return $id; } /* * public function execute($query, $paramType = "", $paramArray = array()) * { * $id = $this->conn->prepare($query); * * if (! empty($paramType) && ! empty($paramArray)) { * $this->bindQueryParams($id, $paramType, $paramArray); * } * $id->execute(); * } */ function deleteImageById($id) { $query = "DELETE FROM tbl_image WHERE id=$id"; $result = $this->conn->select($query); return $result; } } ?> </code></pre> <h2>Upload image as a blob to the database</h2> <p>Though this example comes as the last, I guess it will be very useful for most of you readers.</p> <p><a href="https://phppot.com/php/mysql-blob-using-php/">Uploading the file as a blob to the database</a> helps to move the file binary data to the target database. This example achieves this with very few lines of code.</p> <h3>Uploading image file blob using database insert</h3> <p>This file receives the uploaded file from PHP $_FILES. It extracts the file binary data by using PHP file_get_contents() function.</p> <p>Then, it binds the file MIME type and the blob to the <a href="https://phppot.com/php/crud-with-mysqli-prepared-statement-using-php/">prepared query statement</a>.</p> <p>The code specifies the parameter type as ‘b’ for the file blob data. First, it binds a NULL value for the blob field.</p> <p>Then, it sends the file content using send_long_data() function. This function specifies the query parameter index and file blob to bind it to the statement.</p> <p class="code-heading">file-blob-upload/index.php</p> <pre class><?php if (!empty($_POST["submit"])) { if (is_uploaded_file($_FILES['userImage']['tmp_name'])) { $conn = mysqli_connect('localhost', 'root', '', 'blog_eg'); $imgData = file_get_contents($_FILES['userImage']['tmp_name']); $imageProperties = getimageSize($_FILES['userImage']['tmp_name']); $null = NULL; $sql = "INSERT INTO tbl_image_data(image_type ,image_data) VALUES(?, ?)"; $stmt = $conn->prepare($sql); $stmt->bind_param("sb", $imageProperties['mime'], $null); $stmt->send_long_data(1, $imgData); $stmt->execute(); $currentId = $stmt->insert_id; } } ?> <html> <head> <link href="assets/style.css" rel="stylesheet" type="text/css" /> </head> <body> <div class="form-container"> <h1>Upload Image Blob</h1> <form action="" method="post" name="frm-edit" enctype="multipart/form-data" > <?php if(!empty($currentId)) { ?> <div class="preview-container"> <img src="image-view.php?image_id=<?php echo $currentId; ?>" class="img-preview" alt="photo"> </div> <?php } ?> <div Class="input-row"> <input type="file" name="userImage" id="input-file" class="input-file" accept=".jpg,.jpeg,.png" value="" required> </div> <input type="submit" name="submit" class="btn-link" value="Save"> <span id="message"></span> </div> </form> </body> </html> </pre> <p>This file is to read a blob from the database and show the preview. It will be specified in the <img> tag ‘src’ attribute with the appropriate image id.</p> <p class="code-heading">file-blob-upload/image-view.php</p> <pre class><?php $conn = mysqli_connect('localhost', 'root', '', 'blog_eg'); if(isset($_GET['image_id'])) { $sql = "SELECT image_type,image_data FROM tbl_image_data WHERE id = ?"; $stmt = $conn->prepare($sql); $stmt->bind_param("i", $_GET['image_id']); $stmt->execute(); $result = $stmt->get_result(); $row = $result->fetch_array(); header("Content-type: " . $row["image_type"]); echo $row["image_data"]; } mysqli_close($conn); ?> </pre> <p><img loading="lazy" class="alignnone size-large wp-image-16152" src="https://phppot.com/wp-content/uploads/2022/01/php-file-upload-blob-to-server-550x579.jpg" alt="php file upload blob to server" width="550" height="579" srcset="https://phppot.com/wp-content/uploads/2022/01/php-file-upload-blob-to-server-550x579.jpg 550w, https://phppot.com/wp-content/uploads/2022/01/php-file-upload-blob-to-server-285x300.jpg 285w, https://phppot.com/wp-content/uploads/2022/01/php-file-upload-blob-to-server-768x808.jpg 768w, https://phppot.com/wp-content/uploads/2022/01/php-file-upload-blob-to-server.jpg 800w" sizes="(max-width: 550px) 100vw, 550px"></p> <h2>Conclusion</h2> <p>Thus, we have seen a detailed article to learn file upload. I swear we have covered most of the examples on PHP file upload.</p> <p>We saw code in all levels from simple to elaborate file upload components. I hope, this will be helpful to know how to build this on your own.<br /><a class="download" href="https://phppot.com/downloads/php-file-upload.zip">Download</a></p> <p> <!-- #comments --> </p> <div class="related-articles"> <h2>Popular Articles</h2> </p></div> <p> <a href="https://phppot.com/php/php-file-upload/#top" class="top">↑ Back to Top</a> </p> </div> https://www.sickgaming.net/blog/2022/02/01/php-file-upload-to-server-with-mysql-database/ |