Select Distint code

jd57

Hi Everyone,

I can't seem to get this drop menu to work using the following code, all I'm getting is a blank drop menu, yet if I use the second code just bellow it works fine.
I need to get this script to work using Distint. can someone tell me what is wrong with this code

<?PHP 
mysql_connect("localhost", "xxxx_xxx", "xxxx") or die(mysql_error());
mysql_select_db("xxxx") or die(mysql_error());

$sql = 'SELECT DISTINCT Hybridiser FROM ihsreg ORDER BY Hybridiser';
if (!(@ $result = mysql_query($sql))) { 
        print "There was an error running your query:<p>\n". $sql . "<p>\n"; 
}      

print '<select name="Hybridiser" 
onchange="window.opener.change_search_value(this.options[this.selectedIndex].text);"><br>\n'; 
print '<option value="#">  Choose One   ';


while ($row = mysql_fetch_row($result)) { 
        $id   = $row[0]; 
        $Hybridiser = $row[6]; 

    print "<option value=\"$id\""; 
    if ($_REQUEST["Hybridiser"] == $id) { print " selected"; } 

    print "> $Hybridiser <br>\n"; 
} 
print '</select>'; 

?>

$sql = "SELECT * FROM `ihsreg` ORDER BY `Hybridiser` ASC";

Tezread

hmmmm - i know this is not alot but maybe this could help:

http://dev.mysql.com/doc/refman/5.1/en/distinct-optimization.html

Rodney_H_

This is what I came up with, but I was up late, so I might be totally wrong, LOL!!

Your queries are very different:

1) $sql = 'SELECT DISTINCT Hybridiser FROM ihsreg ORDER BY Hybridiser';

This will just return one field from your table: "Hybridiser"

2) $sql = "SELECT * FROM ihsreg ORDER BY Hybridiser ASC";

This will return ALL (*) fields from your table

Your script AFTER the query is defined uses the function: mysql_fetch_row() and you are attempting to output TWO different row values:

$id = $row[0];
$Hybridiser = $row[6];

This is possible in your 2nd query because you are telling mySQL you want every row returned. This is NOT possible 1st query because you are only asking for one field value to be returned.

You need to tweak your first query if you want to return the IDs, too.

Then it should work.

(This is one reason why I am a BIG fan of the mysql_fetch_array() function: I end up using named keys rather than numerical keys so I can keep things straight.)