CODEWITHSUNDEEP
phpmysqlcount
user2217666
user2217666

Reputation: 15

PHP / MySQL Specific Column Count

I have searched around on forums however all answers don't seem to work for my I am guessing it's more user error.

What I am trying to do:

  1. Retrieve the data set from MySQL

  2. Count the total number of rows

  3. Work out specifically how many of them have the value "Y" in the metSLA column

  4. Work out specifically how many of them have the value "N" in the metSLA column

  5. Convert each of these metSLA values to a percentage

**The MySQL query works for sure and its stored in variable $result for reference.

* 

        //sla and total case count and percentages
        $sla_met_rows = 0;
        $sla_not_met_rows = 0;

        $total_cases = mysql_num_rows($result);

        while ($row = mysql_fetch_array($result)) 
        {
        if `metSLA` = "Y"
            {
            $sla_met_rows ++;
            } else if `metSLA` = "N"
                {
                $sla_not_met_num_rows ++;
                }
        }
        $met_percentage = 100 / $total_cases * $sla_met_rows;
        $not_met_percentage = 100 / $total_cases * $sla_not_met_num_rows;

Upvotes: 1

Views: 1068

Answers (2)

Michael
Michael

Reputation: 12802

Change

    if `metSLA` = "Y"
        {
        $sla_met_rows ++;
        } else if `metSLA` = "N"
            {
            $sla_not_met_num_rows ++;
            }

To:

    if ($row['metSLA'] == "Y")
    {
      $sla_met_rows ++;
    }

    else if ($row['metSLA'] == "N")
    {
      $sla_not_met_num_rows ++;
    }

What you have has three problems:

  1. You're missing the brackets around the conditions,
  2. You're assigning (=) rather than comparing (==), and
  3. You're running a shell command rather than getting the value from the database row.

Upvotes: 1

hjpotter92
hjpotter92

Reputation: 80639

You can use a single MySQL query to get the percentage result:

SELECT COUNT( CASE WHEN `metSLA` = "Y" THEN 1 ELSE NULL END ) AS `Yes`,
    COUNT( CASE WHEN `metSLA` = "N" THEN 1 ELSE NULL END ) AS `No`,
    COUNT(1) AS `total`
FROM `TableName`

In your PHP, it'll be referenced as:

$result = mysql_query( <<The query above is here>> );
$row = mysql_fetch_array( $result );
$met_precentage = $row['Yes'] * 100 / $row['total'];
$not_met_precentage = $row['No'] * 100 / $row['total'];

Upvotes: 5

Related Questions