Page 1 of 1

Selectively populating a drop-down box

Posted: Tue Jul 29, 2008 9:00 am
by kdidymus
I have begun to experiment with populating drop-down boxes from my MySQL dB.

The following code connects to my database, downloads a list of surnames and populates a drop-down box;

Code: Select all

<?php /* Program: surnamelist.php
* Desc:    Surname Drop-Down.
*/
?>
<html>
<head>
<title>Didymus.org.uk | A-Z by SURNAME</title>
</head>
<body topmargin="0" leftmargin="0" rightmargin="0" bottommargin="0" bgcolor="#CED2D9">
<?php
include_once("../*******.inc.php");
$cxn = mysql_connect($host,$user,$password)          or die ("couldn't connect to server");
mysql_select_db($database);
$query = "SELECT surname FROM tree ORDER BY surname,forename,middlenames,yearofbirth";
$result = mysql_query($query)             or die ("Couldn't execute query.");
echo "<form method='POST' action='--WEBBOT-SELF--'>
    <p><select size='1' name='D1'>
      <option selected>SURNAME</option>";
while ($row = mysql_fetch_array($result))
{
extract($row);
echo "<option unselected>$surname</option>";
}
echo "</select><input type='submit' value='GO!' name='B1'></p>
</form>
</body>
 
</html>";
?>
You can see it working at: http://www.didymus.org.uk/tree/test.php

Here's my question. How can I remove duplicate entries? For example, I only want the list to have ONE of each surname, not the multiples it now has.

Any idea how I can achieve this?

Many thanks in advance.

KD.

Re: Selectively populating a drop-down box

Posted: Tue Jul 29, 2008 9:04 am
by it2051229
on your query do a DISTINCT.. somethine like "SELECT DISTINCT surname FROM blah blah"

Re: Selectively populating a drop-down box

Posted: Tue Jul 29, 2008 9:05 am
by jaoudestudios
In your query use Distinct or Group By

eg....

Code: Select all

 
SELECT surname FROM tree GROUP BY surname ORDER BY surname,forename,middlenames,yearofbirth
 

Re: Selectively populating a drop-down box

Posted: Tue Jul 29, 2008 9:08 am
by kdidymus
Superb. Thank you so much. It worked a treat!

KD.

Re: Selectively populating a drop-down box

Posted: Tue Jul 29, 2008 9:50 am
by jaoudestudios
What did you use in the end? Distinct or Group By?

Re: Selectively populating a drop-down box

Posted: Wed Jul 30, 2008 2:18 am
by kdidymus
I used GROUP BY. I've taken the temporary PHP page off-line at the moment whilst I refine it's functions.

Thank you again for your help.