SELECT COUNT Help

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
honkmaster
Forum Newbie
Posts: 12
Joined: Mon Feb 15, 2010 12:10 pm

SELECT COUNT Help

Post by honkmaster »

Hi, I'm quite new to PHP so sorry if this is basic.

I have a MySQL database with a table in called status. There is 6 status possibilities

STATUS
In Progress
Order Received
Waiting for Artwork
Waiting for Approval
Complete
On Hold

So far this is what I have got, it counts the status and groups them and presents back as screen shot attached (Fig1) This is great but what I want it to do is present results in a table format like screen shot attached (Fig2). Where there is know result I would like a "0"

Can anyone point me in the right direction

Code: Select all

 
<?php
$username="XXXX";
$password="XXXX";
$database="XXXX";
 
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
 
$query = "SELECT status, COUNT(Status) FROM main_data GROUP BY status"; 
     
$result = mysql_query($query) or die(mysql_error());
 
// Print out result
while($row = mysql_fetch_array($result)){
    echo "There are ". $row['COUNT(Status)'] ." Records at ". $row['status'] ." Status.";
    echo "<br />";
}
?>
Attachments
fig2
fig2
fig2.png (19.72 KiB) Viewed 318 times
Fig1
Fig1
fig1.png (12.83 KiB) Viewed 318 times
Last edited by Benjamin on Thu Mar 18, 2010 3:16 am, edited 1 time in total.
Reason: Added [code=php] tags.
honkmaster
Forum Newbie
Posts: 12
Joined: Mon Feb 15, 2010 12:10 pm

Re: SELECT COUNT Help

Post by honkmaster »

Thanks for Help, but I'm still a bit confused, below is where I have got to, is this correct so far? I need the result of the COUNT query to display in corresponding table. I have formatted the table but not sure where to go from here, sorry Thanks in advance.

Code: Select all

<?php
$username="XXXX";
$password="XXXX";
$database="XXXX";
 
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
 
$query = "SELECT status, COUNT(status) FROM main_data GROUP BY status"; 
     
$result = mysql_query($query) or die(mysql_error());
 
// Print out result
while($myrow = pg_fetch_assoc($result)) {
            printf ("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>",
htmlspecialchars($myrow['In Progress']),
htmlspecialchars($myrow['Order Received']),
htmlspecialchars($myrow['Waiting for Artwork']),
htmlspecialchars($myrow['Waiting for Approval']),
htmlspecialchars($myrow['Complete']),
htmlspecialchars($myrow['On Hold'])
);
}
?>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
<style type="text/css">
<!--
#new {
    font-family: Verdana, Geneva, sans-serif;
    font-size: 12px;
    color: #0f4b9d;
    background-color: #b7c9e1;
    padding: 15px;
    height: 100px;
    width: 400px;
    text-align: left;
}
#new table tr td2 {
    color: #0F0;
}
-->
</style>
</head>
 
<body>
<div id="new">CURRENT STATUS REPORT
  <br />
  <table width="397" cellpadding="4" cellspacing="4">
    <tr>
      <td width="131">In Progress</td>
      <td width="38" align="center" bgcolor="#FFFFFF">0</td>
      <td width="136">Waiting for Approval</td>
      <td width="38" align="center" bgcolor="#FFFFFF">0</td>
    </tr>
    <tr>
      <td>Order Received</td>
      <td align="center" bgcolor="#FFFFFF">0</td>
      <td>Complete</td>
      <td align="center" bgcolor="#FFFFFF">0</td>
    </tr>
    <tr>
      <td>Waiting for Artwork</td>
      <td align="center" bgcolor="#FFFFFF">0</td>
      <td>On Hold</td>
      <td align="center" bgcolor="#FFFFFF">0</td>
    </tr>
  </table>
</div>
</body>
</html>
honkmaster
Forum Newbie
Posts: 12
Joined: Mon Feb 15, 2010 12:10 pm

Re: SELECT COUNT Help

Post by honkmaster »

Still lost on this anyone else help?
Post Reply