data integrity of data stored using drop down lists.

[ajoo]

Hi all, 

 

I just changed one of my forms to use drop down lists for storing Country, State, City and Pin values. 

 

The values for each of these fields come from individual tables in the database having the same name as these fields.

 

Since the values of pin or picodes are dependent on the city and their value in turn is dependent on the state and so on, so the tables need to check for data integrity.

 

However, the tables not only need to check for data integrity in the sense that a value should exist in the parent table, it also needs to be verified that the data comes from the correct subset of values.

 

For e.g.  The values for the fields are as shown in the table below,  parenthesis values representing their numeric unique ids. 

 

COUNTRY        STATE                  CITY              PIN

C1 (1)                ST1(1)                  CT1 (1)        11001 (1) 

                                                       CT2 (2)        11002 (2)

 

                          ST2(2)                  CT3 (3)        12001 (3)

                                                       CT4 (4)        12002 (4) 

 

 Then the valid sets of values are

 

CO  ST  CT   P

1--- 1 --- 1 --- 1      

1 ---1-----2----2

1----2-----3----3

1----2-----4----4

 

and so on.  

 

A value of 1 --- 2 --- 1 ---2  will obviously be wrong even though the values are coming from valid tables.

 

The question then is, how to ensure in the simplest possible way, that an entry that i fetch from the database or that I am going to insert into it is correct according to the subset values as well. 

 

Thanks all !

 

[requinix]

Not sure I understand the problem...

 

State has a foreign key to the country, city has a foreign key to the state, pin has a foreign key to the city. Parent/child relationships.

 

When you get data from the form, look up the corresponding data from the three child tables and verify that the parent key matches what was given in the form. Like

$state = /* fetch state matching ST */
$city = /* fetch city matching CT */
$pin = /* fetch pin matching P */

if ($state["country"] == $CO && $city["state"] == $ST && $pin["city"] == $CT) {
	// valid
} else {
	// invalid
}

(obviously you should also check that the four values exist in the database in the first place - that overlaps a lot with this)

[ajoo]

Hi requinix, 

 

Thanks for the reply !

 

I think you understand the problem correct. As can be seen from your reply, It needs fetching data from the 3 tables and then making a comparison. What I would like to know is if it is possible to do this in a simpler manner ? Maybe it can be accomplished using a single mysql query. Yes ?

 

Thanks again !

[requinix]

Sure: do one query with JOINs using the foreign keys between the four tables. Coupled with the WHERE based on the user's selection you'll either get one result matching all the data or nothing.

[ajoo]

Thanks requinix, 

 

I'll try out and revert. 

[ajoo]

Hi requinix, 

 

 

 

Coupled with the WHERE based on the user's selection ...

 

I wonder if you mean the user id or the user's choice of the various fields because so far as I can see a WHERE clause on user's id makes no sense.  So I don't think it would need a WHERE clause. Kindly clarify.

 

 

Thanks loads. 

[requinix]

User ID? I didn't say anything about a user ID...

 

SELECT whatever you want if anything at all
FROM countries co
JOIN states st ON co.id = st.country
JOIN cities ct ON st.id = ct.state
JOIN pins p ON ct.id = p.city
WHERE co.id = ? AND st.id = ? AND ct.id = ? AND p.id = ?

The query will only return a result if the four IDs exist and they are related to each other.

[ajoo]

Thanks requinix, 

 

That worked just fine !