By storing media files in the MySQL database make it easier to retrieve files uploaded by the user or in a specific category.
For this require storing the file on the server and saving the reference to the database.
In the tutorial, I show how you can upload and store video to the MySQL database table with PHP. Also, show how you can display stored videos in the database.
Contents
1. Table structure
I am using videos table in the example.
CREATE TABLE `videos` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `name` varchar(255) NOT NULL, `location` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2. Configuration
Create a config.php file for the database configuration.
Completed Code
<?php
session_start();
$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */
$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
die("Connection failed: " . mysqli_connect_error());
}
3. Upload and Store Video
Create a new folder videos at the project root.
HTML
Using $_SESSION['message'] to display the file upload response. Create a file element and a submit button in the <form>.
PHP
On <form > submit assign max file size 5MB in bytes to $maxsize. You can update this value according to your requirement.
Assign file upload folder to $target_dir. Read the file extension and assign it to $extension variable.
Initialize $extensions_arr Array with valid video file extensions.
If file extension exists in $extensions_arr Array then check its file size. If it is not exceeded then upload the file and insert a new record in the videos table.
Pass $name to name field and $target_file to the location field.
Completed Code
<?php
include("config.php");
if(isset($_POST['but_upload'])){
$maxsize = 5242880; // 5MB
if(isset($_FILES['file']['name']) && $_FILES['file']['name'] != ''){
$name = $_FILES['file']['name'];
$target_dir = "videos/";
$target_file = $target_dir . $_FILES["file"]["name"];
// Select file type
$extension = strtolower(pathinfo($target_file,PATHINFO_EXTENSION));
// Valid file extensions
$extensions_arr = array("mp4","avi","3gp","mov","mpeg");
// Check extension
if( in_array($extension,$extensions_arr) ){
// Check file size
if(($_FILES['file']['size'] >= $maxsize) || ($_FILES["file"]["size"] == 0)) {
$_SESSION['message'] = "File too large. File must be less than 5MB.";
}else{
// Upload
if(move_uploaded_file($_FILES['file']['tmp_name'],$target_file)){
// Insert record
$query = "INSERT INTO videos(name,location) VALUES('".$name."','".$target_file."')";
mysqli_query($con,$query);
$_SESSION['message'] = "Upload successfully.";
}
}
}else{
$_SESSION['message'] = "Invalid file extension.";
}
}else{
$_SESSION['message'] = "Please select a file.";
}
header('location: index.php');
exit;
}
?>
<!doctype html>
<html>
<head>
<title>Upload and Store video to MySQL Database with PHP</title>
</head>
<body>
<!-- Upload response -->
<?php
if(isset($_SESSION['message'])){
echo $_SESSION['message'];
unset($_SESSION['message']);
}
?>
<form method="post" action="" enctype='multipart/form-data'>
<input type='file' name='file' />
<input type='submit' value='Upload' name='but_upload'>
</form>
</body>
</html>
4. Read Videos
Create readfile.php file to display the stored videos in the videos table.
Fetch all records from videos table.
Create <video> element where pass $location in the src attribute and use <span > to display the name.
Completed Code
<?php
include("config.php");
?>
<!doctype html>
<html>
<head>
<title>Upload and Store video to MySQL Database with PHP</title>
</head>
<body>
<div>
<?php
$fetchVideos = mysqli_query($con, "SELECT * FROM videos ORDER BY id DESC");
while($row = mysqli_fetch_assoc($fetchVideos)){
$location = $row['location'];
$name = $row['name'];
echo "<div style='float: left; margin-right: 5px;'>
<video src='".$location."' controls width='320px' height='320px' ></video>
<br>
<span>".$name."</span>
</div>";
}
?>
</div>
</body>
</html>
5. Conclusion
Store video file in a folder and insert file location in a MySQL database table. Display file using <video> element.
Before setting max file size for file size validation make sure to check post_max_size and upload_max_filesize in php.ini file and update it accordingly if required.
