ignoring duplicate mysql values in php

Question

Once I run my query, joining several columns together, I get results like this:

id     item     category
1      item 1   A
1      item 1   B
1      item 1   C
2      item 2   A
2      item 2   D
3      item 3   B
3      item 3   E

I've got a while loop that will output each row:.

while($results = mysql_fetch_array($raw_results)) {
    echo $results['item'].<br>;
    echo "Category: ".$results['category'];
}

and it's giving this:

<div>
Item 1
Category: A
</div>

<div>
Item 1
Category: B
</div>

and so on, but what I need to do is combine all the values for each ID into one space, so the results look like:

<div>
Item 1
Category: A, B, C
</div>

<div>
Item 2
Category: A, D
</div>

<div>
Item 3
Category: B, E
</div>

What's the best way to do this? Should I create another array with the rows for each ID, and loop through that (and how would I set up the array?) Or is there something else?

bitWorking · Accepted Answer · 2013-04-22 23:10:28Z

4

try GROUP BY with GROUP_CONCAT

http://www.mysqlperformanceblog.com/2006/09/04/group_concat-useful-group-by-extension/

answered Apr 22, 2013 at 23:10

bitWorking

12.7k1 gold badge34 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lera Over a year ago

Looks like GROUP_CONCAT is what I need. I tested it on multiple columns, and it seems to be working, but to confirm that - suppose I had two more columns, say, an address and a city, so item 1 would have address1, city1; address2, city2; address3, city3. GROUP_CONCAT would always keep the order the same, returning address1,address2,address3 and city1,city2,city3?

Lera Over a year ago

To follow up a bit more (since my actual data's more complicated than my example), what if item 1 took up nine rows without grouping (three categories, all three addresses for each category)? Grouping them would either give A,A,A,B,B,B,C,C,C, or with DISTINCT it would give A,B,C, but then I'd lose the association between category and address. Can I concatenate on multiple columns?

Lera Over a year ago

The HTML output I'd want is Category: A (at addr1, city1 or address2, city2, or addr3, city3), B (at addr1, city1 or addr4, city4, or addr5, city5), C (at addr1, city1 or addr2, city2, or addr6, city6). I'm sure some of it will require code on the PHP end, but how can I best preserve the associations while keeping one row per ID?

Scotch · Accepted Answer · 2013-04-22 23:11:01Z

1

You could do this all in your query instead of relying on PHP.

  Select item, group_concat(category) FROM yourtable GROUP BY Item

answered Apr 22, 2013 at 23:11

Scotch

3,23411 gold badges41 silver badges51 bronze badges

Comments

TheEwook · Accepted Answer · 2013-04-22 23:12:33Z

1

I would do something like this :

select id, item, group_concat(category) from Table1
group by id, item

SQLFiddle

answered Apr 22, 2013 at 23:12

TheEwook

11.2k6 gold badges39 silver badges55 bronze badges

Comments

Mark Parnell · Accepted Answer · 2013-04-22 23:12:30Z

0

Create an array of categories for each item:

while($results = mysql_fetch_array($raw_results)) {
    $items[$results['item']][] = $results['category'];
}

Then use that to output your HTML:

foreach ($items as $itemName => $categories) {
    echo $itemName.'<br>';
    echo 'Categories: '.implode(', ',$categories);
}

answered Apr 22, 2013 at 23:12

Mark Parnell

9,2009 gold badges33 silver badges37 bronze badges

Comments

usumoio · Accepted Answer · 2013-04-22 23:13:26Z

0

If you do not have the ability to change the query that gets the data from the database you can use the PHP helper function array_unique. This function removes duplicate values from an array. While it is better to do this in MySQL, its not always possible for the developer to do this easily so this would help. Here is a link to the PHP manual on this function:

http://www.php.net/manual/en/function.array-unique.php

answered Apr 22, 2013 at 23:13

usumoio

3,5686 gold badges35 silver badges58 bronze badges

Collectives™ on Stack Overflow

ignoring duplicate mysql values in php

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related