3 tables, product, language, several images [beginner]

[webbhelp]

Hi!

 

I got 3 tables!

 

1 with the product,

1 with the translated titles, descriptions.

1 with the images to each products.

 

I have to select all products by category and then get all the products in a specific category where I the language is (id), so I can get the right translated titles, and descriptions.

This works, I got all the products with each translations. but now I have too add the third table... the images.

one product, got 1 title, and description, so it was no big deal to get it. but every product can have a lot of images.

 

So the question is how I can get the images too. Is this even possible to do with a join too? I mean don't I have to make another query for this? and loop them out in every product? it seems to be bad performance thinking if I do so?

SELECT `products`.*, `products_lang`.`title`, `products_img`.`id` AS imgid, `products_img`.`imgurl`, `products_img`.`product` AS imgproduct FROM (`products`) 
INNER JOIN `products_lang` ON `products_lang`.`product` = `products`.`id` 
INNER JOIN `products_img` ON `products_img`.`product` = `products`.`id` WHERE `products_lang`.`language` = '3' AND `products`.`category` = '3' AND products_img.product = '3'

I did something like this first, then I came up with the question if it is even possible to create a third join with the products_img table.

 

Please give me a hand with this one!

 

Thanks in advance =)

[Barand]

You can have as many joins as you need.

 

What is going wrong?

[webbhelp]

okey, yes I can. but the wrong here is that if I have 4 pictures connected to a product, the product loops out 4 times.

 

So if a product got the name iphone the result is:

 

iphone img1

iphone img2

iphone img3

iphone img4.

 

And I don't get it, if I loop my products result and have it like this:

 

iphone - translations - images

samsung - translations - images

 

how can I loop the images out when iphone only echo:es in one loop-iteration.

I don't really know how to explain my question in a good way here but...

 

Loop-iteration 1:

echo $p->title; echo $p->price etc, and then echo $p->img (That is just one img, in this one, right?) where is the other 3 images?

 

Do you understand my question? =)

[Barand]

That's how joins work, appending selected data from each matching record to the records from the other table. The trick is to process the product data only when the value changes.

 

Is the case that you have this for each product return by the query:

 

1 product rec  --- 1 lang rec --- several image recs

[webbhelp]

Sry I didn't understand this line:

"The trick is to process the product data only when the value changes."

 

and this is correct: "1 product rec  --- 1 lang rec --- several image recs"

so how do you mean, how can I echo the result, if there is only one product row, and a several of img rows?

[Barand]

You do it like this. Because you have used "product.* " which is bad practice, I don't know what you are selecting from that table so i have just referred to it as "product_stuff"

$db = new mysqli(HOST, USERNAME, PASSWORD, DATABASE);

$sql = "SELECT `products`.*
    , `products_lang`.`title`
    , `products_img`.`id` AS imgid
    , `products_img`.`imgurl`
    , `products_img`.`product` AS imgproduct 
FROM (`products`) 
    INNER JOIN `products_lang` ON `products_lang`.`product` = `products`.`id` 
    INNER JOIN `products_img` ON `products_img`.`product` = `products`.`id` 
WHERE `products_lang`.`language` = '3' 
    AND `products`.`category` = '3' 
    AND products_img.product = '3' ";

$res = $db->query($sql);

$current_prod = '';
while ($row = $res->fetch_assoc()) {
    if ($row['id'] != $current_prod) {          // has product changed?
        echo "{$row['title']}<br>{$row['product_stuff']} ";
        $current_prod = $row['id'];            // reset current_prod value
    }
    echo "<img src='{$row['imgurl']}' /><br>";
}

[webbhelp]

Aha! now all makes sense!

I have actually never seen this, never been in touch with this problem.

 

Okey but this was actually easy, no advanced solution I mean.

Really good knowledge to have, I thank  you for this =)

 

you mean that products.* is bad practice, because the fields might have same name in another table, as some of them actually have (id-field), and I shouldn't fetch things I don't need.

True, I will change that!

 

Thank you =)

[Barand]

 

you mean that products.* is bad practice, because the fields might have same name in another table, as some of them actually have (id-field), and I shouldn't fetch things I don't need.

 

 

Exactly. Only get what you need. Plus, if you (or someone else) come back to that query in future, it's better documented if you can see exactly what is getting and from where.

 

You are already selecting the product id twice (once from product table and also products_img.product (which will be a match of the product.id)

[webbhelp]

True words!

 

Thank you for all help! I really appreciate that!