SELECT COUNT help

[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 no result I would like a "0"

 

Can anyone point me in the right direction

 

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

 

 

[attachment deleted by admin]

[scvinodkumar]

are u maintaining status values in separate table? if u so you can do with mysql join queries... else you need to put if condition in your code

[honkmaster]

Hi All status controls in the same table, the code I have returns as lines which works but I want to return the status as in fig 2 in a table format. Cheers Chris

 

are u maintaining status values in separate table? if u so you can do with mysql join queries... else you need to put if condition in your code

[Psycho]

Yeah, having the status names in an associated table with the status IDs as a foreign key in the main_data table would be a better approach, but this should work with what you have:

 

$counts = array(
    'In Progress' => 0,
    'Order Received' => 0,
    'Waiting for Artwork' => 0,
    'Waiting for Approval' => 0,
    'Complete' => 0,
    'On Hold' => 0
);

$query = "SELECT status, COUNT(Status) as count FROM main_data GROUP BY status";
$result = mysql_query($query) or die(mysql_error());
while($record = mysql_fetch_assoc($result))
{
    $counts[$record['status']] = $record['count'];
}

$output  = "<table>\n";
$output .= "  <tr><th colspan=\"4\">CURRENT STATUS REPORT</th></tr>\n";
$record = 0;
foreach ($counts as $status => $value)
{
  $record++;
  if ($record%2==1)
  {
    $output .= "  <tr>\n";
  }
  $output .= "    <td>{$status}</td>\n";
  $output .= "    <td>{$value}</td>\n";
  if ($record%2==0)
  {
    $output .= "  </tr>\n";
  }
}
$output  = "</table>\n";

echo $output;

[honkmaster]

Guru, thanks that makes sense to me know. I have tried the code below on my database and I just get a black screen without any error message. Thanks for help , Cheers Chris

 

<?php
// Make a MySQL Connection
mysql_connect("localhost", "XXXX", "XXXX") or die(mysql_error());
mysql_select_db("XXXX") or die(mysql_error());

$counts = array(
    'In Progress' => 0,
    'Order Received' => 0,
    'Waiting for Artwork' => 0,
    'Waiting for Approval' => 0,
    'Complete' => 0,
    'On Hold' => 0
);

$query = "SELECT status, COUNT(Status) as count FROM main_data GROUP BY status";
$result = mysql_query($query) or die(mysql_error());
while($record = mysql_fetch_assoc($result))
{
    $counts[$record['status']] = $record['count'];
}

$output  = "<table>\n";
$output .= "  <tr><th colspan=\"4\">CURRENT STATUS REPORT</th></tr>\n";
$record = 0;
foreach ($counts as $status => $value)
{
  $record++;
  if ($record%2==1)
  {
    $output .= "  <tr>\n";
  }
  $output .= "    <td>{$status}</td>\n";
  $output .= "    <td>{$value}</td>\n";
  if ($record%2==0)
  {
    $output .= "  </tr>\n";
  }
}
$output  = "</table>\n";

echo $output;

?>

 

 

Yeah, having the status names in an associated table with the status IDs as a foreign key in the main_data table would be a better approach, but this should work with what you have:

 

$counts = array(
    'In Progress' => 0,
    'Order Received' => 0,
    'Waiting for Artwork' => 0,
    'Waiting for Approval' => 0,
    'Complete' => 0,
    'On Hold' => 0
);

$query = "SELECT status, COUNT(Status) as count FROM main_data GROUP BY status";
$result = mysql_query($query) or die(mysql_error());
while($record = mysql_fetch_assoc($result))
{
    $counts[$record['status']] = $record['count'];
}

$output  = "<table>\n";
$output .= "  <tr><th colspan=\"4\">CURRENT STATUS REPORT</th></tr>\n";
$record = 0;
foreach ($counts as $status => $value)
{
  $record++;
  if ($record%2==1)
  {
    $output .= "  <tr>\n";
  }
  $output .= "    <td>{$status}</td>\n";
  $output .= "    <td>{$value}</td>\n";
  if ($record%2==0)
  {
    $output .= "  </tr>\n";
  }
}
$output  = "</table>\n";

echo $output;

[Psycho]

Woops!

 

Change this line at the end

$output  = "</table>\n";

 

To this

$output  .= "</table>\n";

[honkmaster]

Guru, that's great worked at treat, more importantly I can see how you achieved the goal which is going to help me learn, Thank you.

 

One thing I forgot to say, the STATUS column in the man_data table is populated from a html dropdown list (see code example) if a status is not selected then "Please Select" which is the instruction shows in the database. (fig 3)

<select name="status" id="status">
              <option selected="selected">Please Select Status</option>
              <option value="In Progress">In Progress</option>
              <option value="Order Received">Order Received</option>
              <option value="Waiting for Artwork">Waiting for Artwork</option>
              <option value="Waiting for Approval">Waiting for Approval</option>
              <option value="Complete">Complete</option>
              <option value="On Hold">On Hold</option>
            </select>

 

 

Is there away of stopping this? Cheers Chris

 

 

Woops!

 

Change this line at the end

$output  = "</table>\n";

 

To this

$output  .= "</table>\n";

 

[attachment deleted by admin]

[Psycho]

Is there away of stopping this? Cheers Chris

 

Yes, but it depends what you are trying to achieve. If you are wanting to allow the user to not select a value, then set that option to an empty value. However, if you want to force the user to select one of the values, then still set that option to an empty value and then do a validation on the server to ensure the value of that field !-''

[honkmaster]

Guru Thanks for help, Cheers Chris

 

Is there away of stopping this? Cheers Chris

 

Yes, but it depends what you are trying to achieve. If you are wanting to allow the user to not select a value, then set that option to an empty value. However, if you want to force the user to select one of the values, then still set that option to an empty value and then do a validation on the server to ensure the value of that field !-''