Search Query Problem.

[thara]

Hi.. everyone.

I am confusing when I creating this search query. can anybody help me to build this query?

I have mysql tables called 'tutors', 'address', 'city', 'district', 'subjects', 'tutor_category_subject'. Tutors table has address_id from address table. Address table has city_id from city table. City table has district_id from district table and district table have district_id, district_name. Subject table has all subjects and 'tutor_category_subject' table has all subjects to particular tutor.

Now I need to create a search query according this information. In My web page's search bar Users have to select Find Option (Eg: Tutors, Institute, etc. In this case it is tutors), District, City and text box to type their Subject to search. So How I create a query using this mysql tables to search tutors according given district, city and subject.

any comments are greatly appreciated.

Thank You.

[thara]

I couldnt tell you my 'tutor_category_subject' table has 'subject_id column not subject_name. All my subjects have my 'subjects' table. tutor_category_subject have subject_id, tutor_id and category_id.

Then I tried like this

SELECT tutor_name, tutor_code, qualification, registration_date, GROUP_CONCAT(subject.subjects SEPARATOR ', ') AS subjects
FROM tutors
INNER JOIN address ON address.address_id = tutors.address_id
INNER JOIN city ON city.city_id = address.city_id
INNER JOIN tutor_category_subject ON tutor_category_subject.tutor_id = tutors.tutor_id
INNER JOIN subject ON subject.subject_id = tutor_category_subject.subject_id
WHERE city.city_name = 'Anuradhapura' AND subject.subjects = 'Catholicism';

But Its output are NULL values for all selected columns.

NOTE: 'Kalkata' and 'Catholicism' come from my search form.

[laserlight]

So there are rows retrieved, but the fields of those rows have NULL values? If so, then it suggests that you have NULLs stored in the table, so garbage in, garbage out.