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. 

[scootstah]

You'd probably want to use JOIN's here.

[thara]

can anybody help me to build this search query?

 

How can I use 'join' here to build this query?

[jcbones]

How about posting your form, that way we will know how to build the query.

[Barand]

it would look something like this (I don't know your actual column names)

 

SELECT t.tutor_name, c.city_name, d.district_name
FROM district d
INNER JOIN city c ON c.disttrict_id = d.id
INNER JOIN address a ON a.city_id = c.id
INNER JOIN tutor t ON t.address_id = a.id
INNER JOIN tutor_category_subject tcs ON tcs.tutor_id = t.id
WHERE tcs.subject_id = $subjectid
   AND c.id = $cityid

 

$subjectid and $cityid would be from the search form.

[thara]

Thanks Barand for your solution. But 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 = 'Kalkata' AND subject.subjects = 'Catholicism';

 

But Its output are NULL values for all selected columns.

 

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

[Barand]

it's is usual with searches for the user to select the object of the search, in this case city and subject, from dropdowns where the values are the IDs of the city and subject. These ID values are then used in the sql query and not the names. This is more efficient as your indexes are usually built on IDs and it avoids erroneous input from users.

[thara]

City come from a dropdown menu in my search form... But users need to type their needed subject in a text field in my search form. Then I want to get search result according to given city and subject. As well as there are more subjects to one tutor. Its no problem if users has given a subject through the search form system should display the tutors who teach that given subject.

[Barand]

If you are using GROUP_CONCAT you should add "GROUP BY tutor_code".

 

I don't have your data so can't test but

 

- Are you sure the city and subject exist in the tables and are spelled the same?

- Are there matching rows in the tables for every join?

[thara]

Still I confusing in this query.. I tried like this

 

$query1 = "SELECT tutor_id, tutor_name, tutor_code, qualification, DATE_FORMAT(registration_date, '%b %D, %Y') AS date, city_name 
			   FROM tutors
			   INNER JOIN address ON address.address_id = tutors.address_id
			   INNER JOIN city ON city.city_id = address.city_id
			   WHERE city.city_id = {$_SESSION['city']}";

 

This query is OK. its working properly. But problem is I have no idea to append subject that user has given from my search form to this query. all subject that some tutor can teach have in tutor_category_subject table. And commonly all subjects exist in subject table. 

 

From search form, user given subject exist in session like this $_SESSION['subject']. Now I need to check who are the tutors who are in $_SESSION['city'] and who are teach this $_SESSION['subject'].

 

My tutor_category_subject have tutor_id, category_id, subject_id and subject table have subject_id and subject_name..

 

Can any body help me to build this query..

 

Any comments are greatly appreciated.

 

thank you

[Barand]

The joins that you used before , IE

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

 

should pull in the subjects but you said you were getting no data returned. Without data I cannot see why not.

[thara]

It can get only one row.. why is it?

[Barand]

That question is moving beyond SQL into the realms of clairvoyance.

[fenway]

You're new here -- and we're having trouble figuring out what you're talking about -- please read the topic in my sig before posting further.