create a drop down menu form a table column

[mickfitz]

Hello

My first post here! :-)

What I'd like to achieve is to create a drop down menu on my search page.

The mysql table contains information on old vinyl records (45's).

What I'd like to do is take the column that contains the artsits name ... filter out any duplicates or triplicates etc etc (only show the artists name once).

Then create/populate a drop down menu with the results.

At the moment I'm creating the drop down menu manually and it's a looooong list :-)

I'm hoping there is a PHP script/method that will do it automatically?

Any help will be very much appreciated.

TIA ... Mick

[edwardsbc]

Use a SELECT DISTINCT on you artist name field, then loop through the results to create the <SELECT> construct. It might look something like this:

$sql="SELECT DISTINCT artistname, uniqueid FROM tblartists ORDER BY artistname";

$r=odbc_exec($h, $sql); //use your database functions
if(!$r){die "Query failed: $sql";}

echo 'Choose Artist: <select name="artistdrop">';
while (odbc_fetch_row($r)) {  //again use your db functions
  $artist=odbc_result($r,'artistname');
  $uid=odbc_result($r,'uniqueid');
  echo '<option value="'.$uid.'">'.$artist.'</option>';
   }
echo '</select>';

Of course you still need the form tag and all, but you should get the idea.

[netmastan]

$result=mysql_query("select player_id, player_name from ".$prefix."_audio_player order by player_name", $dbi);
echo "Player: <select name=\"player\">";
while(list($player_id, $player_name) = mysql_fetch_row($result, $dbi)) {

echo "<option value=\"$player_id\">$player_name</option>";
  }
echo "</select>";

Note :$dbi is database connection
u can echo $player to know what is selected,

[mickfitz]

Thanks guys ... but I'm still not quite there yet! ... my apologies in advance for what I'm pasting below :-)

This is what I've put together and of course it doesn't work ... probably full of stupid errors!

<?

mysql_connect ("mysql.websitename.co.uk", "username", "password");
mysql_select_db ("dbname");

$sql=("SELECT DISTINCT artists, uniqueid FROM tablename, ORDER BY artists");

$r="mysql_connect"; //use your database functions

if(!$r)die ("Query failed: $sql");

//$sql=("SELECT DISTINCT artists, uniqueid FROM northern, ORDER BY artists");

echo 'Choose Artist: <select name="artistdrop">';
while (mysql_fetch_row($r)); //again use your db functions
{
$artist=mysql_result($r,'artists');
$uid=mysql_result($r,'uniqueid');
echo '<option value="'.$uid.'">'.$artists.'</option>';
}
echo '</select>';

?>

[edwardsbc]

Your database function I believe should be mysql_query():

$h=mysql_connect ("mysql.websitename.co.uk", "username", "password"); 
if(!$h){die "DB Connection failed.";}

$d=mysql_select_db ("?yourdbnamehere?"); 
if(!$d){die "Failed to select database.";}

$sql="SELECT DISTINCT artists, uniqueid FROM northern ORDER BY artists"; 

$r=mysql_query($sql,$h); 
if(!$r)die ("Query failed: $sql"); 

echo 'Choose Artist: <select name="artistdrop">'; 
while ($array=mysql_fetch_row($r)); //again use your db functions 
{ 
$artist=$array['artists'];
$uid=$array['uniqueid']'
echo '<option value="'.$uid.'">'.$artists.'</option>'; 
} 
echo '</select>'; 

$h is the 'handle', $r is the 'result pointer'. According to the docs, $h is optional for mySQL functions, but I would get in the habit of using it. See you can test $h to see if the connection went OK.

Of course your database may not actually reflect this example. Do you have a field called 'uniqueid'?