[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

Can I do this with a SQL staement?

I have a huge MySQL database that I keep track of worker assignments that have been assigned by supervisors. I have some PHP forms the supervisors assign things to workers via a browser. My question is, is there a way for me to use a SQL query to look at the MySQL database & count & display the number of assignments that is assigned to each worker? For instance, the database name is DATABASE1 & the table is named ASSIGNMENTS. In the ASSIGNMENTS table I have a field named WORKERS. The workers are identified in this field by worker1, worker2, worker3.....I would like to have a SQL that will look at the WORKERS field name & count how many times it sees worker1 & display it in a browser. I assume I would use PHP code to do this??? It would look like something below:

[b]Worker Name[/b]  [b]Assigned Work[/b]
Worker1               5
Worker2               3
Worker3               2


Thanks for any help provided.
1 Solution
Ephraim WangoyaCommented:

select WORKERS, Count(*) as [Assigned Work]
group by WORKERS
Ray PaseurCommented:
wantabe2Author Commented:
okay, I'm still having issues with this....I have my php page like I need it in to display but still having problems with the SQL...

I've looked at the example above but just can't get a grasp around it...my brain is overloaded today.

To make it more simple for me I would like to just have a SQL that counts the number of assignments for worker1 & displays it in a browser like below:

[b]Worker Name   Assignments[/b]
Worker1                 24

How can I get the SQL to just count each time it see's WORKER1 name listed in the worker field? This would be one way to get what I need.
	   <table border='5'>

<th>Worker Name</th>


$query="SELECT worker, assignments COUNT(assignments) FROM psrinfo WHERE location = 'office1' AND worker = 'worker1' ";
$result = mysql_query($query) or die(mysql_error());  
while($row = mysql_fetch_array( $result )) {
            <td><?php echo "".$row['worker']; ?></td>
            <td><?php echo "".$row['assignments']; ?></td>

<?php } ?>            

Open in new window

Ephraim WangoyaCommented:

That will work for one worker, but if you need a list of workers each with assignment count then you need to add the group by clause

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now