Jump to content


Photo

mysql_num_rows Question


  • Please log in to reply
8 replies to this topic

#1 hostfreak

hostfreak
  • Members
  • PipPipPip
  • Advanced Member
  • 581 posts

Posted 25 August 2006 - 05:28 PM

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:

$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 />";

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.

#2 sasa

sasa
  • Staff Alumni
  • Advanced Member
  • 2,804 posts
  • LocationHrvatska

Posted 25 August 2006 - 05:50 PM

try
$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 />";


#3 hostfreak

hostfreak
  • Members
  • PipPipPip
  • Advanced Member
  • 581 posts

Posted 25 August 2006 - 06:03 PM

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

$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 />";

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

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

#4 sasa

sasa
  • Staff Alumni
  • Advanced Member
  • 2,804 posts
  • LocationHrvatska

Posted 25 August 2006 - 06:12 PM

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

#5 hostfreak

hostfreak
  • Members
  • PipPipPip
  • Advanced Member
  • 581 posts

Posted 25 August 2006 - 06:27 PM

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:
There are total of 1 employee's in the  plant

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

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

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):
$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 />";
}


#6 HeyRay2

HeyRay2
  • Members
  • PipPipPip
  • Advanced Member
  • 223 posts

Posted 25 August 2006 - 06:39 PM

Do you have any employess that have no location associated?

#7 hostfreak

hostfreak
  • Members
  • PipPipPip
  • Advanced Member
  • 581 posts

Posted 25 August 2006 - 06:48 PM

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.

#8 hostfreak

hostfreak
  • Members
  • PipPipPip
  • Advanced Member
  • 581 posts

Posted 25 August 2006 - 07:35 PM

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:
There are total of 0 employee's in the Location plant

Thanks once again.

#9 HeyRay2

HeyRay2
  • Members
  • PipPipPip
  • Advanced Member
  • 223 posts

Posted 25 August 2006 - 09:12 PM

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

users
employee_num
employee_name
user_level
location_id

locations
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:

<?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 />';
}

?>





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users