PHP: Inserting Blob Image to SQLite table

Question

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?

Answer 1

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.