Database is used to store and maintain data. Based on stored data generate report, perform actions, etc.
You can also use it to manage the collection of related files. For this, you can either create a new table or update the existing table to store file information.
In this post, I will show you how to upload file and store to PostgreSQL database with PHP.
Contents
- Create a table for storing file info
- Database Connection
- Create a PHP page for displaying file upload form
- Create a PHP page for uploading file
- Retrieve Files from PostgreSQL
- Output
- Conclusion
1. Create a table for storing file info
I am using files the table in the example –
CREATE TABLE files (
id serial PRIMARY KEY,
file_name varchar(100) NOT NULL,
file_path varchar(100) NOT NULL
)
Fields –
- file_name – Store file name.
- file_path – Store file stored location.
2. Database Connection
Create config.php file for database configuration.
Completed Code
<?php
$host = "localhost";
$user = "postgres";
$password = "root";
$dbname = "tutorial";
$con = pg_connect("host=$host dbname=$dbname user=$user password=$password");
if (!$con) {
die('Connection failed.');
}
3. Create a PHP page for displaying file upload form
Create <form method="post" action="upload.php" enctype="multipart/form-data">. In the <form > create a file element and a submit button.
Completed Code
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>How to Upload And Store File To PostgreSQL with PHP</title>
</head>
<body>
<?php
// Status message
if(isset($_GET['status'])){
$status = $_GET['status'];
if($status == 'success')
echo "Upload successfully.";
else if($status == 'failed')
echo "File is not uploaded.";
else if($status == 'required')
echo "Please select a file.";
}
?>
<div style="margin-top: 20px; ">
<form method="post" action="upload.php" enctype="multipart/form-data">
<input type="file" name="file" > <br><br>
<input type="submit" name="submit" value="Upload">
</form>
</div>
</body>
</html>
4. Create a PHP page for uploading file
Create upload.php file that calls on <form > submit.
Also, create an upload folder to store files.
Read file name and assign it to the variable, assign upload to $target_folder variable.
Assign file extensions to $extensions_arr Array. If file extension exists in $extensions_arr Array then upload the file.
If uploaded successfully then insert a record in the files table.
Completed Code
<?php
include "config.php";
if(isset($_POST['submit'])){
$uploadstatus = "";
if(isset($_FILES['file'])){
$file_name = $_FILES['file']['name'];
$target_folder = "upload/";
$target_file = $target_folder . basename($file_name);
// Select file type
$fileType = strtolower(pathinfo($target_file,PATHINFO_EXTENSION));
// Valid file extensions
$extensions_arr = array("jpg","jpeg","png","pdf","txt");
// Check extension
if( in_array($fileType,$extensions_arr) ){
// Upload file
if(move_uploaded_file($_FILES['file']['tmp_name'],$target_file)){
// Insert record
$query = "INSERT INTO files(file_name,file_path) VALUES ($1,$2)";
pg_query_params($con, $query, array($file_name,$target_file));
$uploadstatus = "success";
}else{
$uploadstatus = "failed";
}
}else{
$uploadstatus = "failed";
}
}else{
$uploadstatus = "required";
}
header('Location: index.php?status='.$uploadstatus);
}
5. Retrieve Files from PostgreSQL
Read all records from the files table.
Loop on the fetched records. Check the file extension if it is image type then display file using <img > tag. Pass $file_path in src attribute.
If file is not image type then display using <a > tag. Pass $file_path in href attribute.
Completed Code
<?php
include "config.php";
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>How to Upload And Store File To PostgreSQL with PHP</title>
<!-- CSS -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.1/dist/css/bootstrap.min.css" rel="stylesheet" >
</head>
<body>
<div class="container mt-5">
<div class="row">
<?php
// Fetch all records
$sql = "select * from files";
$records = pg_query($con, $sql);
$html = "";
$imageExtensions_arr = array("jpg","jpeg","png");
while ($row = pg_fetch_assoc($records) ){
$file_name = $row['file_name'];
$file_path = $row['file_path'];
$extension = strtolower(pathinfo($file_path,PATHINFO_EXTENSION));
$html .= "<div class='col-md-3' >";
if(in_array($extension,$imageExtensions_arr)){
$html .= "<img src='".$file_path."' width='200px' height='200px' >";
$html .= "<br>".$file_name;
}else{
$html .= "<a href='".$file_path."' >".$file_name."</a>";
}
$html .= "</div>";
}
echo $html;
?>
</div>
</div>
</body>
</html>
6. Output
7. Conclusion
In the example, I stored the file path in the database but you can also use bytea datatype to store a file in binary form. To display it on the page you need to first write binary data in a file and then use the created file path.
