getting only one instance from a column

Anon

i am trying to make a dropdown box from and employee database that i have made. this is to do a search by what department people are in. My problem is I do not know how tho make a dropdown box that will list a department only once. this is how my table is set up:

|id|-|name|-|department|

in the department column people can have the same department( example: there are 10 people in accountting)

i tried to use mysql_fetch_row to make a dropdown box but that listed each instance of a department , i just want it to show up once in the dropdown box.

anyone have any idea what i can do ?

Anon

Try: SELECT DISTINCT department FROM yourtable;

Anon

If you have a department table and store the department ID in the employees table, then you should be able to just pull the list of departments from the departments table.
Am I missing something here?

Anon

thanks max, i still am not getting only one instance, it maybe because of the rest of my code. there is what i am doing:@mysql_select_db("employeeprofiles") or die("Unable to open Database");

$query = "SELECT DISTINCT id, bgroup from employees";

$result = mysql_query($query);

/drop-down list/
while(list($id,$bgroup) = mysql_fetch_row($result)) {
print "<option value=>$bgroup</option>\n";

}

</select>
  <br>
<input type=submit value='Search'>

Anon

Normalization is the key!!!!

You should not insert the same data over and over such as department names for different employees.

Use Andrew's suggestion -> create a department table with only two columns, one for department name and another for department ID#.

On your employee table, use a rel_dept_id column that matches the department names.

Anon

I'm not sure exactly how it works, but from the tests I ran, it looks like the number of rows returned by using distinct is the column with the max number of distinct entries.

So, since you're using id, which is different for every record, probably, it's got the most distinct entries. So it's going to return all of those and you'll get duplicates for bgroup. It doesn't matter what order you put them in either.

The suggestion by Kai was a good one. Create a database like this:

table2
+-------+-------------+
| bg_id | bgroup |
+-------+-------------+
| 1 | Accounting |
| 2 | Sales |
| 3 | Engineering |
+-------+-------------+

Then save the number in the original table, rather than the name. Then, when you want all the bgroups, you do

select bgroup from table2;

It'll make things easier in the end...

---JH

no1uknowphp

If I'm reading you right you need to use GROUP BY

SELECT id, name, dept FROM table GROUP BY name;