Slow Page loading due to sql query

[steve deboeck]

Hey everyone,

first of all, i am obligated to say that i use postgresql instead of mysql (choice of my boss, not mine) but I'm sure that's irrelevant to my problem.

I work in a school and a colleague of mine made a photo book for the teachers. This photo book shows the teacher's name, photo and the courses he/she gives.

The way he designed it, was that when the teacher is giving a course to different classes (biology first year, biology second year, etc) it shows every time. I've made some changes so that it only shows biology once.

This was the little introduction. I will show you the code now.
First of all, i will describe the tables.

COURSES


Teachers


Teacher_course (a way to combine the tables above)



Next i show you my colleague's code


function get_courses_by_teacher($user_id) {
global $pgconn;
$query = "SELECT course FROM teacher_course WHERE id_teacher = '{$user_id}'";
$result_set = pg_query($pgconn,$query);
confirm_query($result_set);
return $result_set;
}

function get_course_name($course_code) {
global $pgconn;
$query = "SELECT c_name FROM courses WHERE course = '{$course_code}'";
$result_set = pg_query($pgconn,$query);
confirm_query($result_set);
return $result_set;
}


and his code on the web page itself:



$res = get_courses_by_teacher($array[$l][0]);
//$i=0;
while ($row = pg_fetch_row($res)) {
//$array[$i] = $row;
//$i++;
$res2 = get_course_name($row[0]);
$row2 = pg_fetch_row($res2);
echo $row2[0]." ({$row[0]})";
echo "<br>";


My function to get the unique courses:


function get_unique_courses_by_teacher($user_id) {
global $pgconn;
$query = "SELECT DISTINCT c_name FROM courses WHERE course = (SELECT course FROM teacher_course WHERE id_teacher = '{$user_id}' AND courses.course = teacher_course.course)";
$result_set = pg_query($pgconn,$query);
confirm_query($result_set);
return $result_set;
}


and my code that replaced his code on the web page:


$res = get_unique_courses_by_teacher($array[$l][0]);
while ($row = pg_fetch_row($res)) {
echo $row[0];
echo "<br>";
}
}


The array that you see comes from this:

$res = get_all_teachers();
$i=0;
while ($row = pg_fetch_row($res)) {
$array[$i] = $row;
$i++;
}

Basically, it puts the information of every teacher in the array.



My code works, it does what it has to do, as expected. Instead of showing:

Art (s1art--a)
Art (s1art--h)
Art (s2art--j)
Art (s3art--a)
Art (s3art--g)
Art (s3art--h)
Art (s3art--e)


it shows only one "Art". The only problem is, it takes like 15 to 20 seconds to load the page.

If anyone can identify the problem, I'd be very thankful, or if you have some questions, I'd be happy to answer them.