子级不显示正确父级的数据

Question

I'm relatively new to php, and am stuck on one issue. My index.php file displays a list of states, and displays those states correctly. Each of those states has an p_id field that correspond to the alphabetized listing of states.

The database I'm using has a table that contains the following fields in the cat table: p_id (parent id), state (name of state), city, and id (child's id, which resets for every new state). This is the query I use for populating the states:

select distinct state, p_id from cat order by state asc

List_cities.php is the next page that displays after clicking on a state. Alabama is the first state alphabetically, so would be parent (p_id)=1. When I click on Alabama (or any other state), no cities are displayed. They are all listed in the cat table.

This is the query I use to display the cities:

select p_id, id, city, state from cat where p_id="'.$p_id.'" order by p_id, id

Any help would be greatly appreciated! Thanks!

Answer 1

Imagine you have a STATE table like this:

-------------
id  |  state 
-------------
1   |  California
2   |  New York
3   |  Arizona

and you the CITY table like:

------------------------------
id  |  state_id  | name
------------------------------
1   |     1      | Los Angeles
2   |     1      | San Diego
3   |     1      | San Jose

The correct query to retrieve the states is:

SELECT id, state FROM states ORDER BY state ASC;

and the correct query to retrieve the cities, based on a state is:

SELECT id, name, state_id FROM cities WHERE state_id = ?;

Then obviously, you would use the PDO class to connect the database since it is more secure than whatever you can write. Check the pdo manual for more info. That '?' is actually the parameter which you bind with PDO so don't let it confuse you.

From what I've understood you have created one table where you specify both the city and the state name. I would definitely seperate them, and create two tables as I did above.