Is my DB design correct for one product for multiple images?

I am working on an ecommerce project where user can upload multiple images(3 max) and then on my home page i will display one product img per categories.So i have design a db for the images,articles and categories. HERE IS MY DB

categories
[php]catid (int primary auto increment)
catname (varchar)[/php]

userarticles

[php]id (int primary auto increment)
user_id (index)
idcat
nomarticle
price[/php]

article_imgs

[php]imageid (int primary auto increment)
articleID (index)
filenames[/php]

I got to inseting the uploaded images into the db correct. But now when i query the db for displaying one image for each
article(cause each article has its own id) …instead of one image, all the images are coming

HERE IS MY QUERY

[php]
SELECT id,nomarticle,price,filenames FROM userarticles AS product INNER JOIN article_imgs AS img ON img.articleID = product.id WHERE product.id = 2[/php]

DB design looks OK. You will need to use LIMIT 1

How can edit my question please ? i have looked for link cannot find

You can’t until you have posted here for a bit.

Then i posting the big challenge i having. From the deign tables i did. I got the following :
[php]categories[/php]
[php]catid catname
1 vêtements
2 chaussures
3 accessoires[/php]

userarticles
[php]id idcat nomarticle price
1 2 Escarpin Estelle 15000
2 1 Robe Kelizee 12500
3 2 Tallon Carole 9500[/php]

article_imgs
[php]imageid articleID filenames
1 1 pexels-photo-1340642017-09-29-001618.jpg
2 1 shoess2017-09-29-001618.jpg
3 1 pexels-photo-2641722017-09-27-215812.jpg
4 2 robe-moulante-rouge2017-09-27-215812.jpg
5 2 robe-moulante-rouge2017-09-27-215813.jpg
6 3 tallon-rouge2017-09-27-215813.jpg
7 3 tallon1-rouge2017-09-27-215813.jpg[/php]

What i want to query to db to return one img of each product from the same category.
But instead all the images of the product are returning and LIMIT is not doing the job.

Here is my query
[php]SELECT id,nomarticle,price, filenames FROM userarticles AS product LEFT OUTER JOIN categories AS cat ON cat.catid = product.idcat LEFT OUTER JOIN article_imgs AS img ON img.articleID = product.id WHERE cat.catid = 1;[/php]

This is the result of the query
[php]id nomarticle price filenames
1 sac channel 16000 accessoires-sac2017-09-26-174740.jpg
1 sac channel 16000 sacs-chanel2017-09-26-174740.jpg
2 Robe Channel 5000 pexels-photo-2641722017-09-27-215812.jpg
2 Robe Channel 5000 pexels-photo-2641722017-09-27-215812.jpg
2 Robe Channel 5000 robe-moulante-rouge2017-09-27-215812.jpg[/php]

But me i want to return one article per categorie with one image of that article.

This the result i am looking for
[php]id nomarticle price filenames
1 sac channel 16000 sacs-chanel2017-09-26-174740.jpg
2 Robe Channel 5000 robe-moulante-rouge2017-09-27-215812.jpg[/php]

I dont see LIMIT in your query. Post an SQL dump of your DB with a handful of sample records

[php]GROUP BY cat.catid[/php]

I need to comment on this. There are a lot of guidelines and conventions on structure and naming. An argument could be made for different ways of doing things. But the one thing they all (?) agree on, is be consistent. And the naming in your database is all over the place.

[code]categories
catid (int primary auto increment)
catname (varchar)

userarticles
id (int primary auto increment)
user_id (index)
idcat
nomarticle
price

article_imgs
imageid (int primary auto increment)
articleID (index)
filenames[/code]

PK ID columns
catid
id
imageid

FK ID columns
user_id
idcat
articleID
catid
imageid

Please consider sticking to one standard, stuff like this is terrible for my CDO (OCD). I’d also look at some of the naming, ie what is “nomarticle”?

Sponsor our Newsletter | Privacy Policy | Terms of Service