PHP Query Table Code

The code below works fine but it displays the information in three diffeent tables becuase it is three different queries. Can someone assist me in editing the query so the information will display in one table? For instance, I need the table to display the employee name, pending assignments, finished assignments, total assignments in one table instead of showing the info in three different tables. I just can't figure out how to edit the query to show the info i nthe one table.

<table>
      <thead>
      <tr>
	   <table border='5'>

<th>Employee Name</th>
<th>Pending Assignments</th>

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

$query="SELECT employee, active, COUNT(employee) FROM info WHERE active = '1' AND employee IN ('employee1','employee2') GROUP BY employee";
$result = mysql_query($query) or die(mysql_error());  
 
while($row = mysql_fetch_array( $result )) {
?>
       <tr>
           			
            <td><center><?php echo "".$row['employee']; ?></center></td>
            <td><center><?php echo "".$row['COUNT(employee)']; ?></center></td>

      </tr>

<?php } ?>







<table>
      <thead>
      <tr>
	   <table border='5'>

<th>Employee Name</th>
<th>finished Assignments</th>

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

$query="SELECT employee, active, COUNT(employee) FROM info WHERE active = '0' AND employee IN ('employee1','employee2') GROUP BY employee";
$result = mysql_query($query) or die(mysql_error());  
 
while($row = mysql_fetch_array( $result )) {
?>
       <tr>
           			
            <td><center><?php echo "".$row['employee']; ?></center></td>
            <td><center><?php echo "".$row['COUNT(employee)']; ?></center></td>

      </tr>

<?php } ?>







<table>
      <thead>
      <tr>
	   <table border='5'>

<th>Employee Name</th>
<th>Total Assignments</th>

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

$query="SELECT employee, active, COUNT(employee) FROM info WHERE employee IN ('employee1','employee2') GROUP BY employee";
$result = mysql_query($query) or die(mysql_error());  
 
while($row = mysql_fetch_array( $result )) {
?>
       <tr>
           			
            <td><center><?php echo "".$row['employee']; ?></center></td>
            <td><center><?php echo "".$row['COUNT(employee)']; ?></center></td>

      </tr>

<?php } ?>

Open in new window

LVL 15
wantabe2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gr8gonzoConsultantCommented:
First, I'd normally use one query for this, but for the sake of keeping it closer to the example you provided, I would suggest at least eliminating the last query. You have the data you need in the first two in order to calculate the total number.

My suggestion:
<?php
require('connection.php');

// Start with an empty array
$arrEmpAssignments = array();

// Pull the pending count first
$query="SELECT employee, COUNT(employee) as numPending FROM info WHERE active = '1' AND employee IN ('employee1','employee2') GROUP BY employee";
$result = mysql_query($query) or die(mysql_error());  
 
while($row = mysql_fetch_assoc( $result )) {
  $arrEmpAssignments[$row["employee"]] = array("pending" => $row["numPending"], "finished" => 0);
}

// Update the array with the finished count
$query="SELECT employee, COUNT(employee) as numFinished FROM info WHERE active = '0' AND employee IN ('employee1','employee2') GROUP BY employee";
$result = mysql_query($query) or die(mysql_error());  
 
while($row = mysql_fetch_assoc( $result )) {
  $arrEmpAssignments[$row["employee"]]["finished"] = $row["numFinished"];
}

// Now display the data
?>
<table>
      <thead>
      <tr>
	   <table border='5'>

<th>Employee Name</th>
<th>pending Assignments</th>
<th>finished Assignments</th>
<th>total Assignments</th>

      </tr>      
      </thead>
      <tbody>
<?php
foreach($arrEmpAssignments as $employee => $empNumbers)
{
echo "<tr>";
echo "<td><center>" . $employee . "</center></td>\n";
echo "<td><center>" . $empNumbers["pending"] . "</center></td>\n";
echo "<td><center>" . $empNumbers["finished"] . "</center></td>\n";
echo "<td><center>" . ($empNumbers["pending"] + $empNumbers["finished"]) . "</center></td>\n";
echo "</tr>\n";
}
?>
</tbody>
</table>

Open in new window


Now, all this said, you have got a TON of problems in this code. I would suggest that if this is some kind of homework exercise, that you try and figure out how my suggested code works without further questions. Otherwise, you're not learning anything.

You also are using <center> tags, which are deprecated (and even if they weren't, they shouldn't be used inside <td> tags - use the align property of the TD tag instead or better yet, CSS), and you're not closing your tables properly.

I'd also recommend checking out the minute changes I made to the queries and the code so you can get an idea of how to improve them a bit. Like I said before, you could get everything into one query, but that will be up to you.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gr8gonzoConsultantCommented:
Oh, and I'd also suggest using mysqli_ functions, since mysql_ is deprecated.
0
Ray PaseurCommented:
I think this query alone will give you the data you need.
SELECT employee, active, COUNT(employee) FROM info WHERE employee IN ('employee1','employee2') GROUP BY employee

The other two queries would create a results set that would be a subset of this results set.  Suggest you try running this query, then printing the results set, something like this.  If all of the data you need is present in the printout, you can format it for web display.  Use "view source" to read the output.
<?php
require('connection.php');

$query ="SELECT employee, active, COUNT(employee) FROM info WHERE employee IN ('employee1','employee2') GROUP BY employee";
$result = mysql_query($query) or die(mysql_error());  
 
while($row = mysql_fetch_assoc( $result )) {
    var_dump($row);
}

Open in new window

Also, what @gr8gonzo said about MySQL.  Some guidance is available here:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0
wantabe2Author Commented:
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.