keeping images directly in mysql and displaying them in the koolreport

Richb201 commented on Jul 25, 2021

I have been having lots of issues with images and not being able to display images from s3 in a koolreport. So I followed your lead which was to bring down the images to the server's harddisk before building the report. This works but i can't seem to only transfer the images one time. This is making my app "crawl" and is not acceptable. So what I would like to do it keep the thumbnails up on mysql while keeping the actual full file up on s3. I use MYSQL RDS. Is that a problem? How should I define the field in the table, small blob?

How would I modify the report view? Here is the way it works now with getting the image from the local hard drive.

   <?php
    Table::create(array(
        "dataStore" => $this->dataStore("excel"),
        "showFooter" => true,
        "columns" => array(
            "description",
            "thumbnail_url" => array(
                "formatValue"=> "<img width='500px' height='400px' src='".base_url()."/@value' />"
            ),
        ),

        "cssClass"=>array(
            "table"=>"table-bordered table-striped table-hover"
        )
    ));
    ?>

Sebastian Morales commented on Jul 26, 2021

If you keep your image's thumbnail data directly in your database I think you could convert it to base64 data for your thumbnail column. Here's an example of image tag with base64 data:

<img src="data:image/png;base64, iVBORw0KGgoAAAANSUhEUgAAAAUA
    AAAFCAYAAACNbyblAAAAHElEQVQI12P4//8/w38GIAXDIBKE0DHxgljNBAAO
        9TXL0Y4OHwAAAABJRU5ErkJggg==" alt="Red dot" />

And here's a command to convert an image's binary data to base64 with PHP:

$base64 = 'data:image/' . $type . ';base64,' . base64_encode($imgData);

Richb201 commented on Jul 26, 2021

I am confused about your answer. Are you saying to replace

"formatValue"=> "<img width='500px' height='400px' src='".base_url()."/@value' />"

with:

    AAAFCAYAAACNbyblAAAAHElEQVQI12P4//8/w38GIAXDIBKE0DHxgljNBAAO
        9TXL0Y4OHwAAAABJRU5ErkJggg==" alt="Red dot" />"formatValue"=> "<img width='500px' height='400px' src='".base_url()."/@value' />"

Please communicate less cryptically.

Sebastian Morales commented on Jul 27, 2021

An img tag's src could be a url or base64 data. Because your thumbnails are stored directly in your database you can not use them as urls for your img tags. I shew you the way to convert an image binary data to base64 and use the base64 as src data for your img tags. A pseudo code would be like:

    "thumbnail_url" => array(
                "formatValue"=> function($value, $row, $cKey) {
                    $type = "png"; // or "jpg"
                    $base64 = 'data:image/' . $type . ';base64,' . base64_encode($value);
                    return "<img src='$base64' />";
                }
            ),

KoolReport's Forum

Keeping images directly in mysql and displaying them in the koolreport #2228

Build Your Excellent Data Report