PHP code to return image from database

Question

I am fairly new to php and am having some trouble trying to get my images to populate correctly from my database. Any help would be appreciated.

Below is my current code. It is currently returning my default image and not my product image if in the database.

<?php

$query = "SELECT products.giftID, products.gift_name, products.price, products.short_description, product_image.picture FROM products LEFT JOIN product_image ON product_image.giftID='products.giftID' AND products.vendorID='1'";                                 
$result = mysql_query($query) or die ("Database access failed:".mysql_error());

while($row = mysql_fetch_assoc($result)) {
    if($row['picture'] == '') {
        echo "<img src=product_img/image_coming_soon.jpg>";
    } else {
        echo "<img src='".$row['picture'] ."'>";
    }                                       
}
?>

Formatting the query for readability:

SELECT products.giftID, products.gift_name, products.price,
       products.short_description, 
       product_image.picture
  FROM products
  LEFT JOIN product_image ON product_image.giftID='products.giftID'
                         AND products.vendorID='1'

Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Jan 21 '16 at 20:12
To troubleshoot, what's the actual value of `$row['picture']`? — showdev, Jan 21 '16 at 20:14
Then it would appear that `$row['picture'] == ''` is true. Are you sure your database has the data you expect? — David, Jan 21 '16 at 20:15
it is the path to the folder containing the image. for example: product_img/image_name.jpg — TMAO, Jan 21 '16 at 20:16
If that's the case, the `else` part would be executed. Are you sure that's the value? — showdev, Jan 21 '16 at 20:17
Consult these following links http://php.net/manual/en/function.mysql-error.php and http://php.net/manual/en/function.error-reporting.php and apply that to your code. — Funk Forty Niner, Jan 21 '16 at 20:18
Yes, for example giftID 1 has a value of product_img/giftID_1_.jpg — TMAO, Jan 21 '16 at 20:18
That's contrary to the actual behavior. What I meant is, try `var_dump($row['picture'])` inside your loop to see what the actual value is (since the behavior indicates that it's a blank string). — showdev, Jan 21 '16 at 20:19
I have this same code used in another part of this file, but it is only pulling one possible image. It works correctly there. Because this is pulling multiple images, I wonder if that is the issue? — TMAO, Jan 21 '16 at 20:20
I think the problem lies here: `ON product_image.giftID='products.giftID'`. That's looking for a `product_image` where the `giftID` is a literal string of "products.giftID", rather than the product's id. That probably returns a blank string for the image. — showdev, Jan 21 '16 at 20:22
so my query is incorrect? I am trying to pull data from two tables — TMAO, Jan 21 '16 at 20:24
you should be able to tell if the query is correct by dumping out the contents of $row — , Jan 21 '16 at 20:30

score 1 · Answer 1 · answered Jan 21 '16 at 20:30

Your query joins the product_image table where product_image.giftID='products.giftID'.
I assume you want to match the numeric ID from the products table rather than a literal string.

I suggest removing the quotes from 'products.giftID':

SELECT products.giftID, products.gift_name, products.price, products.short_description, 
       product_image.picture
  FROM products
  LEFT JOIN product_image ON product_image.giftID = products.giftID
                          AND products.vendorID = 1

You are fantastic!!! That worked. Thank you so much! – TMAO Jan 21 '16 at 20:34 — TMAO, Jan 21 '16 at 20:34

score 0 · Answer 2 · answered Jan 21 '16 at 20:35

@showdev has it right. (I made this answer Community Wiki so as not to steal his credit.)

LEFT JOIN product_image   ON product_image.giftID='products.giftID' /*wrong*/
                         AND products.vendorID='1'

is no good, because it looks for a product_image row with a giftID column equal to the text string 'products.giftID'. You want this, without the single quotes in the first line.

LEFT JOIN product_image   ON product_image.giftID=products.giftID
                         AND products.vendorID='1'

Also:

Your HTML code generation is incorrect. You're getting

<img src=product_img/image_coming_soon.jpg>  <!-- wrong -->

The attribute values inside your <img ...> tags have to be surrounded by double quotes. (Some browsers, accidentally, accept other punctuation.) You want stuff like this in your html:

<img src="product_img/image_coming_soon.jpg">

So, you need

{echo '<img src="product_img/image_coming_soon.jpg">';}
else {echo '<img src="' . $row['picture'] . '">';}

You can put double quotes inside php single quotes.

PHP code to return image from database

2 Answers2