CODEWITHSUNDEEP
phpmysqlsqljoin
weng tee
weng tee

Reputation: 392

fetching images from multiple tables

I am building a small online shop where I have a parent table called 'products' and a child table called 'images'.

I have followed best practice and set up a foreign key constraint with the field product_id establishing a link between both tables.

products 

product_id (PK parent table)
product_title
product_category_id
product-price
product_quantity
product_description
long_description
product_image

images

image_id (PK for child table)
product_id (foreign key)
product_image_two

NB: Each product will have 2 images, thus I want to retrieve a product based on its product_id and get the associated images from each table.

ie. the query pulls product_image from 'products' and product_image_two from 'images'

I have trawled through a multitude of posts on here about JOIN and tried to refactor other folks code so far without success.

My Current Statement

<?php

$query = query("SELECT p.* , i.* FROM products p,images i WHERE p.product_id=i.product_id"); 
confirm($query);  

while ($row = fetch_array($query)): 


?>

Upvotes: 0

Views: 540

Answers (2)

Barry Piccinni
Barry Piccinni

Reputation: 1801

It sounds like what you want is a LEFT JOIN. Using a LEFT JOIN, you can select everything in the product table but only rows from the images table if their corresponding key is present in the products table. So for example, your query could look like:

SELECT p.* , i.* 
FROM products p,
LEFT JOIN images i ON
  p.product_id = i.product_id

This will return every row in the products table, and a value of null for each column in the images table if no second image exists. Here is a simplified demo of what this does: SQL Fiddle

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

Try with inner join: according to your explanation it should work

SELECT p.product_id,p.product_image, i.product_image_two FROM products p
inner join images i on 
p.product_id=i.product_id

Upvotes: 0

Related Questions