Jump to content

mysql_num_rows Question


hostfreak

Recommended Posts

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.
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.