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

x
  • 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!

Thanks for any help provided.
0
wantabe2
Asked:
wantabe2
1 Solution
 
Ephraim WangoyaCommented:

select WORKERS, Count(*) as [Assigned Work]
from ASSIGNMENTS
group by WORKERS
0
 
Ray PaseurCommented:
0
 
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>
      <thead>
      <tr>
	   <table border='5'>

<th>Worker Name</th>
<th>Assignments</th>

      </tr>      
      </thead>
      <tbody>
<?php
require('connection.php');

$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 )) {
?>
       <tr>
           			
            <td><?php echo "".$row['worker']; ?></td>
            <td><?php echo "".$row['assignments']; ?></td>

      </tr>
<?php } ?>            
      </tbody>
 
</table>

Open in new window

0
 
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
0

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