08.09.2019»»воскресенье

footballfullpac

How To Create Pdf File In Php From Database

08.09.2019
    96 - Comments

PDF is common format of file to share or read data on web.We have already shared tutorial to convert html data into pdf and best pdf viewer. Now here I am creating a simple php application which are generating PDF file using mpdf library. Single file or multiple files can be easily uploaded using PHP. PHP provides a quick and simple way to implement server-side file upload functionality. Generally, in the web application, the file is uploaded to the server and the file name is stored in the database.

Summary: in this tutorial, you will learn how to handle BLOB data using PHP PDO. We will show you how to insert, update and select BLOB data in MySQL databases.

Sometimes, for the security reasons, you may need to store large data objects e.g., images, PDF files, and videos in the MySQL database.

How To Create Pdf File In Php From Database

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.

Let’s see how PHP PDO handles the BLOB type in MySQL.

First, we create a new table named files in the sample database for practicing.

The files table contains three columns:

  • The id column is the primary key, auto-increment column.
  • The mime column stores the mime type of the file.
  • The data column whose data type is the BLOB that is used to store the content of the file.

The following CREATE TABLE statement creates the files table:

2
4
idINTAUTO_INCREMENTPRIMARY KEY,
dataBLOBNOT NULL

Second, we define a class called BlobDemo with the following code:

2
4
6
8
10
12
14
16
18
20
22
24
26
28
30
32
34
36
* PHP MySQL BLOB Demo
classBobDemo{
constDB_HOST='localhost';
constDB_USER='root';
* Open the database connection
publicfunction__construct(){
$conStr=sprintf('mysql:host=%s;dbname=%s;charset=utf8',self::DB_HOST,self::DB_NAME);
try{
$this->pdo=newPDO($conStr,self::DB_USER,self::DB_PASSWORD);
//$conn->exec('set names utf8');
echo$e->getMessage();
}
/**
*/
// close the database connection
}
}

In the __construct() method, we open a database connection to the MySQL database, and in the __destruct() method, we close the connection.

Insert BLOB data into the database

PHP PDO provides a convenient way to work with BLOB data using the streams and prepare statements. To insert the content of a file into a BLOB column, you follow the steps below:

  • First, open the file for reading in binary mode.
  • Second, construct an INSERT statement.
  • Third, bind the file handle to the prepared statement using the bindParam() method and call the execute() method to execute the query.

See the following insertBlob() method:

2
4
6
8
10
12
14
16
* insert blob into the files table
* @param string $mime mimetype
*/
$blob=fopen($filePath,'rb');
$sql='INSERT INTO files(mime,data) VALUES(:mime,:data)';
$stmt->bindParam(':data',$blob,PDO::PARAM_LOB);
return$stmt->execute();

Notice that the PDO::PARAM_LOB instructs PDO to map the data as a stream.

Update an existing BLOB column

To update a BLOB column, you use the same technique as described in the inserting data into a BLOB column. See the following updateBlob() method:

2
4
6
8
10
12
14
16
18
20
22
24
* update the files table with the new blob from the file specified
* @param int $id
* @param string $mime
*/
SET mime = :mime,
WHERE id = :id;';
$stmt=$this->pdo->prepare($sql);
$stmt->bindParam(':mime',$mime);
$stmt->bindParam(':id',$id);
return$stmt->execute();

Query data from BLOB column

The following steps describe how to select data from a BLOB column:

  • First, construct a SELECT statement.
  • Second, bind the corresponding parameter using the bindColumn() method of the PDOStatement object.
  • Third, execute the statement.

See the following selectBlob() method:

2
4
6
8
10
12
14
16
18
20
22
* select data from the the files
* @return array contains mime type and BLOB data
publicfunctionselectBlob($id){
$sql='SELECT mime,
FROM files
$stmt->execute(array(':id'=>$id));
$stmt->bindColumn(2,$data,PDO::PARAM_LOB);
$stmt->fetch(PDO::FETCH_BOUND);
returnarray('mime'=>$mime,
}

PHP MySQL BLOB examples

In the following examples, we will use the BlobDemo class to save a GIF image and a PDF file into the BLOB column of the files table.

PHP MySQL BLOB with image files

How to create pdf file in php from database software

First, we insert binary data from the images/php-mysql-blob.gif file into the BLOB column of the files table as follows:

2
4
$blobObj->insertBlob('images/php-mysql-blob.gif','image/gif');

Then, we can select the BLOB data and display it as a GIF image:

2
header('Content-Type:'.$a['mime']);

PHP MySQL BLOB with PDF files

The following code inserts the content of the pdf/php-mysql-blob.pdf PDF file into the BLOB column:

2
4
$blobObj->insertBlob('pdf/php-mysql-blob.pdf','application/pdf');

Then, we can select the PDF data and render it in the web browser as follows:

2
header('Content-Type:'.$a['mime']);

To replace the PDF file with the GIF image file, you use the updateBlob() method as follows:

2
4
$blobObj->updateBlob(2,'images/php-mysql-blob.gif','image/gif');
$a=$blobObj->selectBlob(2);
echo$a['data'];

You can download the source code of this tutorial via the following link:

In this tutorial, we have shown you how to manage MySQL BLOB data including inserting, updating, and querying blob.

 footballfullpac © 2019