Retrieving image from MySQL db using php

Question

Good day everybody.

I have searched for hours on a reson for the way php behaves with a query that I have with no avail.. any assistance would be appreciated.

I am creating a simple application where the customer will sign on a tablet and the signiture need to be stored in the db along with other order details as a proof of delivery.

The image field datatype is BLOB.

While storing an image in the database is not the best way to go, it is a part of the requirements so there is no way around it. The signature is stored correctly in the database, using MySQL workbench viewer to retrieve the data actually shows the Base64 image string, and when it is copied and placed within a <img> tag it displays correctly.

When I try to query the database to retrieve the image I get "Warning: Invalid argument supplied for foreach()" which means that I have an error in the sql statement. When I use the same query in phpMyAdmin or MySQL Workbench I get results.

The query I use is as follows:

$order_details_stmt =   $db->query("SELECT *
                                FROM dms_dl_delivery_details
                                WHERE `way_bill_number` = '$waybill_no'");



            foreach($order_details_stmt as $row3){

                        $signature      =   $row3['reciepient_signature'];
                        $invoice_book   =   $row3['reciept_book'];
                        $invoice_no     =   $row3['reciept_no'];
                        $recieved_by    =   $row3['recieved_by'];
            }

To echo the signature I use <img src="<?php echo $signature; ?>">, but I don't even get there. If I do var_dump($order_details_stmt); I get boolean false, however, all the other variables I get from the query work fine: $invoice_book, $invoice_no, and $recieved_by are echoed correctly in the output page.

the table create statement is as follows:

CREATE TABLE `dms_dl_delivery_details` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `way_bill_number` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `sender_customer_id` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
  `way_bill_date_time` datetime NOT NULL,
  `pick_up_address_id` varchar(13) COLLATE utf8_unicode_ci NOT NULL,
  `reciepient_signature` blob NOT NULL,
  `reciept_book` char(4) COLLATE utf8_unicode_ci NOT NULL,
  `reciept_no` char(4) COLLATE utf8_unicode_ci NOT NULL,
  `recieved_by` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `way_bill_number_UNIQUE` (`way_bill_number`)
  ) 
  ENGINE=InnoDB 
  DEFAULT CHARSET=utf8 
  COLLATE=utf8_unicode_ci;

I'm using PHP 5.4.12 and MySQL 5.6.12

Any thoughts on what am I doing wrong here?

Thanks!

Error

@showdev: Yes. It is PDO. The query itself returns false which is strange. The other rows I can fetch and display correctly. — MDChaara
– MDChaara, Commented Apr 11, 2014 at 22:10
Is PDO configured to display error messages / throw exceptions ? — greg0ire
– greg0ire, Commented Apr 11, 2014 at 22:11
Sorry, I deleted my comment. If the query returns false, you may be able to retrieve the error from PDO's errorInfo(): print_r($db->errorInfo()); — showdev
– showdev, Commented Apr 11, 2014 at 22:15
@showdev: I get the following:Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute. — MDChaara
– MDChaara, Commented Apr 11, 2014 at 22:23

Jacob · Accepted Answer · 2014-04-11 22:12:19Z

1

it should look like this

$result = $db->query("SELECT *
                      FROM dms_dl_delivery_details
                      WHERE `way_bill_number` = '$waybill_no'");

$order_details_stmt = $result->fetchAll();

answered Apr 11, 2014 at 22:12

Jacob

9206 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

MDChaara Over a year ago

hi. It returns nothing.

Jacob Over a year ago

as in $order_details_stmt is an empty array or you are still getting an error?

Jacob Over a year ago

if it is an empty array then you need to check the value of $waybill_no

MDChaara Over a year ago

Hi Jacob. Thanks for you help. Strawberry's code worked.

Strawberry · Accepted Answer · 2014-04-11 22:12:57Z

0

What happens if you use the prepare-execute method...

$stm = $dbh->prepare($sql);
$stm->execute();
$result = $stm->fetchAll();

foreach (

answered Apr 11, 2014 at 22:12

Strawberry

34k14 gold badges43 silver badges57 bronze badges

2 Comments

MDChaara Over a year ago

When I do that it returns "empty"

Ronni Egeriis Persson Over a year ago

It would be more memory efficient to do while($row = $stm->fetch())

Collectives™ on Stack Overflow

Retrieving image from MySQL db using php

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related