[RESOLVED] SELECT multple unique results with one query?

[Hendricus]

Hi,

First of all best wishes and a great 2007. To some it's 2550 or 5767 or... funny, Western people like me sometimes think we're so advanced but other cultures are centuries ahead of us 😃 oh well best wishes anyway 🙂

I'm not sure what the correct name is of what I want to do so it's hard finding an answer anywhere.

Each product entry in my db table falls into categories, some entries fall into multiple categories. What I want to do is select the newest 6 products from unique categories.

Let's say I've got 10 categories;

Product A falls into category 1. The datafield product_cat will say: [tab]1;[tab]
Product B falls into category 1 & 2. The datafield product_cat will say: [tab]1;[tab]2;[tab]
Product C falls into category 3 & 1. The datafield product_cat will say: [tab]2;[tab]1;[tab]
Product D falls into category1. The datafield product_cat will say: [tab]1;[tab]
Product E falls into category 4. The datafield product_cat will say: [tab]4;[tab]

And so on...

Now I want a query (if possible), that will select product A (1), product B (2), product C (3), SKIP product D as product A already falls into (1) and product E (4) and so on. And then based on their dates DESC. So if product D was added later than product A the D has to be selected.

To make things even more complicated. I made a php function to return 6 random categories to pick from the 10. So Mysql has to select products form those 6 randomly picked categories.

Any ideas, clues... urls where to look for an answer...

[dougal85]

its a bit complicated trying to imagine the database from what you've said. If you can do a DESCRIBE on the table and let us see the structure.

You will need something like this (i think);

SELECT DISTINCT productname, category FROM tablename GROUP BY productname

depending on how many tables and the structure you will need to select the right JOIN type in the query too

[Hendricus]

Hi Dougal & others,

Well it's quite a big table. But I'll list the fields that matter:

autoID int(11) PRI NULL auto_increment
product_naam tinytext

product_categorie tinytext

product_status int(11) 0

product_last_mut datetime 0000-00-00 00:00:00

What i want to do is possible using multiple selects but I thought maybe this is too much work for the db and possible there is a better way of doing it.

Like I said earlier I have a php function that returns 6 unique random numbers (categories) out of the 10 possible (categories). Mysql has to go round and collect the productnames of the products that fit the categories.

Let's say the random function returns: 1, 3, 5, 6, 7, 10. So an easy way of doing this would be:

SELECT product_name FROM products WHERE product_status < '3' AND product_categorie LIKE '% 1; %' ORDER BY product_last_mut DESC LIMIT 0,1
SELECT product_name FROM products WHERE product_status < '3' AND product_categorie LIKE '% 3; %' ORDER BY product_last_mut DESC LIMIT 0,1
SELECT product_name FROM products WHERE product_status < '3' AND product_categorie LIKE '% 5; %' ORDER BY product_last_mut DESC LIMIT 0,1
SELECT product_name FROM products WHERE product_status < '3' AND product_categorie LIKE '% 6; %' ORDER BY product_last_mut DESC LIMIT 0,1
SELECT product_name FROM products WHERE product_status < '3' AND product_categorie LIKE '% 7; %' ORDER BY product_last_mut DESC LIMIT 0,1
SELECT product_name FROM products WHERE product_status < '3' AND product_categorie LIKE '% 10; %' ORDER BY product_last_mut DESC LIMIT 0,1

I use LIKE '% 1; %' because a product can fall into multiple categories, the product_categorie is tab and semicolon spaced, so it can be like this: " 1; 2; 4; 6; "

Problem with the above SELECT procedure is that the SELECTS in theory could return one and the same product for each SELECT if the product happens to fall into product_categorie 1, 3, 5, 6, 7 and 10.

A solution to this would be to have 6 queries. The first one returning the product_name. Then in the second SELECT having the statement WHERE product_name != '$product_name_of_first_query'. But doing 6 queries is rediculous...

Any ideas?

Cheers

[Roger_Ramjet]

Well the first thing is to redesign your table schema. You should not be storing multiple categories in the one column. Instead you should have a sub-tabble that links products and categories: each row in it would list one product and one category, so products would have multiple rows if they had multiple categories. This is how you normalise your data properly.

table prod_cat
pid, int, FK to table products
cid, int, FK to table categories

Now you can index the columns and tables properly and not have to mess around with LIKE '%7%' etc.

Now, as tro your main problem - cannot think of a single way that you could do this in 1 query. Well, I can think of 1 way but it would require nesting subqueries 5 deep and even then I'm not sure it would work. I'm not sure I could even write the thing.

So, ask yourself if this was a good idea in the first place - is it really essential that you get these products for random categories? If it is then you are just going to have to run multiple queries.

[stolzyboy]

however you decide to get the results... if you have multiple of the same... simply put the results into an array(), then use array_unique() to not show duplicates, then you can jump thru that array in whichever fashion you want

hth,
stolzyboy

[Hendricus]

Hey Stolzyboy,

Yeah I've been thinking about that. Just SELECT the last 20, 30 or 40 or so ROWS and then unique them and only echo the first 6... changes are slim that it will not return 6 unique products... I reccon this will be less work for the db than doing multiple queries right?

Thanx all

[Roger_Ramjet]

The trouble with using unique in any solution is that the prod-cat combination tends to be unique in every case, this is the problem with using DISTINCT in a select query: to get the prod id you have to include it in the query results. The same is going to happen in array_unique; even if you use a multi-dimension array, which you have to to get the prod-cat combination, each entry for a product or a category is going to be made unique when in combination. You cannot use the prod-id as the array index for the category as it will be duplicated, likewise the cat id.

If this is something you are going to try and do often then my solution would be to have a separate table that stores the latest prod for each cat, and update it every time you update the products table. It will be far easier to just determine which single entry to replace than to determine which 6 from 10 are the required entries. You can build the table manually to start with, then every time you add a product just choose one of its categories and update the prod-id for that category in the 'latest' table.

[Hendricus]

Hey Roger,

That last bit sounds like a pretty good idea! Why didn't I think of that 🙂
What I'll do is when the client adds a new product to SELECT all items of that seperate table, oldest on first, match the cat-ids of the newly added product against the cat-id of the items in the seperate table and thus replace the oldest one with a new one. This way I can show the 6 newest products from random categories on the frontpage.

Thanx all!

(I'll "Mark Thread Resolved" tommorrow if there aren't any better ideas)