2. PHPRunner General questions
  3. Displaying count from another table on list page
This topic is locked

Displaying count from another table on list page

 		8/30/2006 2:01:56 PM
PHPRunner General questions
jwoker author

I have a table courses and on it's list page I would like to show the number of students enrolled in a particular course. The enrollment information is kept in table user_students which holds studentid and courseid. I don't think I can use a query with a join because then I won't be able to edit courses.
Any ideas?
J
 		Jane 8/31/2006

Hi,
you can create query with inner join clause and then uncheck this count field from ADD/EDIT page on the Choose field tab.

Here is a sample query:

select `courses`.`field1`,

`courses`.`field2`,

count(`user_students`.`studentid`)

from `courses` inner join `user_studetns`

on (`courses`.`field1`=`user_students`.`courseid`)

group by `courses`.`field1`



where `field1` is your actual field name where course id is stored.
jwoker author 8/31/2006

Thanks, that worked well except that it excluded courses with 0 enrollments from showing up in my course list <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=10841&image=1&table=forumreplies' class='bbc_emoticon' alt=':(' /> because if there are no enrolments there is no entry in user_students for that course.
J
 		Jane 9/1/2006

Hi,
try to use left join clause:

select `courses`.`field1`,

`courses`.`field2`,

count(`user_students`.`studentid`) as `students`

from `courses` left join `user_students`

on (`courses`.`field1`=`user_students`.`courseid`)

group by `courses`.`field1`