PHP / MySQL Specific Column Count

Question

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:

Retrieve the data set from MySQL
Count the total number of rows
Work out specifically how many of them have the value "Y" in the metSLA column
Work out specifically how many of them have the value "N" in the metSLA column
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;

[Please, don't use `mysql_*` functions](http://stackoverflow.com/q/12859942/1190388) in new code. They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the red box? Learn about prepared statements instead, and use [tag:PDO] or [tag:MySQLi]. — hjpotter92, Mar 29 '13 at 17:53
For reference - the MySQL data pull contains multiple columns and I cannot change this query. I believe the issue is likely to be on the if and else if lines as that is where it is erroring. However I don't know how to reference the mySQL column. — user2217666, Mar 29 '13 at 17:54

score 5 · Answer 1 · answered Mar 29 '13 at 17:56

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'];

Michael · Accepted Answer · 2013-03-29T18:01:26.337

1

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:

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

edited Mar 29 '13 at 18:01

answered Mar 29 '13 at 17:56

Michael

11,912
6
49
64

Corrected - the copy and paste didn't take the brackets for some reason – user2217666 Mar 29 '13 at 17:58
1

The brackets aren't the only problem. – Michael Mar 29 '13 at 18:05
While your code should work, this is not very efficient. This kind of calculations is better done by MySQL. – Jocelyn Mar 29 '13 at 18:09
It worked but it seems to have broken some code elsewhere with regards to a table being displayed – user2217666 Mar 29 '13 at 18:11
@Jocelyn, he said he can't change the query (comment to OP). – Michael Mar 29 '13 at 18:13

PHP / MySQL Specific Column Count

What I am trying to do:

2 Answers2