Selectively populating a drop-down box

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Selectively populating a drop-down box

Post 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.
User avatar
it2051229
Forum Contributor
Posts: 312
Joined: Tue Dec 25, 2007 8:34 pm

Re: Selectively populating a drop-down box

Post by it2051229 »

on your query do a DISTINCT.. somethine like "SELECT DISTINCT surname FROM blah blah"
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Selectively populating a drop-down box

Post 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
 
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Selectively populating a drop-down box

Post by kdidymus »

Superb. Thank you so much. It worked a treat!

KD.
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Selectively populating a drop-down box

Post by jaoudestudios »

What did you use in the end? Distinct or Group By?
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Selectively populating a drop-down box

Post 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.
Post Reply