Store documents and media files in MySQL and php
Abstract
A PHP script can be used with a HTML form to allow users to upload files to the server. Initially files are uploaded and stored into MySQL database by a PHP script.This tutorial is an attempt to show you how to store binary files in MySQL using BOLB .
1.What is BOLB ?!
BLOB (Binary Large Object) is a large object data type in the database system. BLOB could store a large chunk of data, document types and even media files like audio or video files. BLOB fields allocate space only whenever the content in the field is utilized. BLOB allocates spaces in Giga Bytes.
- USAGE OF BLOB :
You can write a binary large object (BLOB) to a database as either binary or character data, depending on the type of field at your data source. To write a BLOB value to your database, issue the appropriate INSERT or UPDATE statement and pass the BLOB value as an input parameter. If your BLOB is stored as text, such as a SQL Server text field, you can pass the BLOB as a string parameter. If the BLOB is stored in binary format, such as a SQL Server image field, you can pass an array of type byte as a binary parameter.
2. BOLB and MySQL
MySQL provides a BLOB type that can hold a large amount of data. BLOB stands for the binary large data object. The maximum value of a BLOB object is specified by the available memory and the communication package size. You can change the communication package size by using the max_allowed_packet variable in MySQL and post_max_size in the PHP settings.
3.Files
In your /www folder of wamp/Lamp server create this files to start the project
- Config.php : set the globle variables of our application
- index.php: this is the main page where the upload form was created
- insert.php: link to the database and insert files
- download.php: fetch data from the database and force files to be downloaded .
- show.php: list all files in the database
4.Create the Database
Database name: mystore
Table name: file_upload
Table name: file_upload
CREATE TABLE IF NOT EXISTS `file_upload` ( `file_id` int(11) NOT NULL AUTO_INCREMENT, `name` text NOT NULL, `mime` text NOT NULL, `size` text NOT NULL, `data` blob NOT NULL, `ext` text NOT NULL, `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`file_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
5. config.php
<?php
define("host","_HOST_");
define("username","_USERNAME_");
define("password","_PASSOWRD_");
define("db","_DBname_");
?>
6. index.php
<!--
This application developed by ibsSOFT @NODEME blog
visit http://nodeme.blogspot.com
@ihebBenSalem
-->
<!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">
<title>Upload</title>
<!-- Bootstrap CSS -->
<link href="//netdna.bootstrapcdn.com/bootstrap/3.3.4/css/bootstrap.min.css" rel="stylesheet">
<!-- HTML5 Shim and Respond.js IE8 support of HTML5 elements and media queries -->
<!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
<!--[if lt IE 9]>
<script src="https://oss.maxcdn.com/libs/html5shiv/3.7.0/html5shiv.js"></script>
<script src="https://oss.maxcdn.com/libs/respond.js/1.4.2/respond.min.js"></script>
<![endif]-->
</head>
<body>
<center>
<form action="insert.php" method="POST" role="form" enctype = "multipart/form-data">
<legend>Upload files</legend>
<label class="btn btn-default btn-file">
Browse <input type="file" name="myfile">
</label>
<button type="submit" class="btn btn-danger">Upload</button>
</form><br>
<a href="show.php">Show upload file list</a>
</center>
<!-- jQuery -->
<script src="//code.jquery.com/jquery.js"></script>
<!-- Bootstrap JavaScript -->
<script src="//netdna.bootstrapcdn.com/bootstrap/3.3.4/js/bootstrap.min.js"></script>
</body>
</html>
7. insert.php
<?php
/*
This application developed by ibsSOFT @NODEME blog
visit http://nodeme.blogspot.com
@ihebBenSalem
*/
require("config.php");
if (isset($_FILES["myfile"])) {
$error=$_FILES["myfile"]["error"];
if ($error ==0) {
# code...
$db_link=mysqli_connect(host,username,password,db) or die("Can not connect to db !");
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
}
$name = mysqli_real_escape_string($db_link,$_FILES['myfile']['name']);
$extension = strtolower(substr($name, strpos($name, '.') + 1));
$tmp_name = mysqli_real_escape_string($db_link,$_FILES['myfile']['tmp_name']);
$type = mysqli_real_escape_string($db_link,$_FILES['myfile']['type']);
$size = mysqli_real_escape_string($db_link,$_FILES['myfile']['size']);
$data=mysqli_real_escape_string($db_link,file_get_contents($tmp_name));
$sql="INSERT INTO file_upload (name,mime, size,ext,data) VALUES ('$name','$type','$size','$extension','$data')";
if (!mysqli_query($db_link,$sql)) {
die('Error: ' . mysqli_error($con));
}
else
{
header("location:show.php");
}
}
?>
8. download.php
<?php
/*
This application developed by ibsSOFT @NODEME blog
visit http://nodeme.blogspot.com
@ihebBenSalem
*/
require("config.php");
if (isset($_GET["id"]) and !empty($_GET["id"])) {
# code...
$id=$_GET["id"];
if ($id<=0) { //check the id is valid
# code...
die("Error in id ! try again");
}
$con=new mysqli(host,username,password,db) or die(" Can not connect to db ! ");
$result=$con->query(" SELECT file_id, `mime` , `name` , `size` , `data` FROM `file_upload` WHERE `file_id` ='$id' ");
if($result) {
// Make sure the result is valid
if($result->num_rows == 1) {
// Get the row
$row = mysqli_fetch_assoc($result);
// Print headers
header("Content-Type: ". $row['mime']);
header("Content-Length: ". $row['size']);
header("Content-Disposition: attachment; filename=". $row['name']);
// Print data
ob_clean();
flush();
echo $row['data'];
}
}
}
?>
9. show.php
<?php
/*
This application developed by ibsSOFT @NODEME blog
visit http://nodeme.blogspot.com
@ihebBenSalem
*/
require("config.php");
$con=new mysqli(host,username,password,db);
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$qy=$con->query(" SELECT * FROM `file_upload` order by date DESC;");
?>
<!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">
<title>Show</title>
<!-- Bootstrap CSS -->
<link href="//netdna.bootstrapcdn.com/bootstrap/3.3.4/css/bootstrap.min.css" rel="stylesheet">
<!-- HTML5 Shim and Respond.js IE8 support of HTML5 elements and media queries -->
<!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
<!--[if lt IE 9]>
<script src="https://oss.maxcdn.com/libs/html5shiv/3.7.0/html5shiv.js"></script>
<script src="https://oss.maxcdn.com/libs/respond.js/1.4.2/respond.min.js"></script>
<![endif]-->
</head>
<body>
<div class="well well-lg"><center><h3> Files List</h3></center> </div>
<a class="btn btn-default btn-block" href="index.php" role="button">Upload more files</a>
<table class="table table-hover">
<thead>
<tr>
<th>#id</th>
<th>#Name</th>
<th>#Mime</th>
<th>#size
</th>
<th>#Extension</th>
<th>#Download</th>
<th>#Date</th>
</tr>
</thead>
<tbody>
<?php
$counter=0;
while ($rs=$qy->fetch_array()) {
# code...
$counter++;
echo '<tr>
<td>'.$counter.'</td>
<td>'.$rs[1].'</td>
<td>'.$rs[2].'</td>
<td>'.$rs[3].'</td>
<td>'.$rs[5].'</td>
<td>'.$rs[6].'</td>
<td><a href="download.php?id='.$rs[0].'">Download</a>
</td>
</tr>';
}
?>
</tbody>
</table>
<!-- jQuery -->
<script src="//code.jquery.com/jquery.js"></script>
<!-- Bootstrap JavaScript -->
<script src="//netdna.bootstrapcdn.com/bootstrap/3.3.4/js/bootstrap.min.js"></script>
</body>
</html>
10.Configure apache
By trying to upload huge files such as 1 G0 or even files in Mo this application, we'll not work well because by default the size supported by mysql server is limited to 16 M0 for allowing packet, and 16 M0 for max file size, so, obviously we need to change this configuration to upload files with big size.
open up your Terminal and let's go :D:
open up your Terminal and let's go :D:
nano /etc/my.cnfand now :
add the line: max_allowed_packet=256M (obviously adjust size for whatever you need)
under the [MYSQLD] section. He made a mistake of putting it at the bottom of the file first so it did not work.
Press Ctrl+x then Y to save the conf .
Now let's update the apache upload size :
Now let's update the apache upload size :
sudo nano /etc/php5/apache2/php.ini
which will show you the actual maximum file size .change the
to 500M for exempleupload_max_filesize 2M 2MNow Restart apache and mysql server
sudo service apache2 restart
sudo service mysql restart
11.Screenshots of the project
12.Download the project
Share story
![How to create Basic Login page in Php,MySQL,Bootstrap [part 1]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgUkDDIJAEtLOTNb0109L2QPJ3XTTEW6qqSBMtpnDeXuSu5h3CWuPPCW4lwN0VJ-S7kqeBQeMT8kHqHgj7FEaIQST2JK5423bWjZDdLeyEMVqK0knAvucjcReVchh0lt3y4vBiEUFtzH4w/s72-c/28_login.png)
![Write your own PHP MVC Framework [part 1]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgOac0AV601Kf4LtH3pBbad9KchFR3xReRHKClgew132HmH3ilC0BP0AEkItG4VaL2P748DvU8VxF2zyeQtkz9MztxnT0UavV2BqYwU4bWrbfR6lo7BrxMgArrq_3G2b4dtNn8L3Wp6Rqs/s72-c/PHP-MYSQL-MVC-FRAMEWORK.jpg)
![How to create Basic Register page in Php,MySQL,Bootstrap [part 2]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiKPbzpQRv4U5SIff5mTt8bB0DbQuye7fQm1p_Ft8MeavsXiuydlzgQ_sOsslIfdIIrGKIwbKrad9oW9KRvuElSCyCctggcJYIdZlvwaXLrKficr0vQmr8iSqXZe273cJ62qOZa59RZ5kQ/s72-c/Flat_Web_Design_Elements_1.jpg)
