SQLite Forum

How to READ and WRITE an image to an SQLITE BLOB FIELD
Login

How to READ and WRITE an image to an SQLITE BLOB FIELD

(1) By DMVB123 on 2021-03-11 20:22:38 [link]

I have an existing web application that uses an SQLITE database and PHP coding which works GREAT !!!

I now need to SAVE images to my SQLITE database.
I added a BLOB field type and I am able to easily SAVE and EXPORT an image file using the DB Browser application.

I found the following reference on the "sqlite.org" website which explains how to READ and WRITE an image file to an SQLITE database, however, I am having trouble getting it to work.

Click here for the link: Reference Material
https://www.sqlite.org/cli.html#fileio

Mostly confused by the terminology in the following:
"name TEXT"
"type TEXT"
"img BLOB"

sqlite> INSERT INTO images(name,type,img)
   ...>   VALUES('icon','jpeg',readfile('icon.jpg'));

Creates TABLE:
sqlite> CREATE TABLE images(name TEXT, type TEXT, img BLOB);

INSERTS JPEG image from an external file into an SQLITE database.
sqlite> INSERT INTO images(name,type,img)
   ...>   VALUES('icon','jpeg',readfile('icon.jpg'));

EXPORTS an image from an SQLITE database 
sqlite> SELECT writefile('icon.jpg',img) FROM images WHERE name='icon';

I would appreciate any help I can get in coding this using PHP..

(2) By Keith Medcalf (kmedcalf) on 2021-03-11 20:35:05 in reply to 1 [link]

> I am having trouble getting it to work

Insufficient information for meaningful conversation.

Or as Spock would say "There are no common referents for meaningful discussion".

Perhaps you need a shot of penicillin.  That often cures trouble getting it to work.

(3) By Ryan Smith (cuz) on 2021-03-11 21:00:18 in reply to 1 [link]

That's a whole lot of non-information.

You may as well have said: "My car won't start, here's how I turn the key in the ignition, and this is the shape of the key I use, and it is a red key. please help me fix the problem!"

None of that is useful information. At this point all we can say is "In principle, that should work, given all the prerequisites are in place."

Since we don't know what all the prerequisites are, or if they are in place, cause you said nothing to that effect, best we can do is say "Well, it SHOULD work, no idea why it doesn't. Good luck figuring it out!"

If you want us to help figure it out, you have to give us stuff to figure out with. There's also a bunch of usual-culprits you should have checked already and can report back so we know where to start looking, like:

- Is there an error message, or does it just fail silently?
- If an error message, what is it?
- If it fails silently, how do you know it fails? Maybe the file is written "somewhere"?
- Does your version of PHP and SQLite support that writefile() UDF?
- Does it work normally, but not when you write the images? or does it never work?
- Does the path exist?
- What is the path?
- Does the path have write permissions for the PHP user?
- Can PHP normally write other files there (not via SQLite)? Have you tested this?
- etc.

That list can get very long so I will stop here and say please figure out at least those things, and eliminate every other possible problem you can think of, and then report back with exactly everything you have tried and the results of it. Maybe then, once we have puzzle pieces, can we help solve the puzzle.

Good luck.

(4) By Kees Nuyt (knu) on 2021-03-11 21:06:36 in reply to 1 [link]

The reference material says:

<blockquote>
7.3. File I/O Functions<br />

The command-line shell adds two application-defined SQL functions that facilitate reading content from a file into a table column, and writing the content of a column into a file, respectively.
</blockquote>

So, those functions are part of the SQLite shell, and are not in the SQLite library by default. The SQLite library that PHP offers will not have them either, because it doesn't need them.

You will have to use PHP functions to read the image into memory and bind it as a BLOB to your prepared INSERT statement, and the other way around for SELECT.

(5) By DMVB123 on 2021-03-11 21:12:29 in reply to 4 [link]

Thank you for the help Kees..

Do you know of any PHP sample code, examples, or tutorials that might help me get this working?

(6) By DMVB123 on 2021-03-11 21:26:20 in reply to 2 [link]

Hi Keith, 

Thank you for the reply..

I am trying to find "how" to read and write an image file and save it to an SQLITE database using PHP..

This is the starting point for me since I have never done that before.

I have working PHP code that works great and I now need to be able to read and write images to my SQLITE database, however, I am having trouble finding a working reference example, sample code, or tutorials that can guide me.

Do you know of any example code, sample code, or tutorials that can help me learn what I need to know to get this working?

(7) By DMVB123 on 2021-03-11 21:34:19 in reply to 3 [link]

Hi Ryan,

Thank you for the reply..

I am trying to find "how" to read and write an image file to an SQLITE database using PHP..

This is the starting point for me since I have never done that before.

I have working PHP code that works great with my existing SQLITE database and I now need to be able to read and write images to my SQLITE database, however, I am having trouble finding a working reference example, sample code, or tutorials that can guide me.

Do you know of any example code, sample code, or tutorials that can help me learn what I need to know to get this working?

(8) By Tim Streater (Clothears) on 2021-03-11 21:43:17 in reply to 1 [link]

1) I don't see any PHP there.

2) Why are you doing a readfile inside an INSERT?

3) Why are you giving an example using the sqlite3 CLI program? If you want to do it in PHP, do it in PHP.

Something like this:

     $sql = 'update mytable set imgdata = ? where id = ?';
     $sth = $dbh->prepare ($sql);
     $result = $sth->bindValue (1, $blobdata, SQLITE3_BLOB);
     $result = $sth->bindValue (2, $id, SQLITE3_INTEGER);
     $res = $sth->execute ();

Adjust as appropriate. I've removed checks on $result to simplify.

(9) By DMVB123 on 2021-03-11 21:51:18 in reply to 8 [link]

Hi Tim,

Thank you for the reply..

Here is all I have right now, I planned on passing the ROW ID and Field name 
via a URL as shown.

<?php

// URL Format:
// http://www.localhost/sqlite/ccc_update_image.php?iduser=1&idimage=image1

   $userid = $_GET['iduser'];
   $imageid = $_GET['idimage'];

   class MyDB extends SQLite3 {
      function __construct() {
         $this->open('ccc_contents.db');
      }
   }
   
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully\n";
   }

///////////////////////////////////////////////////////////////////////////////


// Need PHP code to READ and WRITE to external image file and SAVE to SQLITE


///////////////////////////////////////////////////////////////////////////////

   $ret = $db->exec($sql);
   if(!$ret) {
      echo $db->lastErrorMsg();
   } else {
      echo "Records created successfully\n";
   }
   $db->close();
?>

(10) By Willem (sn0wbl1nd) on 2021-03-12 01:49:40 in reply to 1 [link]

I think what the respondents are trying to say that your question is out of scope here. You need a PhP tutorial on sqlite use, and clearly the usual suspects nor me can help you there.

It is certainly possible to store an image as a blob.

The first duckduck for "call sqlite from php blob":

  https://www.sqlitetutorial.net/sqlite-php/blob/

(11) By Kees Nuyt (knu) on 2021-03-12 08:30:52 in reply to 5 [link]

I don't have any working code and this is not the place to discuss it.

A web search for <code>"PHP sqlite database store image file tutorial"</code> returns quite a few examples. Many are about MySQL, but the mechanisms are mostly the same.

A PHP forum or usenet group would be a better place to ask.

(12) By Ryan Smith (cuz) on 2021-03-12 08:40:42 in reply to 7

> I am trying to find "how" to read and write an image file to an SQLITE database using PHP.

Lead with that next time. :)

I'm with Kees and Willem - it's a PHP question, not an SQLite question and the suggested searches should sort you out.

Good luck!

(13) By Trudge on 2021-03-12 16:09:47 in reply to 1 [link]

I've done several personal and client web sites using SQLite and MySQL / mariadb that need to store images, but I have always found from research and personal experience that it is better to save the PATH to the image in the database, rather that the actual image. For one thing you can't really 'search' an image - only whatever tags are placed on it. And it's usually better to let the OS handle files.