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
-- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
