Added Custom PullDown Values, Allow Users To Delete?

[JustinK101]

Hello, I am creating an application where I allow users to dynamically add values to pull down menus via a mysql database. For example, a simple form has a field, Country. Currently the only value under the pulldown is United States, but I have a link to allow users to add custom values into the country menu.

My problem, I would like to enable users to delete entries in pulldown menus as well, but I forsee logical problems in the future. For example, what happens if there are a few hundred records with various custom countries, and then a user deletes a custom country. All the records with that custom country now point to an orphaned country value. How do you keep track? Do you check every record when a user attempts to delete a custom pulldown value, and if any of the records point to that custom pulldown value, not allow the user to delete it? Sounds like a lot of work, and confusion. But at the same time, not allowing deletes is not user friendly. For example, what if they make a typo when they provide a custom menu value?

Thanks for advice.

[pocobueno1388]

Give each custom pulldown value an ID number when it is added. Then when they select the custom value it will know which value to delete by the ID.

[code]
$countryID = $_POST['countryID'];
mysql_query("DELETE FROM tablename WHERE countryID='$countryID'");[/code]

Just make the select have a name of "countryID" like so:

[code]<select name="countryID">[/code]

Then you could do a while loop to show all the option values.

[JustinK101]

I know how to do the delete, I am wondering the logic behind it. I.E. the prospect of leaving orphaned values stores in records in the database.

For example lets say I pull up a customer record. The country they selected when they created the record was deleted out of the pulldown menu values though. Now their country is not set, because the value stored under the customer does not match any of the values stored for the pulldown menu.

You see, it just seem like a bad idea to delete pulldown menu values that are stored in mysql records.

[snorkler]

This is opening a huge can of worms, potential for multiple entries for the same country, orphan ids.

I don't see why you would want to allow this, it seems quite strange.  Best not to allow users to add and delete countries.  Better to use a table system where the users can choose to add a country to their list from a list of countries.  This can be done with two tables
Table 1
country_id
country

Table 2
user_id
country_id

That way when a user wants to add a country to their list, you add an entry into table 2 that links the user to the country and delete that entry when they wish to delete the country, that leaves all the other user records in tack,

[JustinK101]

snorkler:


Thanks for the reply, you understand the problem of orphaned values and duplicates. The countries was just an example, for example another field is Title/Occupation. This is where allowing the user to add their custom title/occupation to the list of pulldown menu values is going to come in handy, since I can't possibly list every possible title/occupation.

In your example using the two tables, I dont quite follow. Currently I have the following as an example:

Table Customers
title_occupation varchar(100)

Table PullDownMenuValues
menu_id value_id value
0           0          CEO
0           1          CFO
0           2          CTO

So when the user select a value from the pulldown, i.e. CEO that value is stored into the customers.title_occupation. Now if they delete the CEO record in the pulldownmenu values, their customers.title_occupation still stores CEO.

The only solution I can see, if a user deletes one of the values in the pulldown menu, it must search and clear out that deleted value from the customers.title_occupation table as well. Major pain in the butt. There has to be some sort of framework for managing this stuff. Anybody know of one? All this interaction with forms is the biggest pain in the butt.

[markbett]

another soluion is to add an "expired" or "retired" field  the dropdown will only show values tha are not expired a user cannot delete a field but rather expire it... then when they get readded itwill check to see if it exists first, if so it will unexpire it otherwise it will add it.

[snorkler]

Maybe you can keep the same format, but just allow them to add to Table 1, and when they delete, just delete the realtionalship data, so the occupation title still exists for other users.  This way you don't have to populate Table 2, clients will do that for you.

You may need to keep an eye on the entries to make sure it's quite valid and there are no problems (bad spelling, vulgarities etc)