Store documents and media files in MySQL and php

1:28 PM Unknown 0 Comments


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

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:

nano /etc/my.cnf
and 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 :


sudo nano /etc/php5/apache2/php.ini
which will show you the actual maximum file size .change the 

upload_max_filesize 2M 2M
to 500M for exemple

Now Restart apache and mysql server

sudo service apache2 restart


sudo service mysql restart

11.Screenshots of the project


12.Download the project


Download the project from Github :bolb-in-php-and-mysql



Unknown