There is a field, 3 dropdowns, and a button:
- The field will allow the user to change his name.
- The first drop-down will allow the user to choose the country.
- The second drop-down will allow the user to choose the city which will be enabled after the user chooses the country. (It will show cities that are related to the chosen country)
- The third drop-down will allow the user to choose the neighborhood which will be enabled after the user chooses the city. (It will show neighborhoods that are related to the chosen city)
- The button will allow the user to save the changes.
There are 4 tables inside MySQL:
The users' table
| ID | NAME | COUNTRY_ID | CITY_ID | NEIGHBORHOOD_ID |
|---|---|---|---|---|
| 1 | user_3281681 | 2 | 4 | 7 |
The countries table
| ID | NAME |
|---|---|
| 1 | Germany |
| 2 | Canada |
The cities table
| ID | NAME | COUNTRY_ID |
|---|---|---|
| 1 | Berlin | 1 |
| 2 | Munich | 1 |
| 3 | Toronto | 2 |
| 4 | Ottawa | 2 |
The neighborhoods table
| ID | NAME | CITY_ID |
|---|---|---|
| 1 | Bergmannkiez | 1 |
| 2 | Old Spandau | 1 |
| 3 | Obermenzing | 2 |
| 4 | Cosimapark | 2 |
| 5 | Casa Loma | 3 |
| 6 | Distillery District | 3 |
| 7 | Westboro | 4 |
| 8 | Manotick | 4 |
What do I want?
How can I prevent the user update in the users' table if the country, city, and neighborhood were not related to each other?
Here are some examples:
- I'll accept the user update in this situation
(NAME = ... | COUNTRY_ID = 2 | CITY_ID = 4 | NEIGHBORHOOD_ID = 7) - I'll reject the user update in this situation
(NAME = ... | COUNTRY_ID = 2 | CITY_ID = 4 | NEIGHBORHOOD_ID = 3) - I'll reject the user update in this situation
(NAME = ... | COUNTRY_ID = 1 | CITY_ID = 4 | NEIGHBORHOOD_ID = 1) - I'll accept the user update in this situation
(NAME = ... | COUNTRY_ID = 1 | CITY_ID = 2 | NEIGHBORHOOD_ID = 4)
We all know the client side is not safe so we should verify insertions and updates before putting the data in the database so I want to prevent the user update in the users' table if the country, city, and neighborhood were not related to each other.
I know how can I prevent the user update if the country, city, and neighborhood were not related to each other using PHP and using MySQL triggers, but I'm here to ask if there is any other way to prevent that without writing the code.
Inside MySQL: Is there any way to automatically distinguish if the country, city, and neighborhood were related to each other or not?
Ask me if there is anything that is not clear. Thank you.
CodePudding user response：
Drop COUNTRY_ID and CITY_ID from the users table. You can get them all with a proper join. For example
select u.*, co.NAME as country, ci.NAME as city, n.NAME as neighborhood
from users u
join neighborhoods n on u.NEIGHBORHOOD_ID = n.ID
join cities ci on n.CITY_ID = ci.ID
join countries co on ci.COUNTRY_ID = co.ID