Querying associated images in one table after querying products from another

Question

I used this code to connect to a database and fetch results. This worked perfectly until i tried to work in another query to the images table to get associated images. I'm not very experienced with OO programming. So hopefully someone can see where ive gone wrong and help me out.

<?php 
    global $__CMS_CONN__;

    $sql = "SELECT * FROM ecom_products";
    $stmt = $__CMS_CONN__->prepare($sql);
    $stmt->execute(array($id));

    while ($row = $stmt->fetchObject()) {

        $imagesql = "SELECT * FROM ecom_product_images where id = $row->id && where primaryImage = '1'";
        $imagestmt = $__CMS_CONN__->prepare($sql);
        $imagestmt->execute(array($id));
        $imageName = $imagestmt->fetchObject();

        echo '<a href="'.URL_PUBLIC.$row->id.'">'.$row->productNm.'</a>'.$imageName;

    }
?>

Answer 1

Modify your SQL query:

$imagesql = 'SELECT * FROM ecom_product_images where id = ' . $row->id . ' AND primaryImage = "1"';

Have you declared before $id variable ?

Have you got any errors with your code ?

If your primaryImage column is int type then skip apostrophes in query

primaryImage = 1

if enum then it is ok.

Answer 2

You need not OO programming, but SQL one.

SELECT p.*, imageName 
FROM ecom_products p, ecom_product_images i 
WHERE p.id = i.id AND primaryImage = 1;