Marc's Place

 

BLOB images (PHP and Lasso 9)   How to store and show BLOB images using a MySQL table

---

I was lately building an interface for storing data and signature images coming from rugged handheld scanners into a MySQL table and showing this data onto a Lasso 9 web page. If you need to the same, read on.

Tweet

Scripting Language

I had to decide what language to use for the import process, because I found Lasso 9 scripts a bit difficult to set up for executing locally on the server, by cron. Mind you, I wanted a script that can be executed from a command line, not a call to an URL. So I went for PHP.

First Steps

The interface process reads files from disk that are uploaded per FTP by the handheld scanner. This means that the interface-script cannot run simultaneously and therefore the script must check if it is already running or not. This is useful if you let cron execute a script every minute, and sometimes the script runs longer than a minute.

Next, the files sent by the handheld scanner must be named unique so no files get overwritten by new ones.

Then, to know if the handheld scanner also sent an image, the data in the text file should contain the name of the image file that belongs to the data.

And last, create a table with a BLOB field:

CREATE TABLE `blobdemo` (
    `bdid` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Record ID',
    `bddata` varchar(255) NOT NULL COMMENT 'Scanner data',
    `bdimgname` varchar(255) NOT NULL COMMENT 'Name of image.',
    `bdimgdata` mediumblob NOT NULL COMMENT 'Image data (max 15MB)'
    PRIMARY KEY (`bdid`),
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC

The Import Script

Initialization

First some initialization steps and opening a pooled connection to the database:

#!/usr/bin/php
<?php
// bd-import-test.php

date_default_timezone_set('Europe/Amsterdam');

// Set to pre PHP 8.1 behavior.
mysqli_report(MYSQLI_REPORT_OFF);
// Create DB connection
$s_DBCONNECT = new mysqli('127.0.0.1', 'testuser', 'testpw', 'testdb');
if ($s_DBCONNECT->connect_error) {
    error_log('bd-import-test.php : Connect Error (' . $s_DBCONNECT->connect_errno . ') ' . $s_DBCONNECT->connect_error);
    exit;
}
// Set UTF8 charset
mysqli_set_charset($s_DBCONNECT, "utf8");

Check if already running

if(app_job_status('BD-IMPORT')) {
    error_log('bd-import-test.php : already running.', 0);
    exit;
}
else
    app_job_status('BD-IMPORT', 1);

The function app_job_status is a simple function to read/set the flag in table jobs for key 'BD-IMPORT'.

Process file contents

I am skipping the part that finds out what files to read. At this point I have a path to a single UTF-8 or plain ASCII text file in $zzfile which can be read into a variable:

$array = file($zzfile);

Add the path to an array which contains all files to be deleted when the process is finished:

$filesToDelete[] = $zzfile;

To find any relevant data coming from the scanner, loop through the variable $array and examine each line. I use key values which must contain certain values, so I only look for those keys, skipping blank and other irrelevant lines. In this example, keys begin with a Z or a digit and end with a colon, like ZID:, ZIMG: and 001:, 002:, etc..

// Read file
foreach($array as $line) {
    $tmp = trim($line);
    // Does the line begin with a valid character?
    if(stripos('Z0123456789', substr($tmp, 0, 1)) !== false) {

        // Split the line into the 2 relevant segments
        list($code, $data) = explode(':', $tmp, 2);

        $code = strtoupper(trim($code));
        $data = trim($data);

        if($code == 'ZIMG') {
            $imgdata[0] = $data;
            $imgdata[1] = app_check_uploaded_image($data, 500000);
            if($imgdata[1] !== 'URL') $filesToDelete[] = $data;

            // Do not store yet; assumes ZIMG-key comes before all other data
            $store = false;
        }
        else {
            // ...
            // ... process the data into $zzdata
            // ...

            // Other data captured, now store
            $store = true;
        }

        if($store) {
            $zzsql = "insert into blobdemo (bddata, bdimgname, bdimgdata) values('" . $zzdata . "','" . $imgdata[0] . "','" . $imgdata[1] . "')";

            if(! mysqli_real_query($s_DBCONNECT, $zzsql)) {
                $err = mysqli_error($s_DBCONNECT);
                error_log('bd-import-test.php: MySQL error: ' . $err . " (" . $zzsql . ")");
            }

            $imgdata[0] = '';
            $imgdata[1] = '';
            $store = false;
        }
    }
}

Finishing the script

// Delete files
foreach($filesToDelete as $line) unlink($line);

// Mark as not running
app_job_status('BD-IMPORT', 0);

// Close mysql connection
$s_DBCONNECT->close();

?>

This is very, very basically the flow of the script.

Function app_check_uploaded_image

Inside the above script, the function app_check_uploaded_image() is called to process the image that is also uploaded by the handheld scanner. This image may contain a signature or so. This function is described here and should be placed after the initialization and before the first call to app_job_status(). I have removed any error logging to keep the example simple.

function app_check_uploaded_image($p1, $p2) {
    // $p1 = image file name with path
    // $p2 = max image size

    $retval = '';
    if($p1 !== '') {
        // It is a link to an image
        if(strpos($p1, 'http://') === 0 || strpos($p1, 'https://') === 0)
            $retval = 'URL';
        elseif(file_exists($p1)) {
            $size = filesize($p1);
            if($size <= $p2) {
                $filepointer = @fopen($p1, 'rb'); // READ + Binary
                if($filepointer) {
                    $retval = fread($filepointer, $size);
                    fclose($filepointer);
                }
            }
        }
    }

    return($retval);
}

Display the image on a webpage

For website development, I use Lasso 8 and 9 - more 9 then 8 nowadays - and to display the previously stored image from the BLOB-field, you only need a few lines of code:

[
// File: testblob.ls

var('title' = 'Lasso 9: Display BLOB-Image')
]
<html>
    <head>
        <title>[$title]</title>
    </head>
    <body>
        <h1>[$title]</h1>
        [
        inline(-database='testdb',
                   -sql="select distinct * from blobdemo where bdimgname<>''",
                   -maxRecords='all') => {
            records => {^
                '<h1>'; field('bdid'); '</h1>';
                field('bddata'); ':'; field('tmimgname');
                '<br>'

                if(field('tmimgdata') == 'URL') => {^
                    '<img src="'
                    field('tmimgname')
                    '" alt="external image" width="50">'
                else
                    '<img src="data:image/jpeg;base64,'
                    bytes(field('tmimgdata'))->encodebase64
                    '" alt="blob image" width="50">'
                ^}
                '<hr>'
            ^}
        }
        ]
    </body>
</html>

This code assumes that you have setup Lasso 9 correctly and that Lasso 9 can find the database testdb in its configured connections.

In my tests, this code works with jpg, gif and png. No need to change data:image/jpeg; into something else.

Happy coding!

---

© 1997- Marc Vos (and others)   -   Privacy Statement   -    Contact Me

On this website, Google Analytics is used to track visitor statistics. These are anonymised data about the number of visitors, which pages they visit on this site, from which regions they visit, which web browsers they use, etc.. You will also see non-personalised ads via Google AdSense. Cookies from Paddle or Paypal are placed when you click on a 'Buy now!' or 'Donate!' button, and possible cookies from Disqus when you use that system to comment on one or more blogposts.
Privacy Statement

Dismiss