I need to insert blob content into mysql table consisting of many columns

Question

I have an issue inserting blob content into a table and retrieving it as my table consists of some other fields along with the Blob field, data is inserting properly but when i try to fetch the image is not displaying. But the same code if i use to insert into a separate table with only 2 columns of type id, image . It is retrieving correctly. // to insert i am using :

$image=addslashes(file_get_contents($_FILES['upload_file']['tmp_name'])); $image_type = $_FILES['upload_file']['type'];

INSERT INTO questions(id ,subject_id ,topic_id ,language ,question ,solution ,img_required ,image_type ,image) VALUES ( 12 , '1', '1', 'English', 'QQQQ?', 'Ans','Yes','image/jpg','BLOB-IMG001');

// to display image on screen i'm using :

$preview = $db->GetRow("SELECT * from questions where id='".mysql_real_escape_string($_REQUEST['id'])."' ");

$preview['image']= '';

But if i use 2 queries - an insert followed by an update in which the insert statement will insert the fields data followed by updating the same row with blob image. This is working fine. My question is cant i make it insert the full data in a single insert statement? Here is how i'm running it currently - 1) $id=$questions->insert($data); //followed by

2) $imgData =addslashes(file_get_contents($_FILES['upload_file']['tmp_name'])); $imageProperties = $_FILES['upload_file']['type'];

$exe=$db->Execute("update pmd_questions set image_type='".$imageProperties."' ,image='".$imgData."' where id='".$id."' ");

Senthil Senthil · Accepted Answer · 2016-09-17T07:56:59

you can do it. recommended is use prepared statements and can insert it.

    public function insertBlob($filePath, $mime) {
        $blob = fopen($filePath, 'rb'); 
        $sql = "INSERT INTO files(mime,data) VALUES(:mime,:data)";
        $stmt = $this->pdo->prepare($sql);
        $stmt->bindParam(':mime', $mime);
        $stmt->bindParam(':data', $blob, PDO::PARAM_LOB); 
        return $stmt->execute();
    }

http://www.mysqltutorial.org/php-mysql-blob/

I need to insert blob content into mysql table consisting of many columns

1 Answers