SQL question

I need to remove all country options on my site apart from 1

Removing them in the predefined values in admin does nothing at all, after saving they all just re-appear...

Found 2 areas in database that I think are relevant?

sys_countries - have deleted all but the one here but still no change sitewide

sys_pre_values - lists all the countries that are within the pre values area of admin, only there is no option to delete anything in this database!

Any suggestions how I can go about this? I need just a single country to show sitewide, no options in profile join / edit

I know I can set a 'default' country but I want to remove all other options as Im using a mod with country / states central to the site and don't want anything else to show other than the one country with states.

Thanks :-)

"sys_pre_values - lists all the countries that are within the pre values area of admin, only there is no option to delete anything in this database!"

No sure exactly what you mean by this - if you deleted all of the countries in the other table, the same process would be used to delete all of the countries in the sys_pre_values table as well

If you need the country field in the profiles set to United States; then remove the country item from the join page and from the profile edit page; drag to the inactive box.  Go into the database and set the default value for the country field to be United States; that way each time a profile is made, it will populate the country field with United States.

I deleted most countries from pre-defined list on multiple sites and had no issues with them re-appearing.  Make sure you click the "save button" after you have deleted them and clear caches.

http://yoursite.com/administration/preValues.php?list=Country  

click red minus sign on the right side of the box and save,

As I said in the first post, removing countries in the predefined value list within admin does nothing. 

Click the red minus sign, the go, click save at the bottom, they re-appear... first thing I did.

This must be cache issues..

 as in clearing them.. done. 

as soon as a click 'save' the deleted items re-appear. 

Clean install of 7.1.4

phpmyadmin - the sys_pre_value table has the following written above it, 

 This table does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available.

Not sure if this is not allowing changes to be made from within admin.

All (those looked at) other tables in db I can edit, delete etc... just not this one.

 
Will not affect it from admin. But you must be using a newer version of phpMyAdmin which requires all tables have at least one unique index to enable those functions. If this is your own dedicated server you may want to revert to a older version of phpMyAdmin. A Version under 4.0.

Not all tables in dolphin have a index. Look at this table in phpMyAdmin.

sys_stat_member

That table also does not have a index. So if i am correct about your phpMyAdmin version, you should see the same problem there. There are others as well, i just don't want to hunt them all down.

Anyhow. That's a phpMyAdmin issue. It does not affect dolphins admin. So something else is causing your problem. I don't have a issue deleting them either.

Anyway. Here is a query you can run that will delete all country's except US. Modify the US to use the code of the country you do not want deleted.

DELETE FROM `sys_pre_values` WHERE `Key` = 'Country' AND `Value` != 'US';

Then clear the dolphin cache.

Thanks Deano :-) 

Really helpful as always!