Problem with PHP/SQL Query (working with foreign keys)

Posted 09 October 2011 - 02:00 AM

Hello,

I have created 3 tables: trainer, course, trainerCourses.

Table trainer has the following fields: trainerID(PK) , trainerName, email and a brief summary.

Table course has the following fields: courseID(PK), courseDate and trainerName(FK)

Table trainerCourses has the following fields: courseID(FK), trainerID(FK) and attendanceStatus (int where 0 is absent and 4 is present)

I want to write a PHP page to display trainerName where next each name is the number of all the courses they teach and the number of attendances they have.

I am a little confused as to how to do this because of the foreign keys.

This is my query code so far but it returns an empty result set. :s

SELECT COUNT(course.courseID)
FROM course, trainer
WHERE course.courseID = trainer.trainerID;

SELECT COUNT(trainerCourses.attendanceStatus) 
FROM course, trainerCourses
WHERE trainerCourses.courseID = course.courseID AND trainerCourses.attendanceStatus = '4';

SELECT trainer.trainerName
FROM course, trainer
WHERE course.courseID = trainer.trainerID;

This is also my php page:

<html>
<head>
	<title>Test Page</title>
</head>
<body>

<?php

	$link = mysql_connect('127.0.0.1');
	
	if (!$link)
  	{
  		die('Could not connect: ' . mysql_error());
  	}// open connection


	mysql_select_db('task4', $link); // database selection
	$c1 = mysql_query("SELECT COUNT(course.courseID)
					   FROM course, trainer
					   WHERE course.courseID = trainer.trainerID");
					   
	$c2 = mysql_query("SELECT COUNT(trainerCourses.attendanceStatus)
					   FROM course, trainer
					   WHERE course.courseID = trainer.trainerID AND trainerCourses.courseID = course.courseID
					   											 AND trainerCourses.attendanceStatus = '4'");
					   											 
	$n = mysql_query("SELECT trainer.trainerName
					  FROM trainerCourse, trainer
					  WHERE trainerCourse.courseID = trainer.trainerID");


	while ($row = mysql_fetch_array($n))
	{
		echo "Trainer Name: ".$row."<br>";
		echo "Number of Courses: ".$c1."<br>";
		echo "Number of Attendances: ".$c2."<br>";
		echo "---------------------------------------------<br><br>";
	}
	
	mysql_close($link);	//close connection to db 
		
?>
</body>
</html>

In my php page, the only error I get in this "Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in" from the while loop condition [while ($row = mysql_fetch_array($n))].

Can anyone help me by reviewing my code and telling me what I'm doing wrong?

Thank you.