The database is used to save and maintain records. Related on saving data, creating reports, perform actions, etc.
You can also use it to guide the collection of related files. For this, you could either create a new DB table or update the existing table to save file information.
In this tutorial, We will show you how to upload file and save to PostgreSQL database with PHP.
1. Create a table for save file info
We are 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 – save file name.
- file_path – save file stored location
2. Database Connection
Create config.php file for DB 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 page for displaying file upload form
Create <form method="post" action="upload.php" enctype="multipart/form-data">. In this <form > create a file element and a button for submit.
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 page for uploading file
Create upload.php file that get data on submit.
Also, create an upload folder to store files.
Read file name and allocate it to the variable, allocate upload to $target_folder variable.
Assign file extensions to $extensions_arr Array. If file extension exists in $extensions_arr Array then upload the uploaded file.
If file 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- Fetch Files from PostgreSQL
Read all data from the files table.
Loop on the retrieved data. Check the file extension if it is image type then display file using tag. Pass $file_path in src (source) attribute.
If file is not image type then display using 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>
Output
For see the video tutorial and output click to link
0 Comments