使用php mysql显示外键值

Question

I have 3 tables which are states, cities, colleges. All are contains id and corresponding names. I have one more table students with column names are id, studentname, state, city, college.

Now i need to search how many students are from selected state/city/college, . sometimes i need to select multiple options like state and city but not college or state college but not city etc...

My query is:

SELECT `state`, `city`, `college`, `student name` FROM `students` where `state`='1' AND `city`='4';

It returns records but state city college columns will be foreign keys, i need to see city name but not city id, state name but not state id, etc..

Answer 1

use sql join assuming state_id, city_id, college_id are saved for each student for ex.

SELECT `states.*`, `cities.*`, `colleges.*`, `students.*` FROM `students` left join states on states.id = students.state_id left join cities on cities.id = students.city_id left join colleges on colleges.id = students.id where `students.state`='1' AND `students.city`='4';