unique records only

[wkilc]

Howdy,

 

The following code wil geenrate a dynamic pull-down list based on column values.

 

Can someone help me tweak it so that is will NOT display a duplicate record value.  That is, if the sponsors values in the table were Hart, Michaels, Michaels, Morella  ...it would only display Hart, Michaels, Morella in the pull-down menu.

 

<form name="form0">
<? $result = @mysql_query("select distinct sponsor from table ORDER BY sponsor ASC");
if (mysql_num_rows($result) > 0) {
  print "<select name=\"link\">"; ?>
  <option <?php if(empty($_GET['sponsor'])){ echo "selected=\"selected\""; } ?> value="<? echo "$page_name" ?>">SELECT A SPONSOR</option>
  <? while ($row = mysql_fetch_array($result)) {
    print "<option ";
    if($_GET['sponsor'] ==   $row['sponsor']  ){ echo "selected=\"selected\""; }
    print " value=\"index?sponsor=" . $row['sponsor'] . "\">" . $row['sponsor'] . "</option>\n";
  }
  print "</select>";
} 
?>
</form>

 

Thank you.

 

~Wayne

[redarrow]

select distinct sponsor from table ORDER BY sponsor ASC

 

mysql should off done that, your code is correct, try with no ascending

 

 

1 min your hiding all the error's why...

 

get rid off the @, your a coder know...

 

what not broke leave alone, if broke fix it

 

also <?php <<correct way.

 

sometimes short cuts make scripts not work, even if function  is enabled....

[jcbones]

Distinct doesn't like to be coupled with Order by.

 

So, you have to join the table to itself.

 

Try:

SELECT DISTINCT sponser FROM (table INNER JOIN table as t ON table.sponser = t.sponser) ORDER BY sponser ASC;

 

Ideally you would join on the PRIMARY KEY, but I dont' know if you have one so I didn't go there.

[redarrow]

Distinct doesn't like to be coupled with Order by.

 

where that written, i do it that way???? (i do use a primary key thu.

 

 

[jcbones]

Distinct doesn't like to be coupled with Order by.

 

where that written, i do it that way???? (i do use a primary key thu.

 

Le Manuel

Distinct Optimization

DISTINCT combined with ORDER BY needs a temporary table in many cases.

 

There are comments at the bottom that suggest the INNER JOIN.  I have found that to work in EVERY case that I have had trouble using a ORDER BY in a DISTINCT Op query.

[DavidAM]

I believe the manual is saying that using DISTINCT causes the engine to create a temporary table in order to collect the data and determine the unique ones.

 

The user comments do seem to indicate that there is some problems with DISTINCT and ORDER BY.  Rather than join the table to itself, I would try:

select sponsor from table GROUP BY sponsor

The GROUP BY should cause the returned list to be unique.

 

However, is your table really named "table"?  Is that a legal name for a table?  If that is the table's name try putting it in backticks

select distinct sponsor from `table` ORDER BY sponsor ASC"

(well, that doesn't make sense, if it was an illegal name, the query should be failing entirely)

 

[jcbones]

You are correct, David.  The manual does state that.

In most cases, a DISTINCT clause can be considered as a special case of GROUP BY.

 

And that using GROUP BY will return the same results as DISTINCT in many cases.

 

But, I wasn't following the KISS rule on this one.