[RESOLVED] queries and logic

Bilbozilla

Hi. I have a mysql database set up that records 2 sets of data and writes the data into a lookup table.

tbl_country                       tbl_country_lu (data updated by user)
| ID | country_name               | ID | country_ref | userid   |
---------------------------        ---------------------------------
| 1  | United States               1  1   user2
| 2  | Mexico                      2  3   user2
| 3  | Canada                      3  3   user1
                                   4  1   user1


tbl_lu_areas                                   lu_user_areas (data updated by user)
| ID | ctry_ref    | area_name  |        | ID | userid             | area_ref     |
-------------------------------------        -----------------------------------------
1  1  California                          1  user2   1
2  1  Hawaii                              2  user2   3
3  3  Alberta                             3  user1   4
4  3  British Columbia                    4  user2   2
                                          5  user6   1

My issue is this - The user enters the countries he has traveled to and the reference is stored in tbl_country_lu. I would then like to bring up a display showing the states/provinces so that further selections can be made, pulling the data from tbl_lu_areas.

I am "hacking" my way through this and have come up against a brick wall. Can you please assist? I am looking specifically for the process and the SQL to get a list of the areas constrained by the user already indicating he or she has been in the country in which those areas exist. Thanks.

Bilbozilla

So the user selects the United States and Canada boxes on page1.php. Page2.php writes the data into the database and shows page3.php. Page 3.php should list the name of the country and its associated states/provinces. How do I do this? I'd like it to list it as the following:

United States

Alabama
Arkansas
...

Canada

Alberta
...

Bilbozilla

I'm making some progress. The following sql statement gives me the same record X3.

For instance, in lu_countries, there are three distinct entries for $userid. I can't quite wrap my head around this and the logic. Can you assist?

SELECT distinct lu_countries.skill_id, tbl_countries.country, tbl_states.ID, tbl_states.state_name FROM lu_countries, tbl_countries, tbl_states WHERE tbl_states.countrylu_id=tbl_countries.ID  AND lu_countries.uid='$userid' ORDER BY country, state_name

The results are as follows:

Skill_ID  country   ID   state_name
134      Canada    58   Alberta
162      Canada    58   Alberta
299      Canada    58   Alberta
134      Canada    59   British Columbia
162      Canada    59   British Columbia
299      Canada    59   British Columbia
134      Canada    63  Labrador
162      Canada    63  Labrador
299      Canada    63  Labrador
...
134   United States    12    California
162   United States    12    California
299   United States    12    California
134   United States    14    Washington
162   United States    14    Washington
299   United States    14    Washington
134   United States    18    Arkansas
162   United States    12    Arkansas
299   United States    12    Arkansas

...
134   Mexico     104   Cozumel
162.....

I just want it to return

134      Canada    58   Alberta
134      Canada    59   British Columbia
134      Canada    63  Labrador
....
162   United States    12    California
162   United States    14    Washington
162   United States    12    Arkansas
...
299   Mexico     104   Cozumel
299   Mexico     105  ....
....

Bilbozilla

Ok. I solved this problem too.

User ignorance.

Instead of trying to write the entire SQL command at once, I did it piece by piece in a "stacked" manner

Select
   *
From
   tbl1, tbl2
where
   tbl1.row1 = tbl2.row4
Order by
   ......

And then proceeded to add from there. A new approach for an inexperienced SQL user.