PHP and SQL

[eluser]maniac[/eluser]
I made a view in sql which uses the count method -

CREATE VIEW courses_report_IT AS
SELECT deptname, a.courseid, a.coursename, COUNT(a.courseid) AS "Course Count"
FROM Course a, Department b, Employee c
JOIN Result d WHERE a.courseid = d.courseid
AND b.deptid = c.deptid
AND c.empid = d.empid
AND b.deptid = "2"
GROUP BY courseid;

How do I show the count column in the table? Everything I try comes up in an error. I have called my row in the table as "coursecount", but not sure how to work it in my model.

So far, my model has -

function get_coursecount_it_view()
{
$query=$this->db->query('select * from courses_report_it');
return $query->result();
}

I assume I have to add some sql code between the $query and the return or am I completely wrong?

Thanks

[eluser]steelaz[/eluser]
Try changing

Code:

SELECT deptname, a.courseid, a.coursename, COUNT(a.courseid) AS "Course Count"

with

Code:

SELECT deptname, a.courseid, a.coursename, COUNT(a.courseid) AS soursecount

If you're not sure if you're getting correct results, try running your query in phpMyAdmin first. You can also dump() result returned from get_coursecount_it_view() to see what you're working with.

[eluser]maniac[/eluser]
Thank you. Changed the sql and all worked well. I was always taught to put the new column in "", but for this, it shouldn't be done.

Thanks again,
Maniac

[eluser]EmmaMadeleine[/eluser]
thnxx to all