Tutorials
Upload Files to MySQL on PHP
Last modified: June 12, 2021You need to be able to run PHP Script on your computer and have MySQL database running. If you do not have them, you can view this tutorial.
Create a database called demo and run the following SQL script to create a table. In this table, we have Id auto incremental primary key, filename, & mime-type as varchar, and content as blob type.
CREATE TABLE Files(
Id int NOT NULL AUTO_INCREMENT,
FileName varchar(255),
Content MEDIUMBLOB,
MimeType varchar(255),
PRIMARY KEY (Id)
);
We connect MySQL through new mysqli($servername, $username, $password, $database).by providing localhost as a server name, root as username, there is no password so it is blank, demo as the database name.
In line 13, the PHP script checks if we submit the file upload, if yes, it creates insert statements together with parameters. $_FILES['file']['name'] gives the filename, file_get_contents($_FILES['file']['tmp_name']); gives the file content in binary, mime_content_type($_FILES['file']['tmp_name']); gives file types (mime types). In line 21 we execute the insert statment and add to the database.
In lines 26 and 27, we query the database to list the file uploaded to the database. Then in line 48-55, we loop through the found record and display to the web.
Finally, in line 57, we close the connection.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "demo";
$conn = new mysqli($servername, $username, $password, $database);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
if (!empty($_FILES)) {
$stmt = $conn->prepare("INSERT INTO Files (FileName, Content, MimeType) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $file, $content, $mime_type);
$file = $_FILES['file']['name'];
$content = file_get_contents($_FILES['file']['tmp_name']);
$mime_type = mime_content_type($_FILES['file']['tmp_name']);
$stmt->execute();
}
$sql = "SELECT Id, FileName, MimeType FROM Files";
$result = $conn->query($sql);
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Upload</title>
</head>
<body>
<form action="" method="post" enctype="multipart/form-data">
<input type="file" name="file">
<button type="submit">Upload</button>
</form>
</body>
</html>
<?php
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "id: " . $row["Id"]. " - Name: " . $row["FileName"]. " " . $row["MimeType"]. "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>
