Select Distinct Values from Multiple Columns

[rstewar]

I am having trouble printing unique values from a database where 3 different fields could contain the same value.

 

Basically, I have 4 fields 'id, university_1, university_2, and university_3'.  And I have records like this:

 

 

ID    university_1      university_2      university_3

-------------------------------------------------------------------

1      Clemson            Virginia              California

-------------------------------------------------------------------

2      Virginia              USC                  Florida

-------------------------------------------------------------------

3      USC                  Florida              Clemson

-------------------------------------------------------------------

 

I want to print a list of the universities, but I do not want a college to show up more than one time in the list. I only know how to select a distinct value for one field.

 

Here is what I have thus far:

$result = mysql_query("SELECT DISTINCT (university_1), university_2, university_3 FROM stores WHERE status = 1 AND approved = 1");

                while($row = mysql_fetch_array($result)) {

                    $u1= stripslashes($row['university_1']);
                    $u2= stripslashes($row['university_2']);
                    $u3= stripslashes($row['university_3']);

                    //display the row
                    if(!empty($u1)) { print '<option>'.$u1.'</option>'; }
                    if(!empty($u2)) { print '<option>'.$u2.'</option>'; }
                    if(!empty($u3)) { print '<option>'.$u3.'</option>'; }

                }

 

Thanks for any help.

 

 

 

[bbaker]

instead of taking care of the duplicates in the query....let PHP do the work for you.

 

Put all the university info in 1 array, then use array_unique(), to remove the duplicates

 

$result = mysql_query("SELECT university_1, university_2, university_3 FROM stores WHERE status = 1 AND approved = 1");
while($row = mysql_fetch_array($result)) {
    $u[] = stripslashes($row['university_1']); // ALL IN
    $u[] = stripslashes($row['university_2']); // ONE
    $u[] = stripslashes($row['university_3']); // ARRAY $u
}

$newU = array_unique($u); // remove duplicates from array

foreach($newU as $opts){
    print '<option>'.$opts.'</option>';
}

[rstewar]

Thanks. Works Great.