PHP MySQL BLOB (original) (raw)

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 security reasons, you may need to store large data objects such as images, PDF files, and videos, in the MySQL database.

MySQL provides the BLOB data type that can store a large amount of binary 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, create a new table named files in for practice.

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 used to store the content of the file.

The following CREATE TABLE statement creates the files table:

CREATE TABLE files ( id INT AUTO_INCREMENT PRIMARY KEY, mime VARCHAR (255) NOT NULL, data BLOB NOT NULL );Code language: SQL (Structured Query Language) (sql)

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

`<?php

/**

PHP MySQL BLOB Demo */ class BlobDemo {

const DB_HOST = 'localhost'; const DB_NAME = 'classicmodels'; const DB_USER = 'root'; const DB_PASSWORD = '';

/**
- Open the database connection */ public function __construct() { // open database connection $conStr = sprintf("mysql:host=%s;dbname=%s;charset=utf8", self::DB_HOST, self::DB_NAME);
  
  try { this−>pdo=newPDO(this->pdo = new PDO(this−>pdo=newPDO(conStr, self::DB_USER, self::DB_PASSWORD); //for prior PHP 5.3.6 //$conn->exec("set names utf8"); } catch (PDOException $e) { echo $e->getMessage(); }
}

/**
- close the database connection */ public function __destruct() { // close the database connection $this->pdo = null; }

}`Code language: PHP (php)

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:

` /** * insert blob into the files table * @param string $filePath * @param string $mime mimetype * @return bool */ public function insertBlob($filePath, $mime) { blob=fopen(blob = fopen(blob=fopen(filePath, 'rb');

    $sql = "INSERT INTO files(mime,data) VALUES(:mime,:data)";
    <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>s</mi><mi>t</mi><mi>m</mi><mi>t</mi><mo>=</mo></mrow><annotation encoding="application/x-tex">stmt = </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.6151em;"></span><span class="mord mathnormal">s</span><span class="mord mathnormal">t</span><span class="mord mathnormal">m</span><span class="mord mathnormal">t</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">=</span></span></span></span>this->pdo->prepare($sql);

    <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>s</mi><mi>t</mi><mi>m</mi><mi>t</mi><mo>−</mo><mo>&gt;</mo><mi>b</mi><mi>i</mi><mi>n</mi><mi>d</mi><mi>P</mi><mi>a</mi><mi>r</mi><mi>a</mi><mi>m</mi><msup><mo stretchy="false">(</mo><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mo>:</mo><mi>m</mi><mi>i</mi><mi>m</mi><msup><mi>e</mi><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mo separator="true">,</mo></mrow><annotation encoding="application/x-tex">stmt-&gt;bindParam(&#x27;:mime&#x27;, </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.6984em;vertical-align:-0.0833em;"></span><span class="mord mathnormal">s</span><span class="mord mathnormal">t</span><span class="mord mathnormal">m</span><span class="mord mathnormal">t</span><span class="mord">−</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">&gt;</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:1.0019em;vertical-align:-0.25em;"></span><span class="mord mathnormal">bin</span><span class="mord mathnormal">d</span><span class="mord mathnormal" style="margin-right:0.13889em;">P</span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.02778em;">r</span><span class="mord mathnormal">am</span><span class="mopen"><span class="mopen">(</span><span class="msupsub"><span class="vlist-t"><span class="vlist-r"><span class="vlist" style="height:0.7519em;"><span style="top:-3.063em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mtight"><span class="mord mtight">′</span></span></span></span></span></span></span></span></span><span class="mrel">:</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:0.9463em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">mim</span><span class="mord"><span class="mord mathnormal">e</span><span class="msupsub"><span class="vlist-t"><span class="vlist-r"><span class="vlist" style="height:0.7519em;"><span style="top:-3.063em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mtight"><span class="mord mtight">′</span></span></span></span></span></span></span></span></span><span class="mpunct">,</span></span></span></span>mime);
    <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>s</mi><mi>t</mi><mi>m</mi><mi>t</mi><mo>−</mo><mo>&gt;</mo><mi>b</mi><mi>i</mi><mi>n</mi><mi>d</mi><mi>P</mi><mi>a</mi><mi>r</mi><mi>a</mi><mi>m</mi><msup><mo stretchy="false">(</mo><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mo>:</mo><mi>d</mi><mi>a</mi><mi>t</mi><msup><mi>a</mi><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mo separator="true">,</mo></mrow><annotation encoding="application/x-tex">stmt-&gt;bindParam(&#x27;:data&#x27;, </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.6984em;vertical-align:-0.0833em;"></span><span class="mord mathnormal">s</span><span class="mord mathnormal">t</span><span class="mord mathnormal">m</span><span class="mord mathnormal">t</span><span class="mord">−</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">&gt;</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:1.0019em;vertical-align:-0.25em;"></span><span class="mord mathnormal">bin</span><span class="mord mathnormal">d</span><span class="mord mathnormal" style="margin-right:0.13889em;">P</span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.02778em;">r</span><span class="mord mathnormal">am</span><span class="mopen"><span class="mopen">(</span><span class="msupsub"><span class="vlist-t"><span class="vlist-r"><span class="vlist" style="height:0.7519em;"><span style="top:-3.063em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mtight"><span class="mord mtight">′</span></span></span></span></span></span></span></span></span><span class="mrel">:</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:0.9463em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">d</span><span class="mord mathnormal">a</span><span class="mord mathnormal">t</span><span class="mord"><span class="mord mathnormal">a</span><span class="msupsub"><span class="vlist-t"><span class="vlist-r"><span class="vlist" style="height:0.7519em;"><span style="top:-3.063em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mtight"><span class="mord mtight">′</span></span></span></span></span></span></span></span></span><span class="mpunct">,</span></span></span></span>blob, PDO::PARAM_LOB);

    return $stmt->execute();
}`Code language: PHP (php)

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 inserting data into a BLOB column. See the following updateBlob() method:

` /** * update the files table with the new blob from the file specified * by the filepath * @param int $id * @param string $filePath * @param string $mime * @return bool */ function updateBlob($id, filePath,filePath, filePath,mime) {

    <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>b</mi><mi>l</mi><mi>o</mi><mi>b</mi><mo>=</mo><mi>f</mi><mi>o</mi><mi>p</mi><mi>e</mi><mi>n</mi><mo stretchy="false">(</mo></mrow><annotation encoding="application/x-tex">blob = fopen(</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.6944em;"></span><span class="mord mathnormal">b</span><span class="mord mathnormal" style="margin-right:0.01968em;">l</span><span class="mord mathnormal">o</span><span class="mord mathnormal">b</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">=</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mord mathnormal" style="margin-right:0.10764em;">f</span><span class="mord mathnormal">o</span><span class="mord mathnormal">p</span><span class="mord mathnormal">e</span><span class="mord mathnormal">n</span><span class="mopen">(</span></span></span></span>filePath, 'rb');

    $sql = "UPDATE files
            SET mime = :mime,
                data = :data
            WHERE id = :id;";

    <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>s</mi><mi>t</mi><mi>m</mi><mi>t</mi><mo>=</mo></mrow><annotation encoding="application/x-tex">stmt = </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.6151em;"></span><span class="mord mathnormal">s</span><span class="mord mathnormal">t</span><span class="mord mathnormal">m</span><span class="mord mathnormal">t</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">=</span></span></span></span>this->pdo->prepare($sql);

    <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>s</mi><mi>t</mi><mi>m</mi><mi>t</mi><mo>−</mo><mo>&gt;</mo><mi>b</mi><mi>i</mi><mi>n</mi><mi>d</mi><mi>P</mi><mi>a</mi><mi>r</mi><mi>a</mi><mi>m</mi><msup><mo stretchy="false">(</mo><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mo>:</mo><mi>m</mi><mi>i</mi><mi>m</mi><msup><mi>e</mi><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mo separator="true">,</mo></mrow><annotation encoding="application/x-tex">stmt-&gt;bindParam(&#x27;:mime&#x27;, </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.6984em;vertical-align:-0.0833em;"></span><span class="mord mathnormal">s</span><span class="mord mathnormal">t</span><span class="mord mathnormal">m</span><span class="mord mathnormal">t</span><span class="mord">−</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">&gt;</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:1.0019em;vertical-align:-0.25em;"></span><span class="mord mathnormal">bin</span><span class="mord mathnormal">d</span><span class="mord mathnormal" style="margin-right:0.13889em;">P</span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.02778em;">r</span><span class="mord mathnormal">am</span><span class="mopen"><span class="mopen">(</span><span class="msupsub"><span class="vlist-t"><span class="vlist-r"><span class="vlist" style="height:0.7519em;"><span style="top:-3.063em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mtight"><span class="mord mtight">′</span></span></span></span></span></span></span></span></span><span class="mrel">:</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:0.9463em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">mim</span><span class="mord"><span class="mord mathnormal">e</span><span class="msupsub"><span class="vlist-t"><span class="vlist-r"><span class="vlist" style="height:0.7519em;"><span style="top:-3.063em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mtight"><span class="mord mtight">′</span></span></span></span></span></span></span></span></span><span class="mpunct">,</span></span></span></span>mime);
    <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>s</mi><mi>t</mi><mi>m</mi><mi>t</mi><mo>−</mo><mo>&gt;</mo><mi>b</mi><mi>i</mi><mi>n</mi><mi>d</mi><mi>P</mi><mi>a</mi><mi>r</mi><mi>a</mi><mi>m</mi><msup><mo stretchy="false">(</mo><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mo>:</mo><mi>d</mi><mi>a</mi><mi>t</mi><msup><mi>a</mi><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mo separator="true">,</mo></mrow><annotation encoding="application/x-tex">stmt-&gt;bindParam(&#x27;:data&#x27;, </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.6984em;vertical-align:-0.0833em;"></span><span class="mord mathnormal">s</span><span class="mord mathnormal">t</span><span class="mord mathnormal">m</span><span class="mord mathnormal">t</span><span class="mord">−</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">&gt;</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:1.0019em;vertical-align:-0.25em;"></span><span class="mord mathnormal">bin</span><span class="mord mathnormal">d</span><span class="mord mathnormal" style="margin-right:0.13889em;">P</span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.02778em;">r</span><span class="mord mathnormal">am</span><span class="mopen"><span class="mopen">(</span><span class="msupsub"><span class="vlist-t"><span class="vlist-r"><span class="vlist" style="height:0.7519em;"><span style="top:-3.063em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mtight"><span class="mord mtight">′</span></span></span></span></span></span></span></span></span><span class="mrel">:</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:0.9463em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">d</span><span class="mord mathnormal">a</span><span class="mord mathnormal">t</span><span class="mord"><span class="mord mathnormal">a</span><span class="msupsub"><span class="vlist-t"><span class="vlist-r"><span class="vlist" style="height:0.7519em;"><span style="top:-3.063em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mtight"><span class="mord mtight">′</span></span></span></span></span></span></span></span></span><span class="mpunct">,</span></span></span></span>blob, PDO::PARAM_LOB);
    <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>s</mi><mi>t</mi><mi>m</mi><mi>t</mi><mo>−</mo><mo>&gt;</mo><mi>b</mi><mi>i</mi><mi>n</mi><mi>d</mi><mi>P</mi><mi>a</mi><mi>r</mi><mi>a</mi><mi>m</mi><msup><mo stretchy="false">(</mo><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mo>:</mo><mi>i</mi><msup><mi>d</mi><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mo separator="true">,</mo></mrow><annotation encoding="application/x-tex">stmt-&gt;bindParam(&#x27;:id&#x27;, </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.6984em;vertical-align:-0.0833em;"></span><span class="mord mathnormal">s</span><span class="mord mathnormal">t</span><span class="mord mathnormal">m</span><span class="mord mathnormal">t</span><span class="mord">−</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">&gt;</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:1.0019em;vertical-align:-0.25em;"></span><span class="mord mathnormal">bin</span><span class="mord mathnormal">d</span><span class="mord mathnormal" style="margin-right:0.13889em;">P</span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.02778em;">r</span><span class="mord mathnormal">am</span><span class="mopen"><span class="mopen">(</span><span class="msupsub"><span class="vlist-t"><span class="vlist-r"><span class="vlist" style="height:0.7519em;"><span style="top:-3.063em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mtight"><span class="mord mtight">′</span></span></span></span></span></span></span></span></span><span class="mrel">:</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:0.9463em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">i</span><span class="mord"><span class="mord mathnormal">d</span><span class="msupsub"><span class="vlist-t"><span class="vlist-r"><span class="vlist" style="height:0.7519em;"><span style="top:-3.063em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mtight"><span class="mord mtight">′</span></span></span></span></span></span></span></span></span><span class="mpunct">,</span></span></span></span>id);

    return $stmt->execute();
}`Code language: PHP (php)

Query data from the 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:

` /** * select data from the the files * @param int $id * @return array contains mime type and BLOB data */ public function selectBlob($id) {

    $sql = "SELECT mime,
                    data
               FROM files
              WHERE id = :id;";

    <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>s</mi><mi>t</mi><mi>m</mi><mi>t</mi><mo>=</mo></mrow><annotation encoding="application/x-tex">stmt = </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.6151em;"></span><span class="mord mathnormal">s</span><span class="mord mathnormal">t</span><span class="mord mathnormal">m</span><span class="mord mathnormal">t</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">=</span></span></span></span>this->pdo->prepare($sql);
    <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>s</mi><mi>t</mi><mi>m</mi><mi>t</mi><mo>−</mo><mo>&gt;</mo><mi>e</mi><mi>x</mi><mi>e</mi><mi>c</mi><mi>u</mi><mi>t</mi><mi>e</mi><mo stretchy="false">(</mo><mi>a</mi><mi>r</mi><mi>r</mi><mi>a</mi><mi>y</mi><mo stretchy="false">(</mo><mi mathvariant="normal">&quot;</mi><mo>:</mo><mi>i</mi><mi>d</mi><mi mathvariant="normal">&quot;</mi><mo>=</mo><mo>&gt;</mo></mrow><annotation encoding="application/x-tex">stmt-&gt;execute(array(&quot;:id&quot; =&gt; </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.6984em;vertical-align:-0.0833em;"></span><span class="mord mathnormal">s</span><span class="mord mathnormal">t</span><span class="mord mathnormal">m</span><span class="mord mathnormal">t</span><span class="mord">−</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">&gt;</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mord mathnormal">e</span><span class="mord mathnormal">x</span><span class="mord mathnormal">ec</span><span class="mord mathnormal">u</span><span class="mord mathnormal">t</span><span class="mord mathnormal">e</span><span class="mopen">(</span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.02778em;">rr</span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.03588em;">y</span><span class="mopen">(</span><span class="mord">&quot;</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">:</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:0.7335em;vertical-align:-0.0391em;"></span><span class="mord mathnormal">i</span><span class="mord mathnormal">d</span><span class="mord">&quot;</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">=&gt;</span></span></span></span>id));
    <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>s</mi><mi>t</mi><mi>m</mi><mi>t</mi><mo>−</mo><mo>&gt;</mo><mi>b</mi><mi>i</mi><mi>n</mi><mi>d</mi><mi>C</mi><mi>o</mi><mi>l</mi><mi>u</mi><mi>m</mi><mi>n</mi><mo stretchy="false">(</mo><mn>1</mn><mo separator="true">,</mo></mrow><annotation encoding="application/x-tex">stmt-&gt;bindColumn(1, </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.6984em;vertical-align:-0.0833em;"></span><span class="mord mathnormal">s</span><span class="mord mathnormal">t</span><span class="mord mathnormal">m</span><span class="mord mathnormal">t</span><span class="mord">−</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">&gt;</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mord mathnormal">bin</span><span class="mord mathnormal">d</span><span class="mord mathnormal" style="margin-right:0.07153em;">C</span><span class="mord mathnormal">o</span><span class="mord mathnormal" style="margin-right:0.01968em;">l</span><span class="mord mathnormal">u</span><span class="mord mathnormal">mn</span><span class="mopen">(</span><span class="mord">1</span><span class="mpunct">,</span></span></span></span>mime);
    <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>s</mi><mi>t</mi><mi>m</mi><mi>t</mi><mo>−</mo><mo>&gt;</mo><mi>b</mi><mi>i</mi><mi>n</mi><mi>d</mi><mi>C</mi><mi>o</mi><mi>l</mi><mi>u</mi><mi>m</mi><mi>n</mi><mo stretchy="false">(</mo><mn>2</mn><mo separator="true">,</mo></mrow><annotation encoding="application/x-tex">stmt-&gt;bindColumn(2, </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.6984em;vertical-align:-0.0833em;"></span><span class="mord mathnormal">s</span><span class="mord mathnormal">t</span><span class="mord mathnormal">m</span><span class="mord mathnormal">t</span><span class="mord">−</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">&gt;</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mord mathnormal">bin</span><span class="mord mathnormal">d</span><span class="mord mathnormal" style="margin-right:0.07153em;">C</span><span class="mord mathnormal">o</span><span class="mord mathnormal" style="margin-right:0.01968em;">l</span><span class="mord mathnormal">u</span><span class="mord mathnormal">mn</span><span class="mopen">(</span><span class="mord">2</span><span class="mpunct">,</span></span></span></span>data, PDO::PARAM_LOB);

    $stmt->fetch(PDO::FETCH_BOUND);

    return array("mime" => $mime,
        "data" => $data);
}`Code language: PHP (php)

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

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

`$blobObj = new BlobDemo();

// test insert gif image $blobObj->insertBlob('images/php-mysql-blob.gif',"image/gif");`Code language: PHP (php)

php mysql blob gif example

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

$a = $blobObj->selectBlob(1); header("Content-Type:" . $a['mime']); echo $a['data'];Code language: PHP (php)

php mysql blob render gif image

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:

`$blobObj = new BlobDemo();

// test insert pdf $blobObj->insertBlob('pdf/php-mysql-blob.pdf',"application/pdf");`Code language: PHP (php)

php mysql blob pdf example

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

$a = $blobObj->selectBlob(2); header("Content-Type:" . $a['mime']); echo $a['data'];Code language: PHP (php)

php mysql blob render PDF

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

$blobObj->updateBlob(2, 'images/php-mysql-blob.gif', "image/gif"); <math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>a</mi><mo>=</mo></mrow><annotation encoding="application/x-tex">a = </annotation></semantics></math>a=blobObj->selectBlob(2); header("Content-Type:" . $a['mime']); echo $a['data'];Code language: PHP (php)

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

Download PHP MySQL BLOB Source Code

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

Was this tutorial helpful?