grouping 'duplicated' mySQL entries during output

sprocket

If anyone could help me solve this problem, I'd appreciate it -

I have many items in a mySQL table, some unique, some identical items but differing in vendors, price, locating URL etc, hence the need for a 'family'. Now, I have a simple mySQL call for displaying these items, but I need to collate the 'family' entries into one displayed item, so that I can have a kind of 'offered by vendor x,y,z for price a,b,c' added to it.

I can flag 'duplicate' table entries with a connector to all the other similar items, but how can I get mySQL to use this to group them as one item?

The only way I can see is during the mySQL while() call, if the item is flagged as having a family, go and retrieve the family (and flag them as being retrieved so that when the parent mySQL call reaches one of the family members, it doesn't go and find it's family since it has already been found). This is not very desirable since the parent mySQL call retrives a few thousand entries, and a child call will generate hundreds of SQL hits in a very vrief time.

If anyone can understand my predicament, is there a simple alternative?

zhabala

If it is in one table, it is not so good, but can be solved.

It depends on how the display should look like. If you would like to see table like

Item name Vendor Price
Computer HP $100
IBM $200
Compaq $50
Printer HP $50
IBM $75

You can do it in one while loop, when selected rows will be sorted by Item name and then in each loop you will compare present Item name with Item name from previous cycle and when it is equal you do not print item name, otherwise you print Item name.

Hope it helps

Zdenek

sprocket

Thanks, but it won't work for a number of reasons. I'm actually sorting my SQL query on price, so that display goes cheapest->more expensive and am looking for a type of 'comparison shopping' type thing, to highlight the cheapest vendor. I could sort on name, but I have implimented a 'show items < $150' type of thing. I need to have a sort of flag in the table to show an item is in a family, or else some of the identical items, eg >$150 would not get grouped.

Also, some items have identical names, but are from different manufacturers, so are not the same.

I am willing to impliment more than 1 table to incorporate this, but I don't see what benefits it would have. Perhaps a 2nd table listing all the families, and during the first while loop, check the family table to see if the item is present. If it is, then go fetch all the other family members. But then how do I skip those fetched members when they come up in the first while loop?

My items table is basically:

id
name
features
description
manufacturer
vendor
url
price
family

where at the moment, family contains the id's of all the other idenical products, but from different vendors. I think my easiest way out is to have vendor, url and price fields use some form of comma-delimited array which will store all the family members in one table entry, but I would really like to keep the families in separate separate table entries if possible.

sprocket

I guess maybe this 2 table solution is the only solution:

table1:

id
family_id
name
manufacturer
description
lowest_price

table2:

family_id
vendor
price

table1["family_id"] would be a comma delimited list of all the table2["family_id"] entries for the family, and table1["lowest_price"] would be the lowest price found from table2["price"] for all the family members.

Oh well, here's to the next week of recoding my data entry code 🙁

zhabala

It is basically bad idea.

It should look like:

table 1

id
name
manufacturer
description
lowest_price

table2:

id
table1_id
vendor
price

where table2["table1_id"] contains value of table1["id"]

then you can do combined select:

SELECT * FROM table1, table2 WHERE table1.id = table2.table1_id AND some others.

But because I do not understand correctly, there maybe a solution with three tables:

Family table - which will contain informations about families
Item table - which will contain informations about items belonging to families
Vendor table - informations about vendors providing some item

maybe even more when you would like to have unique vendors in one table and link between this table and item table as another table.

Zdenek