Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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;
share|improve this question
1  
Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use pdo or mysqli. –  hjpotter92 Mar 29 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 at 17:54

2 Answers

up vote 1 down vote accepted

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.
share|improve this answer
 
Corrected - the copy and paste didn't take the brackets for some reason –  user2217666 Mar 29 at 17:58
1  
The brackets aren't the only problem. –  MichaelRushton Mar 29 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 at 18:09
 
It worked but it seems to have broken some code elsewhere with regards to a table being displayed –  user2217666 Mar 29 at 18:11
 
@Jocelyn, he said he can't change the query (comment to OP). –  MichaelRushton Mar 29 at 18:13
up vote 5 down vote

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'];
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.