Really need help with sql join and displaying data

deansaddigh · 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"];
			}



            ?>

Andy-H · 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"];
            }
            
                     
            
            ?>

deansaddigh · 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

Andy-H · 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"];
            }
            
                     
            
            ?>

deansaddigh · February 14, 2010

Hi, i have changed it and now im getting an sql error.

http://www.languageschoolsuk.com/list_courses.php

Andy-H · 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"];
            }
            
                     
            
            ?>

deansaddigh · February 14, 2010

Thanks

http://www.languageschoolsuk.com/list_courses.php

Andy-H · 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)

deansaddigh · February 14, 2010

Yes i beleive there are.

you can check here

Andy-H · February 14, 2010

You dont have a record in the 'school' table with the 'school_id' of 11...

deansaddigh · February 14, 2010

Woop woop

Thanks so much for your help.

Andy-H · February 14, 2010

NP

Andy-H · 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'];
                  }
               }
            
                     
            
            ?>

deansaddigh · 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] => 
)

Andy-H · 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";

deansaddigh · February 14, 2010

Wow, you star.

Thanks again for your help

Andy-H · February 14, 2010

No problem m8, glad I could help

You should really secure your PHPmyadmin account with a password you know lol

deansaddigh · 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.

Andy-H · February 14, 2010

Can you show the full page code?

deansaddigh · 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;
            }

deansaddigh · February 14, 2010

Oh and my page is

http://www.languageschoolsuk.com/list_courses.php

it seems to work

Andy-H · 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.

deansaddigh · February 14, 2010

Brilliant

Thanks so much.

Thats me done ill stop bothering you now.

Thanks again you have been a brilliant help

Andy-H · 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.

Sign In

Really need help with sql join and displaying data

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information