On Oct 12, 2008, at 6:31 PM, <[EMAIL PROTECTED]> <[EMAIL PROTECTED] > wrote:
> 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. Try: SELECT COUNT(*) FROM Assignments, Classes, Instructors, Departments WHERE Assignments.ClassID = Classes.ClassID AND Classes.DepartmentID = Departments.DepartmentID AND Classes.InstructorID = Instructors.InstructorID What gets returned is one row with one field (You have to read that... calling mysql_num_rows() will return 1) -D. Israel [EMAIL PROTECTED] http://www.customcodebydan.com AIM: JudoDanIzz [Non-text portions of this message have been removed]
