SELECT duplicates Q

MySQL database
#1

Hello everyone,

I will present the simplest problem I can think of for a bigger problem I have.

I have three tables:

tbl_items:
[table]
[tr]
[td]id[/td]
[td]item_name[/td]
[td]item_size[/td]
[/tr]
[tr]
[td]1[/td]
[td]iPad1[/td]
[td]7"[/td]
[/tr]
[tr]
[td]2[/td]
[td]iPad3[/td]
[td]7"[/td]
[/tr]
[tr]
[td]3[/td]
[td]GalaxyTab[/td]
[td]7"[/td]
[/tr]
[/table]

tbl_brands:
[table]
[tr]
[td]id[/td]
[td]brand_name[/td]
[/tr]
[tr]
[td]1[/td]
[td]Apple[/td]
[/tr]
[tr]
[td]2[/td]
[td]Samsung[/td]
[/tr]
[/table]

tbl_item_brands_assignment:
[table]
[tr]
[td]id[/td]
[td]item_id[/td]
[td]brand_id[/td]
[/tr]
[tr]
[td]1[/td]
[td]1[/td]
[td]1[/td]
[/tr]
[tr]
[td]2[/td]
[td]2[/td]
[td]1[/td]
[/tr]
[tr]
[td]3[/td]
[td]3[/td]
[td]2[/td]
[/tr]
[/table]

I want to select data and print it in the following way:
[table]
[tr]
[td]#[/td]
[td]Item Name[/td]
[td]Item Brand[/td]
[/tr]
[tr]
[td]1[/td]
[td]iPad1,iPad3[/td]
[td]Apple[/td]
[/tr]
[tr]
[td]1[/td]
[td]GalaxyTab[/td]
[td]Samsung[/td]
[/tr]
[/table]

Basically, for all duplicates I want them printed separated by commas (,) instead of having many rows.

Thanks!

#2

The separated by comma is the tricky part, not sure how to do that in a query, below will give you all the rows your looking for.

[php]Select a.id, a.item_name, b.brand_name from tbl_items a, tbl_brands b, tbl_item_brands_assignment c where a.id = c.id and c.brand_id = b.id order by b.brand_id[/php]

From that query you can then cycle through the results because they are sorted by the brand ID and then you can produce the results you want using PHP. But I can’t think of a way to get the results you want from a query, I think you’ll going to have to use a combination of the query above and a little php logic in a loop to produce the results you want.

#3

I already got the query right to select all the results I wanted, needless to say it was huge :stuck_out_tongue:

Anyhow, the problem is that I get 1 row per cell in the array i’m storing the data in so:

Array[0] => 1,iPad1,Apple
Array[1] => 2,iPad3,Apple
Array[2] => 3,GalaxyTab,Samsung

Could you help with using this kind of array for example to get to my desired results?

#4

This is a good challenge I think you can do it this way in the MySQL query…

[php]SELECT b.brand_name, group_concat(a.item_name SEPARATOR ’ ,’) item_name
from tbl_items a, tbl_brands b, tbl_item_brands_assignment c where a.id = c.id and c.brand_id = b.id
GROUP BY b.brand_name;
[/php]

This is untested and might need to be tweaked a little.

#5

Didn’t work, however, I did get interesting result. Is it possible you could help me via TeamViewer?

#6

Post the interesting results.

#7

Well, since I know I did all the correct checks when inserting data I divided the query into two and used group_concat along with group by.
As I said before I gave this sample database so it breaks a bigger problem down to a smaller one, easier to understand. I’ll post the solution anyhow:

[php]
$query = “SELECT items.id,items.item_name,departments.id,GROUP_CONCAT(departments.department_name),departments.glyphicon
FROM items,departments,item_department_assignment
WHERE items.id=item_department_assignment.item_id
AND departments.id=item_department_assignment.department_id
GROUP BY items.id”;
$query1 = “SELECT items.id,categories.id,categories.category_name,sub_categories.id,sub_categories.sub_categories_name,items.inventory,items.order_limit,items.ordered
FROM items,categories,sub_categories,item_category_assignment,item_sub_category_assignment
WHERE items.id=item_category_assignment.item_id
AND items.id=item_sub_category_assignment.item_id
AND categories.id=item_category_assignment.category_id
AND sub_categories.id=item_sub_category_assignment.sub_category_id
GROUP BY items.id”;[/php]

Thanks for you help :slight_smile:

#8

That’s awesome, I never used that command before, just read about it. Thanks for sharing.

Sponsor our Newsletter | Privacy Policy | Terms of Service