mysql_num_rows Question

[hostfreak]

I am trying to make a statistics page to show how many employee's are at each plant. I know I could do it this way for each plant:

[code]
$query = "SELECT employee_num FROM users WHERE userlevel = '1' AND location = 'Punta Gorda'";
$result = mysql_query($query) or die(mysql_error());
$num_rows = mysql_num_rows($result);
echo "<font face='Tahoma' size='2'>There are total of <b>$num_rows</b> employee's in the <b>Punta Gorda</b> plant</font><br />";
[/code]

So would I need to query each plant like that or could I do mysql_num_rows in a loop somehow? All the plants are contained in the field "location". Thanks in advance for any responses.

[sasa]

try[code]
$query = "SELECT COUNT(employee_num) AS num, location FROM users WHERE userlevel = '1' GRUP BY location'";
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_affected_rows($rwsult))
echo "<font face='Tahoma' size='2'>There are total of <b>$row['num']</b> employee's in the <b>$row['location']</b> plant</font><br />";
[/code]

[hostfreak]

Your code didn't work, had some errors. I was able (I think) fix it though and came up with:

[code]
$query = "SELECT COUNT(employee_num) AS num, location FROM users WHERE userlevel = '1' GROUP BY location";
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_affected_rows($result)) {
$num = $row["num"];
$location = $row["location"];
echo "<font face='Tahoma' size='2'>There are total of <b>$num</b> employee's in the <b>$location</b> plant</font><br />";
[/code]

Then got:
[code]
Warning: mysql_affected_rows(): supplied resource is not a valid MySQL-Link resource in *path*/test.php on line 10
[/code]

After looking at the php.net manual for mysql_affected_rows, I don't think it will do the trick.

[sasa]

sorry i make mistake row[code]while ($row = mysql_affected_rows($rwsult))[/code]must be[code]while ($row = mysql_fetch_array($result))[/code] not $rwsult  ???

[hostfreak]

There were a couple small errors, but now it works with mysql_fetch_array. Well, almost works. For some reason it has added a plant that doesn't even exist (along with saying it contains employees) and at the top it added:
[code]
There are total of 1 employee's in the  plant
[/code]

The plant it added, that doesn't exist is:
[code]
There are total of 5 employee's in the Placidia plant
[/code]

Just above that plant is:
[code]
There are total of 11 employee's in the Placida plant
[/code]

Which does exist. There are a couple plants that don't have any employee's in them, but I don't think that would have anything to do with it.

Oh, and with your suggestions, here is the code I am using (slight modifications):
[code]
$query = "SELECT COUNT(employee_num) AS num, location FROM users WHERE userlevel = '1' GROUP BY location";
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
$num = $row['num'];
$location = $row['location'];
echo "<font face='Tahoma' size='2'>There are total of <b>$num</b> employee's in the <b>$location</b> plant</font><br />";
}
[/code]

[HeyRay2]

Do you have any employess that have no location associated?

[hostfreak]

Got the problems fixed. Was a spelling error on one of the plants. Thanks for menetioning that HeyRay, made me check. Thanks guys for your help.

[hostfreak]

Is there any way I could get the plants to show up the don't have any employee's under them as well? Like for example, loop through all the locations and if one was to come up where there were no employee's in it, it will still show up, but as:
[code]
There are total of 0 employee's in the Location plant
[/code]

Thanks once again.

[HeyRay2]

To do what you're asking, you'll need to make a [b]locations[/b] table, and then change the [b]location[/b] field in the [b]users[/b] table to be a foreign key of [b]locations[/b] (a number pointing to a certain location ID in the [b]locations[/b] table). Your tables could be set up like so:

[b]users[/b]
employee_num
employee_name
user_level
location_id

[b]locations[/b]
location_id
location_name

You could then run a query for all the locations, then loop through each location for a count of the employees for that location:

[code]
<?php

// Query the locations
$query_loc = "SELECT * FROM locations ORDER BY location_name ASC";
$result_loc = mysql_query($query_loc) or die("Error Querying Locations: ".mysql_error());

while($row_loc = mysql_fetch_array($result_loc)){
// Query for employee count for each locations
$query_emps = "SELECT COUNT(*) FROM users WHERE location_id = ".$row_loc['location_id']."";
$result_emps = mysql_query($query_emps) or die("Error Querying Employees: ".mysql_error());

$emp_count = mysql_result($result_emps, 0);

// Print the employee count
echo 'There are total of '.$emp_count.' employees in the '.$row_loc['location_name'].' plant.<br />';
}

?>
[/code]