thara Posted October 31, 2012 Share Posted October 31, 2012 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. Quote Link to comment https://forums.phpfreaks.com/topic/270123-problem-in-my-select-query-again/ Share on other sites More sharing options...
Barand Posted October 31, 2012 Share Posted October 31, 2012 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. Quote Link to comment https://forums.phpfreaks.com/topic/270123-problem-in-my-select-query-again/#findComment-1389073 Share on other sites More sharing options...
thara Posted October 31, 2012 Author Share Posted October 31, 2012 no.. I need to get all the subjects with searching subject in where clause. Quote Link to comment https://forums.phpfreaks.com/topic/270123-problem-in-my-select-query-again/#findComment-1389080 Share on other sites More sharing options...
Psycho Posted October 31, 2012 Share Posted October 31, 2012 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? Quote Link to comment https://forums.phpfreaks.com/topic/270123-problem-in-my-select-query-again/#findComment-1389082 Share on other sites More sharing options...
thara Posted October 31, 2012 Author Share Posted October 31, 2012 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? Quote Link to comment https://forums.phpfreaks.com/topic/270123-problem-in-my-select-query-again/#findComment-1389084 Share on other sites More sharing options...
Psycho Posted October 31, 2012 Share Posted October 31, 2012 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/270123-problem-in-my-select-query-again/#findComment-1389085 Share on other sites More sharing options...
thara Posted October 31, 2012 Author Share Posted October 31, 2012 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... Quote Link to comment https://forums.phpfreaks.com/topic/270123-problem-in-my-select-query-again/#findComment-1389086 Share on other sites More sharing options...
mikosiko Posted October 31, 2012 Share Posted October 31, 2012 WHERE t.tutor_id (SELECT tutor_id .... should be WHERE t.tutor_id IN (SELECT tutor_id Quote Link to comment https://forums.phpfreaks.com/topic/270123-problem-in-my-select-query-again/#findComment-1389090 Share on other sites More sharing options...
thara Posted October 31, 2012 Author Share Posted October 31, 2012 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 Quote Link to comment https://forums.phpfreaks.com/topic/270123-problem-in-my-select-query-again/#findComment-1389093 Share on other sites More sharing options...
thara Posted October 31, 2012 Author Share Posted October 31, 2012 sorry.. there is a mistake when naming subject table... now its ok....thanks Quote Link to comment https://forums.phpfreaks.com/topic/270123-problem-in-my-select-query-again/#findComment-1389094 Share on other sites More sharing options...
thara Posted November 1, 2012 Author Share Posted November 1, 2012 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/270123-problem-in-my-select-query-again/#findComment-1389306 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.