Mysql issue

[Dhuan]

I tried to come up with a name for this topic, but with no sucess...

table people

|name |sex |age|occupation|
john m 30 cop
brian m 33 lawyer
ana f 28 lawyer
jack m 27 cop
max m 31 cop

What I want to do is, make the php show all occupation records, without repeating them. so it would be like this:

<select>
<option>cop</option>
<option>lawyer</option>

In other words, to make the php display all occupations avaiable.

Sure i could simply make the Mysql select all records from that table but then the occupations would repeat.

[Fou-Lu]

DISTINCT should take care of you're distinct records (ie: SELECT DISTINCT occupation FROM people); however, a better solution is to break out the occupation from the people table and form an occupation table and link the two tables together. This will eliminate two anomolies from you're manipulations - loss of an occupation through deletion, and awkward updates on an occupation (as well as incorrect occupation, such as setting 'cops' instead of 'cop' for example). Same would theoretically go for you're gender field, but its likely un-necessary since I doubt that any additions / deletions / updates would effect genders (at least anytime in our lifetimes lol). Though technically, you can lose track of a gender if all men were deleted from the database. Solution: use an enum / set type field. This process is called database normalization.