sleepyw Posted November 18, 2013 Share Posted November 18, 2013 (edited) 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 November 18, 2013 by sleepyw Quote Link to comment Share on other sites More sharing options...
dalecosp Posted November 18, 2013 Share Posted November 18, 2013 You can't ask for DISTINCT on multiple fields ... that's not logical. Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted November 18, 2013 Solution Share Posted November 18, 2013 (edited) 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 November 18, 2013 by Barand Quote Link to comment Share on other sites More sharing options...
sleepyw Posted November 18, 2013 Author Share Posted November 18, 2013 (edited) 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 November 18, 2013 by sleepyw Quote Link to comment Share on other sites More sharing options...
Barand Posted November 18, 2013 Share Posted November 18, 2013 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 Quote Link to comment Share on other sites More sharing options...
sleepyw Posted November 18, 2013 Author Share Posted November 18, 2013 (edited) 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! Edited November 18, 2013 by sleepyw Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.