I have an uploads table, containing image filenames, and titles (used for alt text); a catalogue table, containing product ids, descriptions, image ids etc; a categories tables, containing id, name; a sections table, containing id, name; and two lookup tables: cataloguesections, and cataloguecategories.
I need to generate a query which will provide me with one image for each category in a section. The image needs to be from the first product that would appear in that category, ordered by product id.
Here’s what I’ve got so far:
SELECT `categories`.`name` AS `name`,
`uploads`.`title` AS `alttext`,
`uploads`.`filename` AS `image`,
`bgcolor`,
`color`,
`catalogue`.`id` AS `id`
FROM `categories`
INNER JOIN `cataloguecategories` ON `categories`.`id` = `cataloguecategories`.`categoryid`
INNER JOIN `catalogue` ON `catalogue`.`id` = `cataloguecategories`.`catalogueid`
INNER JOIN `cataloguesections` ON `catalogue`.`id` = `cataloguesections`.`catalogueid`
INNER JOIN `sections` ON `sections`.`id` = `cataloguesections`.`sectionid`
LEFT JOIN `uploads` ON `catalogue`.`image` = `uploads`.`id`
WHERE `sections`.`name` = '$section_name'
GROUP BY `categories`.`id`"
This fine, except that the image returned is not necessarily from the first product by product id, since the query does not order the catalogue ids at any point.
I tried adding ORDER BY catalogue.id at the end of the query, but that throws an error.
I’m sure I need to use a temporary table but I can’t quite get my head round it at the moment.
each section has multiple catalogues, and each catalogue has multiple products?? how is this relationship implemented? i don’t see a product tabler or product ids being used anywhere
then, each catalogue has multiple categories? so even if we find one image per catalogue, how does the image relate to the category?
perhaps you could do a SHOW CREATE TABLE for each table to clear up some of this…
It would probably help if I gave some context. The database holds data about educational resources.
There are 12 categories in total: Books and DVDs, Listening Resources, Vocabulary Games… etc.
There are 2 sections: Key Stages 1 and 2, Key Stages 3 and 4.
Each product can belong to many categories, and many stages.
‘Selective Mutism’ for example is a book which is appropriate for both sections, and resides in two categories.
When a user is browsing the online shop, they first land on a page where they can select between sections (Key Stages), then they can decide which category they want to browse. Key Stages 1 and 2 > Books and DVDs will contain some different products to Key Stages 3 and 4 > Books and DVDs, and some that are the same.
At the moment the following query will do a fairly good job:
SELECT `categories`.`name` AS `name`,
`uploads`.`title` AS `alttext`,
`uploads`.`filename` AS `image`,
`bgcolor`,
`color`,
`catalogue`.`id` AS `id`
FROM `categories`
INNER JOIN `cataloguecategories` ON `categories`.`id` = `cataloguecategories`.`categoryid`
INNER JOIN `catalogue` ON `catalogue`.`id` = `cataloguecategories`.`catalogueid`
INNER JOIN `cataloguesections` ON `catalogue`.`id` = `cataloguesections`.`catalogueid`
INNER JOIN `sections` ON `sections`.`id` = `cataloguesections`.`sectionid`
LEFT JOIN `uploads` ON `catalogue`.`image` = `uploads`.`id`
WHERE `sections`.`name` = '$category'
GROUP BY `categories`.`id`
The problem is the order of the categories needs to be preserved, so that disrupts the order of the catalogue ids.
i hate to be a real stickler, but i wanted the result of the SHOW CREATE TABLE statements
SHOW CREATE TABLE will produce, as you might guess, the CREATE TABLE statement for the table
if i had the CREATE TABLE statements, i could run them into my local testing mysql database, and easily build a query from those tables using my front end interface utility
as it is, i would have to read off your table layouts and re-type everything, and today i’m just too lazy to do that
I’ve actually updated the site using a workaround in PHP, so the site behaves as it should right now, but I’d rather do this with MySQL if I can.
Resources for Foundation Stage and Key Stages 1 and 2 is the section, Listening Resources is the category.
Catalogue contains all the products.
Uploads contains all the images and pdfs uploaded by the user. The catalogue has a row image which stores the uploads.id, so the upload (image in this case), can be associated with the product.
The product code is stored in the table as id… It should probably rename that product_code or something similar…
The query, because of the amount of many-to-many relationships there are will bring back multiple results for each category. I just need one result for each category: categories.name, categories.bgcolor, categories.color, uploads.filename AS image, uploads.title AS text, WHERE uploads.filename is the image associated with the first product in that section and that category sorted alphabetically by product code (catalogue.id). That’s why I added the GROUP BY clause.
don’t change any of the column names, as you are now used to them
i don’t see the many-to-many problem yet, but give me time
you said “Resources for Foundation Stage and Key Stages 1 and 2 is the section, Listening Resources is the category” and you also said “I just need one result for each category” and yet when i look at that sample page i see a whole bunch of catalogue products down the page…
http://languageforlearning.co.uk/shop/Foundation-Stage-and-Key-Stages-1-and-2 displays the categories. I need an image for each category. There are 12 categories, so I need a result set of 12 rows, each containing the filename of the image associated with the first product that would appear that category, when ordered alphabetically by product code (catalogue.id).
However, there are also 2 sections. ‘Foundation Stage and Key Stages 1 and 2’, and ‘Key Stages 3 and 4’… The images will be different for each category pages, since the products in the first section will not (necessarily) be in the second section.
It belongs in the Books and DVDs category, but it also belongs in the Assessments category. It is suitable for Foundation Stages and Key Stages 1 and 2, so it is associated with that category, but it is also suitable for Key stages 3 and 4, so it is associated with that category as well. So it appears 4 times throughout the shop.
Since both sections contain all 12 categories, there’s no real need for them to have a relationship in the db, but they need different images when the categories are displayed, depending on which section the customer is browsing at the time. I associate the product BK8 to the categories and sections via look up tables (cataloguecategories and cataloguesections).
It belongs in the Books and DVDs category, but it also belongs in the Assessments category. It is suitable for Foundation Stages and Key Stages 1 and 2, so it is associated with that category, but it is also suitable for Key stages 3 and 4, so it is associated with that category as well. So it appears 4 times throughout the shop.
That should read: It belongs in the Books and DVDs category, but it also belongs in the Assessments category. It is suitable for Foundation Stages and Key Stages 1 and 2, so it is associated with that section, but it is also suitable for Key stages 3 and 4, so it is associated with that section as well. So it appears 4 times throughout the shop.
i am now thoroughly confused, and i think you should consider a slight redesign of your tables
there is no relationship between sections and categories, and i think you need to build that table
then, cataloguecategories makes sense (it would make more sense if named categorycatalogues, but that’s a quibble), so that the products can be tied to the correct category in a section
catalogue sections makes no sense, unless you want to tie products to a section without those products belonging to a category in that section, which i doubt, so you can delete it
only after these changes would i attempot to write your queries
