Hi,
I am using the following query to get results from a mysql database.
$query = 'SELECT * FROM Assignments, Classes, Instructors, Departments';
$query .= ' WHERE Assignments.ClassID = Classes.ClassID';
$query .= ' AND Classes.DepartmentID = Departments.DepartmentID';
$query .= ' AND Classes.InstructorID = Instructors.InstructorID';
If($departmentid != "") $query .= ' AND Departments.DepartmentID = ' .
$departmentid;
if($instructorid !="") $query .= ' AND Instructors.InstructorID = ' .
$instructorid;
If($orderby != "") $query .= ' ORDER BY ' . $orderby;
$query .= ' LIMIT ' . ((0 + $page - 1) * $perpage) . ',' . $perpage;
This works fine but I need to count the number of matches that would have
occurred if I didn't specify a LIMIT.
I tried wrapping COUNT() around the whole Query string minus the LIMIT and
ORDER but the following errors occurred -
mysql query -
SELECT COUNT(SELECT * FROM Assignments, Classes, Instructors, Departments WHERE
Assignments.ClassID = Classes.ClassID AND
Classes.DepartmentID = Departments.DepartmentID AND Classes.InstructorID =
Instructors.InstructorID)
PHP error -
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result
resource in C:\wamp\www\database\assignments.php on line
122
mysql 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 * FROM Assignments, Classes, Instructors, Departments WHE
In the above query I could just do 'SELECT COUNT(SELECT * FROM Assignments)'
But I need queries like -
SELECT COUNT(SELECT * FROM Assignments, Classes, Instructors, Departments WHERE
Assignments.ClassID = Classes.ClassID AND
Classes.DepartmentID = Departments.DepartmentID AND Classes.InstructorID =
Instructors.InstructorID AND Departments.DepartmentID =
5)
I am only new to mysql so any help would be much appreciated.
Robert.
