deansaddigh Posted February 14, 2010 Share Posted February 14, 2010 Hi guys, Firstly i have 3 tables: 1.School 2.course 3.school_course (linker table) What i want to do is make a page which lists all courses with its school name above it. I have done the sql statment to the best of my ability but to be honest i need someone to help me. Heres my sql statement, it doesnt error out, but it doesnt seem to echo anything out, any ideas? <?php $query = "select school_course.school_id, school_course.course_id, course.course_id, course.name, course.level, course.price, course.duration, course.info, school.school_id, school.name, school.street, school.town, school.city, school.county, school.region, school.postcode, school.country, school.school_facts, school.general_info, school.school_facilities FROM school_course LEFT JOIN course ON school_course.course_id = course.course_id LEFT JOIN school ON school_course.school_id = school.school_id"; //Use this query below $result = mysql_query($query, $conn) or die ("Unable to perform query"); while($row = mysql_fetch_array($result)) { echo $row["school.name"]; } ?> Quote Link to comment https://forums.phpfreaks.com/topic/192049-really-need-help-with-sql-join-and-displaying-data/ Share on other sites More sharing options...
Andy-H Posted February 14, 2010 Share Posted February 14, 2010 Why are you selecting all that info, only select what you need, also; when the query is fetched into an array, the keys will not be included in the array indexes, since you have multiple fields with the 'name' alias being fetched in the query, I am not sure which will be returned from the school table and how mysql will handle the array indexes sent to PHP, please run this and paste back the results. <?php $query = "select school_course.school_id, school_course.course_id, course.course_id, course.name, course.level, course.price, course.duration, course.info, school.school_id, school.name, school.street, school.town, school.city, school.county, school.region, school.postcode, school.country, school.school_facts, school.general_info, school.school_facilities FROM school_course LEFT JOIN course ON school_course.course_id = course.course_id LEFT JOIN school ON school_course.school_id = school.school_id"; //Use this query below $result = mysql_query($query, $conn) or die ("Unable to perform query"); while($row = mysql_fetch_array($result, MYSQL_ASSOC)) { echo '<pre>' . print_r($row, true) . '\r\n\r\n</pre>'; //echo $row["school.name"]; } ?> Quote Link to comment https://forums.phpfreaks.com/topic/192049-really-need-help-with-sql-join-and-displaying-data/#findComment-1012177 Share on other sites More sharing options...
deansaddigh Posted February 14, 2010 Author Share Posted February 14, 2010 Firstly thanks for taking the time to help me Here is what is printed out, i have included the link http://www.languageschoolsuk.com/list_courses.php Quote Link to comment https://forums.phpfreaks.com/topic/192049-really-need-help-with-sql-join-and-displaying-data/#findComment-1012179 Share on other sites More sharing options...
Andy-H Posted February 14, 2010 Share Posted February 14, 2010 Please change it to this and let me know when you've done it so I can take another look at the output. <?php $query = "SELECT course.course_id, (course.name AS course_name), course.level, course.price, course.duration, course.info, school.school_id, (school.name AS school_name), school.street, school.town, school.city, school.county, school.region, school.postcode, school.country, school.school_facts, school.general_info, school.school_facilities FROM school_course JOIN course ON school_course.course_id = course.course_id JOIN school ON school_course.school_id = school.school_id"; //Use this query below $result = mysql_query($query, $conn) or die ("Unable to perform query"); while($row = mysql_fetch_array($result, MYSQL_ASSOC)) { echo '<pre>' . print_r($row, true) . '</pre>'; //echo $row["school.name"]; } ?> Quote Link to comment https://forums.phpfreaks.com/topic/192049-really-need-help-with-sql-join-and-displaying-data/#findComment-1012181 Share on other sites More sharing options...
deansaddigh Posted February 14, 2010 Author Share Posted February 14, 2010 Hi, i have changed it and now im getting an sql error. http://www.languageschoolsuk.com/list_courses.php Quote Link to comment https://forums.phpfreaks.com/topic/192049-really-need-help-with-sql-join-and-displaying-data/#findComment-1012183 Share on other sites More sharing options...
Andy-H Posted February 14, 2010 Share Posted February 14, 2010 <?php $query = "SELECT course.course_id, course.name AS course_name, course.level, course.price, course.duration, course.info, school.school_id, school.name AS school_name, school.street, school.town, school.city, school.county, school.region, school.postcode, school.country, school.school_facts, school.general_info, school.school_facilities FROM school_course JOIN course ON school_course.course_id = course.course_id JOIN school ON school_course.school_id = school.school_id"; //Use this query below $result = mysql_query($query, $conn) or die ("Unable to perform query: " . mysql_error()); while($row = mysql_fetch_array($result, MYSQL_ASSOC)) { echo '<pre>' . print_r($row, true) . '</pre>'; //echo $row["school.name"]; } ?> Quote Link to comment https://forums.phpfreaks.com/topic/192049-really-need-help-with-sql-join-and-displaying-data/#findComment-1012185 Share on other sites More sharing options...
deansaddigh Posted February 14, 2010 Author Share Posted February 14, 2010 Thanks http://www.languageschoolsuk.com/list_courses.php Quote Link to comment https://forums.phpfreaks.com/topic/192049-really-need-help-with-sql-join-and-displaying-data/#findComment-1012188 Share on other sites More sharing options...
Andy-H Posted February 14, 2010 Share Posted February 14, 2010 Looks like nothing is being returned, have you checked your mysql database to ensure that there are matching id's for the criteria in the linker, course and school tables. NOTE: I canged the LEFT JOIN to JOIN as the arrays were not being populated correctly, this indicates that the JOIN criteria was not being matched and data was being pulled from the course table alone (Due to LEFT JOIN) Quote Link to comment https://forums.phpfreaks.com/topic/192049-really-need-help-with-sql-join-and-displaying-data/#findComment-1012190 Share on other sites More sharing options...
deansaddigh Posted February 14, 2010 Author Share Posted February 14, 2010 Yes i beleive there are. you can check here Quote Link to comment https://forums.phpfreaks.com/topic/192049-really-need-help-with-sql-join-and-displaying-data/#findComment-1012196 Share on other sites More sharing options...
Andy-H Posted February 14, 2010 Share Posted February 14, 2010 You dont have a record in the 'school' table with the 'school_id' of 11... Quote Link to comment https://forums.phpfreaks.com/topic/192049-really-need-help-with-sql-join-and-displaying-data/#findComment-1012198 Share on other sites More sharing options...
deansaddigh Posted February 14, 2010 Author Share Posted February 14, 2010 Woop woop Thanks so much for your help. Quote Link to comment https://forums.phpfreaks.com/topic/192049-really-need-help-with-sql-join-and-displaying-data/#findComment-1012199 Share on other sites More sharing options...
Andy-H Posted February 14, 2010 Share Posted February 14, 2010 NP Quote Link to comment https://forums.phpfreaks.com/topic/192049-really-need-help-with-sql-join-and-displaying-data/#findComment-1012200 Share on other sites More sharing options...
Andy-H Posted February 14, 2010 Share Posted February 14, 2010 P.S You may wat to add something to let a user no when no results are matched - <?php $query = "SELECT course.course_id, course.name AS course_name, course.level, course.price, course.duration, course.info, school.school_id, school.name AS school_name, school.street, school.town, school.city, school.county, school.region, school.postcode, school.country, school.school_facts, school.general_info, school.school_facilities FROM school_course JOIN course ON school_course.course_id = course.course_id JOIN school ON school_course.school_id = school.school_id"; //Use this query below $result = mysql_query($query, $conn) or trigger_error("Unable to perform query: " . mysql_error(), E_USER_ERROR); $n = mysql_num_rows($result); if ($n == 0) { echo 'Sorry, no results matched your search criteria!<br /><br />'; } else { echo number_format($n) . ' matches returned: <br /><br />'; while($row = mysql_fetch_array($result, MYSQL_ASSOC)) { echo '<pre>' . print_r($row, true) . '</pre>'; //echo $row['school_name']; } } ?> Quote Link to comment https://forums.phpfreaks.com/topic/192049-really-need-help-with-sql-join-and-displaying-data/#findComment-1012202 Share on other sites More sharing options...
deansaddigh Posted February 14, 2010 Author Share Posted February 14, 2010 Hi, one thing i was gonna ask is how do i just litterally print out a school name, and then display the courses based on that school. so the school name is mentioned once, then underneath it shows all the courses. Also how do i get rid of it saying array Array ( [course_id] => 46 [course_name] => royal course [level] => begginer [price] => 10 [duration] => 14 [info] => shit [school_id] => 11 [school_name] => SCHOOL 1 [street] => [town] => [city] => [county] => [region] => [postcode] => [country] => [school_facts] => [general_info] => [school_facilities] => ) Quote Link to comment https://forums.phpfreaks.com/topic/192049-really-need-help-with-sql-join-and-displaying-data/#findComment-1012206 Share on other sites More sharing options...
Andy-H Posted February 14, 2010 Share Posted February 14, 2010 <?php $query = "SELECT course.course_id, course.name AS course_name, course.level, course.price, course.duration, course.info, school.school_id, school.name AS school_name, school.street, school.town, school.city, school.county, school.region, school.postcode, school.country, school.school_facts, school.general_info, school.school_facilities FROM school_course JOIN course ON school_course.course_id = course.course_id JOIN school ON school_course.school_id = school.school_id"; //Use this query below $result = mysql_query($query, $conn) or trigger_error("Unable to perform query: " . mysql_error(), E_USER_ERROR); $n = mysql_num_rows($result); if ($n == 0) { echo 'Sorry, no results matched your search criteria!<br /><br />'; } else { while($row = mysql_fetch_array($result, MYSQL_ASSOC)) { if (!isset($done)) { echo '<h2>' . $row['school_name'] . '</h2>' . "\r\n"; echo '<p>School Details</p> <br />'; $done = true; } //echo $row['rest_of_course_info']; } } ?> Replace //echo $row['rest_of_course_info']; With stuff like echo 'Price: £' . number_format($row['price'], 2) . '<br />'."\r\n"; echo 'Difficulty: ' . $row['level'] . '<br />'."\r\n"; Quote Link to comment https://forums.phpfreaks.com/topic/192049-really-need-help-with-sql-join-and-displaying-data/#findComment-1012210 Share on other sites More sharing options...
deansaddigh Posted February 14, 2010 Author Share Posted February 14, 2010 Wow, you star. Thanks again for your help Quote Link to comment https://forums.phpfreaks.com/topic/192049-really-need-help-with-sql-join-and-displaying-data/#findComment-1012211 Share on other sites More sharing options...
Andy-H Posted February 14, 2010 Share Posted February 14, 2010 No problem m8, glad I could help You should really secure your PHPmyadmin account with a password you know lol Quote Link to comment https://forums.phpfreaks.com/topic/192049-really-need-help-with-sql-join-and-displaying-data/#findComment-1012212 Share on other sites More sharing options...
deansaddigh Posted February 14, 2010 Author Share Posted February 14, 2010 I dont know if your still around, just been studying what your wrote etc. Do you know how i can only list the school name one, then the courses, and then a different school name and its respective courses. Quote Link to comment https://forums.phpfreaks.com/topic/192049-really-need-help-with-sql-join-and-displaying-data/#findComment-1012224 Share on other sites More sharing options...
Andy-H Posted February 14, 2010 Share Posted February 14, 2010 Can you show the full page code? Quote Link to comment https://forums.phpfreaks.com/topic/192049-really-need-help-with-sql-join-and-displaying-data/#findComment-1012234 Share on other sites More sharing options...
deansaddigh Posted February 14, 2010 Author Share Posted February 14, 2010 Hi, i have managed to do it, Can you tell me if the way i have done it, would be correct, or is there a better way? $query = "SELECT course.course_id, course.name AS course_name, course.level, course.price, course.duration, course.info, school.school_id, school.name AS school_name, school.street, school.town, school.city, school.county, school.region, school.postcode, school.country, school.school_facts, school.general_info, school.school_facilities FROM school_course JOIN course ON school_course.course_id = course.course_id JOIN school ON school_course.school_id = school.school_id"; //Use this query below $result = mysql_query($query, $conn) or die ("Unable to perform query: " . mysql_error()); // Initialise temp variable $previousschoolname = ""; while($row = mysql_fetch_array($result, MYSQL_ASSOC)) { // Get the school name $schoolname = $row['school_name']; // If the previous school name is equal to the new name then do not print // it out as we only want the same school name printed once if ( $schoolname == $previousschoolname ) { $previousschoolname = ""; } // Else it is a new school so print it out else { echo 'School Name ' . $schoolname . '<br />'."\r\n"; } echo 'Price: £' . number_format($row['price'], 2) . '<br />'."\r\n"; echo 'Difficulty: ' . $row['level'] . '<br /><br />'."\r\n"; // Make temp variable equal the previous school so can be used in check $previousschoolname = $schoolname; } Quote Link to comment https://forums.phpfreaks.com/topic/192049-really-need-help-with-sql-join-and-displaying-data/#findComment-1012236 Share on other sites More sharing options...
deansaddigh Posted February 14, 2010 Author Share Posted February 14, 2010 Oh and my page is http://www.languageschoolsuk.com/list_courses.php it seems to work Quote Link to comment https://forums.phpfreaks.com/topic/192049-really-need-help-with-sql-join-and-displaying-data/#findComment-1012238 Share on other sites More sharing options...
Andy-H Posted February 14, 2010 Share Posted February 14, 2010 Pretty much mate, just 1 bit of redundant code there: $query = "SELECT course.course_id, course.name AS course_name, course.level, course.price, course.duration, course.info, school.school_id, school.name AS school_name, school.street, school.town, school.city, school.county, school.region, school.postcode, school.country, school.school_facts, school.general_info, school.school_facilities FROM school_course JOIN course ON school_course.course_id = course.course_id JOIN school ON school_course.school_id = school.school_id"; //Use this query below $result = mysql_query($query, $conn) or die ("Unable to perform query: " . mysql_error()); // Initialise temp variable $previousschoolname = ""; while($row = mysql_fetch_array($result, MYSQL_ASSOC)) { // Get the school name $schoolname = $row['school_name']; // If the previous school name is equal to the new name then do not print // it out as we only want the same school name printed once if ( $schoolname != $previousschoolname ) { echo '<h2>School Name: ' . $schoolname . '</h2><br />'."\r\n"; } echo '<br />' . "\r\n"; echo 'Course name: ' . ucfirst($row['course_name']) . '<br />'."\r\n"; echo 'Course description: ' . $row['info'] . '<br />'."\r\n"; echo 'Price: £' . number_format($row['price'], 2) . '<br />'."\r\n"; echo 'Duration: ' . $row['duration'] . ' weeks<br />'."\r\n"; echo 'Difficulty: ' . ucfirst($row['level']) . '<br /><br />'."\r\n"; echo '<hr />' . "\r\n"; // Make temp variable equal the previous school so can be used in check $previousschoolname = $schoolname; } Should work ok. Quote Link to comment https://forums.phpfreaks.com/topic/192049-really-need-help-with-sql-join-and-displaying-data/#findComment-1012242 Share on other sites More sharing options...
deansaddigh Posted February 14, 2010 Author Share Posted February 14, 2010 Brilliant Thanks so much. Thats me done ill stop bothering you now. Thanks again you have been a brilliant help Quote Link to comment https://forums.phpfreaks.com/topic/192049-really-need-help-with-sql-join-and-displaying-data/#findComment-1012249 Share on other sites More sharing options...
Andy-H Posted February 14, 2010 Share Posted February 14, 2010 No problem, I edited the code when I noticed you were using horisontal rule tags to seperate the courses, you may want to update it. Quote Link to comment https://forums.phpfreaks.com/topic/192049-really-need-help-with-sql-join-and-displaying-data/#findComment-1012250 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.