Combine arrays, remove duplicates, sport, use in WHILE statement

[sleepyw]

Ugh - title should say sort, not sport, sorry.

 

This should be easy, but for the life of me, I cannot get this to work. I've been staring at it for so long, I'm going nuts.

 

Ultimately, what I'm trying to do is combine the unique results of a query (of 3 columns of a table) to populate a drop-down menu. I have a function in place, however, that function only grabs data from one of the 3 fields I designate, not all 3. So I'm trying to create a new query to search all 3 fields, remove duplicates, sort alphabetically, and then display them in the drop-down list.

 

Here's the function I originally had, but I don't know how to alter this (if possible) to include combining 3 fields instead of having to list 1:

function filldropdown($sql, $sfieldname) {
        $result=mysql_query($sql);
        while($row=mysql_fetch_assoc($result)){
            echo "<option value='" . $row[$sfieldname] . "'>" . $row[$sfieldname] . "</option>";
        }
        mysql_free_result($result);
        return;
    }

And then this is the code that adds the list to the page, except you'll notice at the end of the query, I have to list only 1 field to pull from (TW), instead of it listing TW1, 2 and 3.

<? filldropdown("SELECT distinct TW, TW2, TW3 from TABLE", "TW"); ?>

So instead, I'm trying to do this (I've simplified this to hopefully make it easier to understand my thought process of what I'm trying to do). What am I doing wrong?

$TW1 = mysql_query("SELECT distinct TW from TABLE");
$TW2 = mysql_query("SELECT distinct TW2 from TABLE");
$TW3 = mysql_query("SELECT distinct TW3 from TABLE");

// combine and sort results of TW, TW2, TW3 fields into a single list
$TW1result = mysql_fetch_array($TW1);
$TW2result = mysql_fetch_array($TW2);
$TW3result = mysql_fetch_array($TW3);
													
$CombinedTW = array_merge($TW1result, $TW2result, $TW3result);
$UniqueTW = array_unique($CombinedTW);
$ALLTW = asort($UniqueTW);

while($ALLTW) {
echo "<option value='" . $ALLTW . "'>" . $ALLTW . "</option>"; 
}

[dalecosp]

You can't ask for DISTINCT on multiple fields ... that's not logical.

[Barand]

try

$ALLTW = mysql_query("SELECT DISTINCT TW
    FROM (
    SELECT TW from TABLE
    UNION
    SELECT TW2 as TW from TABLE
    UNION
    SELECT TW3 as TW from TABLE
    ) subq
    ORDER BY TW");


while($row = mysql_fetch_assoc($ALLTW)) {
    echo "<option value='" . $row['TW'] . "'>" . $row['TW'] . "</option>"; 
}

If you correctly normalized your data there would be no need for the UNIONs

[sleepyw]

Barand - it's not working. The entire page is coming back blank (I can't see if there's an error because of a banner at the top lkely covering it).

 

And I'm not sure what you mean by "normalized" my data.

 

The table is a project list, and each column is a person that worked on the project. The 3 columns represent different roles, and people can be in any of the 3 columns. I'm not sure if that has anything to do with what you mean or not.

 

EDIT: I see you reworked the code a little, and I modified mine, but the result is the same (an entirely blank page, except for the banner).

[Barand]

  On 11/18/2013 at 8:53 PM, sleepyw said:

Barand - it's not working. The entire page is coming back blank (I can't see if there's an error because of a banner at the top lkely covering it).

 

And I'm not sure what you mean by "normalized" my data.

 

The table is a project list, and each column is a person that worked on the project. The 3 columns represent different roles, and people can be in any of the 3 columns. I'm not sure if that has anything to do with what you mean or not.

 

That code would produce only part of the page, so ???

 

As for normalizing data, see http://forums.phpfreaks.com/topic/273634-best-way-to-set-up-tables-when-multiple-values/?do=findComment&comment=1408360

[sleepyw]

OK - I temporarily removed the banner and was able to read the error (typical syntax error I missed). It appears as though your suggestion is working!

 

Muchas gracias, kind sir!