I'm trying to get the total number of a certain records from a database,
but the result is always '1'. Please advise!
=MySql Table =
=activitiy =
id | employee_id | project_id | date
1 | 45 | 60 | 2003-09-09
2 | 34 | 10 | 2003-09-10
3 | 45 | 45 | 2003-09-10
4 | 23 | 30 | 2003-04-11
Now, I'm trying to get the following info:
- Total Number of employees in the datbase
- Total number of projects
-total number of projects per employee
I currently have
$_sql = "select count(*) as TotalRecords, count(employee_id) as
TotalEmployees, count(project_id) as TotalProjects GROUP BY employee_id,
project_id
$_qry = mysql_query($_sql) or die('...blah...');
$_res = mysql_fetch_object($_qry);
//output
echo $_res->TotalRecords; //prints nothing
echo $_res->TotalEmployees; // returns 1
echo $_res->TotalProjects; //also return 1
Your query doesn't even have a "FROM Table" in it. Hopefully that's just a cut and paste error, as you should see your "die" message because of that error.
You're not going to get the results you're after with a single query (unless you have PHP do some of the counting). Look at using a couple different queries to get your count.
One solution may be:
SELECT employee_id, COUNT(*) AS NumProjects FROM activity GROUP BY employee_id;
The number of rows returned will be the number of employees. Each row will list the employee_id and how many times it appeared in the table. If project_id is not repeated for a given employee_id, then this will be your project count. As you loop throught the results, keep a running total of the NumProjects column to get the total projects.
--
---John Holmes...
Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/
php|architect: The Magazine for PHP Professionals – www.phparch.com