Problem In My Select Query Again..

[thara]

hello.. again to my select query... this is my updated one.

 

  $q = "SELECT
   tcs.tutor_id AS tid,
   tcs.category_id AS cid,
   tcs.subject_id AS sid,
   GROUP_CONCAT( DISTINCT s.subjects SEPARATOR ', ') AS subjects,
   t.tutor_name AS tname,
   t.tutor_code AS tcode,
   DATE_FORMAT(t.registration_date, '%b %D, %Y') AS date,
   t.qualification AS qualifi,
   GROUP_CONCAT( DISTINCT o.option_name SEPARATOR ', ') AS tutor_option,
   timg.image_name AS img,
   city_name AS city,
   d.district_name AS district
  FROM tutor_category_subject as tcs
   INNER JOIN subject AS s ON tcs.subject_id = s.subject_id
   INNER JOIN tutor_option AS toption ON toption.tutor_id = tcs.tutor_id
   INNER JOIN options AS o ON toption.option_id = o.option_id
   INNER JOIN tutors AS t ON tcs.tutor_id = t.tutor_id
   INNER JOIN address ON address.address_id = t.address_id
   INNER JOIN city ON city.city_id = address.city_id
   INNER JOIN district AS d ON d.district_id = city.district_id
   LEFT JOIN tutor_images AS timg ON timg.tutor_id = tcs.tutor_id AND timg.image_type = 'profile'
  WHERE s.subjects LIKE '%$subject%'
  GROUP BY tcs.tutor_id";

 

this query is working now.. now I found a problem. it is..... so many subject may have to one particular tutor. I need to select all the subject that pertaining to a tutor with searching keyword to display with search result. In this query searching keyword is '$subject'.

This query only selecting subjects to the tutor similar to the given keyword. For an example if a user give a keyword like 'business' query selecting subjects to that tutor like 'business studies, business accounting., etc only'. so I need to select other subjects too through this query.. can anybody help me?

 

thank you.

[Barand]

That's what a where clause does. If you ask for subjects containing "business" then that's what you get.

 

If you want all subjects, remove the WHERE clause.

[thara]

no.. I need to get all the subjects with searching subject in where clause.

[Psycho]

no.. I need to get all the subjects with searching subject in where clause.

 

Your statement doesn't make sense. I have an idea of what I *think* you might need:

 

Are you wanting all the subjects for any tutors who have one or more subjects that contain 'business' (or whatever the search value is)?

 

For example if tutor Bob subjects "Basket Weaving", "Business Administration" and "Statistics" you want all three of those records because he has at least one record with 'business' in the name?

[thara]

yes... just think tutor Bob has 3 subjects "Basket Weaving", "Business Administration" and "Statistics" and a user given a keyword like '%business%' to find a tutor. In my above query then only selecting one subject "business administration'. I need to select all 3 subject with 'business administration'. clear?

[Psycho]

Then, you probably want a sub-query.

 

You could create a sub-query such as this

SELECT tutor_id
FROM tutors AS t2
JOIN tutor_category_subject AS tcs2
ON tcs2.tutor_id = t2.tutor_id
JOIN subjects AS s2
ON s2.subject_id = tcs2.subject_id
WHERE s2.subjects LIKE '%$subject%'

 

That will return a list of all the tutor IDs where the tutor has one or more subjects with $subject in the name. Then, just use that sub-query in the WHERE clause to only pull records where the tutor ID is in the list of that sub-query

$q = "SELECT tcs.tutor_id AS tid, tcs.category_id AS cid, tcs.subject_id AS sid,
		 GROUP_CONCAT( DISTINCT s.subjects SEPARATOR ', ') AS subjects,
		 t.tutor_name AS tname, t.tutor_code AS tcode, DATE_FORMAT(t.registration_date, '%b %D, %Y') AS date,
		 t.qualification AS qualifi,
		 GROUP_CONCAT( DISTINCT o.option_name SEPARATOR ', ') AS tutor_option,
		 timg.image_name AS img,
		 city_name AS city,
		 d.district_name AS district
 FROM tutor_category_subject as tcs
 INNER JOIN subject AS s ON tcs.subject_id = s.subject_id
 INNER JOIN tutor_option AS toption ON toption.tutor_id = tcs.tutor_id
 INNER JOIN options AS o ON toption.option_id = o.option_id
 INNER JOIN tutors AS t ON tcs.tutor_id = t.tutor_id
 INNER JOIN address ON address.address_id = t.address_id
 INNER JOIN city ON city.city_id = address.city_id
 INNER JOIN district AS d ON d.district_id = city.district_id
 LEFT JOIN tutor_images AS timg ON timg.tutor_id = tcs.tutor_id AND timg.image_type = 'profile'
 WHERE t.tutor_id (SELECT tutor_id
		 FROM tutors AS t2
		 INNER JOIN tutor_category_subject AS tcs2
		 ON tcs2.tutor_id = t2.tutor_id
		 JOIN subjects AS s2
		 ON s2.subject_id = tcs2.subject_id
		 WHERE s2.subjects LIKE '%$subject%')
 GROUP BY tcs.tutor_id";

Edited October 31, 2012 by Psycho

[thara]

thanks for response... I checked it and get this error...

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT tutor_id FROM tutors AS t2 ' at line 18

 

what is the problem...sorry I dont have experience in sub query...

[mikosiko]

 WHERE t.tutor_id (SELECT tutor_id ....

 

 

should be

 WHERE t.tutor_id IN (SELECT tutor_id

[thara]

then I can get this error..

 

Table 'lanka_institute.subjects' doesn't exist

Query: SELECT tcs.tutor_id AS tid, tcs.category_id AS cid, tcs.subject_id AS sid, GROUP_CONCAT( DISTINCT s.subjects SEPARATOR ', ') AS subjects, t.tutor_name AS tname, t.tutor_code AS tcode, DATE_FORMAT(t.registration_date, '%b %D, %Y') AS date, t.qualification AS qualifi, GROUP_CONCAT( DISTINCT o.option_name SEPARATOR ', ') AS tutor_option, timg.image_name AS img, city_name AS city, d.district_name AS district FROM tutor_category_subject as tcs INNER JOIN subject AS s ON tcs.subject_id = s.subject_id INNER JOIN tutor_option AS toption ON toption.tutor_id = tcs.tutor_id INNER JOIN options AS o ON toption.option_id = o.option_id INNER JOIN tutors AS t ON tcs.tutor_id = t.tutor_id INNER JOIN address ON address.address_id = t.address_id INNER JOIN city ON city.city_id = address.city_id INNER JOIN district AS d ON d.district_id = city.district_id LEFT JOIN tutor_images AS timg ON timg.tutor_id = tcs.tutor_id AND timg.image_type = 'profile' WHERE t.tutor_id IN (SELECT t2.tutor_id FROM tutors AS t2 INNER JOIN tutor_category_subject AS tcs2 ON tcs2.tutor_id = t2.tutor_id JOIN subjects AS s2 ON s2.subject_id = tcs2.subject_id WHERE s2.subjects LIKE '%chistian civilization%') GROUP BY tcs.tutor_id

An error occurred in script 'C:\wamp\www\lanka_institute\search\searching.php' on line 92: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given

Date/Time: 10-31-2012 14:15:54

 

[thara]

sorry.. there is a mistake when naming subject table... now its ok....thanks

[thara]

hello... again to this question and I need to modify this with a condition in where clause. I need to search tuition option too along with subject in where clause.

 

so I add it to sub query's where clause like this...

 

WHERE s2.subjects LIKE '%Catholicism%' AND o.option_name = 'Individual'

 

its working.. but problem is same in above subject case. that mean I need to select all tuition option with searching tuition option... in this case it is 'Individual'...

 

again do I need a sub query to do this????

 

thank you..

 

 

Edited November 1, 2012 by thara