CODEWITHSUNDEEP
phpsqliteblob
Mohamed Emad Hegab
Mohamed Emad Hegab

Reputation: 2675

PHP: Inserting Blob Image to SQLite table

i'm trying to insert an image using (cakephp) to sqlite DB in a BLOB column.

i'm using this code 

$insert = "INSERT INTO story (page, image) 
                    VALUES (:page, :image)";
        $stmt = $file_db->prepare($insert);

        // Bind parameters to statement variables
        $img = file_get_contents($this->request->data['image']['tmp_name']);
        $img = mysql_real_escape_string($img);
        $stmt->bindParam(':page', $this->request->data['page']);
        $stmt->bindParam(':image', $img);

           // Execute statement
          $stmt->execute();

and it's inserting but a characters set not a blob data as it suppose to be.. is there any other way to do it or else what is the wrong with my code?

Upvotes: 2

Views: 10084

Answers (2)

georgiecasey
georgiecasey

Reputation: 23391

This is an answer to the question in the title, as it ranks #1 for [php sqlite blob]. This doesn't use PDO as I had trouble with it. You have to use prepared statement, you usually can't execute a literal SQL insert as the size is too big with a blob. This is an update rather than an insert but it's basically the same.

// example variables
$row_id=1;
$image_filename="/home/mywebsite/public_html/images/an_image.png";
$sqlite_table_name="user_images";
$database_filename="database.sqlite";
// the blob field in the sqlite table is called ZIMAGE and the id field is ZID.

// Code
if (file_exists($image_filename)) {
    $db = new SQLite3($database_filename);
    $query = $db->prepare("UPDATE sqlite_table_name SET ZIMAGE=? WHERE ZID=?");
    $image=file_get_contents($image_filename);
    $query->bindValue(1, $image, SQLITE3_BLOB);
    $query->bindValue(2, $row_id, SQLITE3_TEXT);
    $run=$query->execute();
}

I don't think there's any hard rules in programming, I store images in blobs in Sqlite in certain situations. I also scrape webpages with regular expressions instead of Xpath! Whatever gets the job done.

Upvotes: 8

thaJeztah
thaJeztah

Reputation: 29097

This is not an answer, but there's 'unclarity' in this question, too long to put it as a comment

  1. You mention CakePHP, but there's no CakePHP involved in your question
  2. You mention 'SQLite', but you're using mysql_real_escape?

Please make clear what database you intend to use.

Regarding MySQL and storing images inside the database;

  • mysql_ functions in PHP are deprecated and no longer maintained
  • try to determin if you really need to store your image data inside the database, generally, it's better to store the image itself in the file system and the name of the image in the database

Read this question for an answer on inserting images in mysql:

Insert Blobs in MySql databases with php

Read this question for an answer on inserting images in SQLite (Using PDO):

Remote image file to sqlite blob in PHP?

Here is a generic PDO step-by-step walk through for BLOBs;

http://www.phpeveryday.com/articles/PDO-Working-With-BLOBs-P554.html

Upvotes: 2

Related Questions