Jump to content

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


sleepyw
Go to solution Solved by Barand,

Recommended Posts

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>"; 
}
Edited by sleepyw
Link to comment
Share on other sites

  • Solution

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

Edited by Barand
Link to comment
Share on other sites

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).

Edited by sleepyw
Link to comment
Share on other sites

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 ??? :psychic:

 

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.