Mysql select distinct multiple columns loop through results

Question

I'm trying to select categories without duplicates from 2 different columns, the table looks something like this:

cuisine_1    cuisine_2
----------------------
italian       french
italian       chinese
japanese      german
western
french        german
international
western       sushi
sushi         steak
steak
chinese
vietnamese    chinese

Expected result is every category only once where it doesn't matter if it came from cuisine_1 or cuisine_2

I tried like so:

$categories=mysqli_query($link,"select distinct cuisine_1,cuisine_2 AS cuisine_unique from table");


while($row_cu=mysqli_fetch_array($categories)){
       echo $row_cu['cuisine_unique']."<br>";
    }

Without any luck so far. Do I have to group the results first, or what am I doing wrong ?

tried to put these two in a single array und use array_unique() on it? — UnskilledFreak
– UnskilledFreak, Commented Nov 12, 2014 at 18:38
possible duplicate of MySQL: Get unique values across multiple columns in alphabetical order — Paul
– Paul, Commented Nov 12, 2014 at 18:42
You probably want a union as MamaWalter is suggesting. Distinct will get you distinct rows, not columns. This means that a single column may repeat because all columns in a row have to be unique for the row to be unique which is what you are seeing here. The combination of column 1 and column 2 is unique. — Jonathan Kuhn
– Jonathan Kuhn, Commented Nov 12, 2014 at 18:46

MamaWalter · Accepted Answer · 2014-11-12 18:55:23Z

2

You can try with UNION:

(SELECT distinct cuisine_1 AS cuisine_unique FROM table) UNION (SELECT distinct cuisine_2 FROM table)

SqlFiddle

edited Nov 12, 2014 at 18:55

answered Nov 12, 2014 at 18:43

MamaWalter

2,1131 gold badge19 silver badges27 bronze badges

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

Comments

Gerardo Charles Rojas Vega · Accepted Answer · 2014-11-12 18:42:31Z

0

The problem is if you filter by cuisine_1 you'll be losing some data from the other column, so you can try by getting one SELECT DISTINCT for each column and then merge both arrays and filter by array_unique

answered Nov 12, 2014 at 18:42

Gerardo Charles Rojas Vega

6237 silver badges16 bronze badges

Collectives™ on Stack Overflow

Mysql select distinct multiple columns loop through results

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related