CODEWITHSUNDEEP
phpmysqlcountmultiple-columns
Paul
Paul

Reputation: 41

Counting the occurence of a specific variable in multiple columns

I would greatly apreciate any of you brilliant coders out there could help me with this. My coding expertise in mysql/php is limited but I am stubborn.

So far: This successful query below gives the number of employees that have 'severe' in only one column 'rsmed' for the business named 'zmon', I now need to count 'severe' from multiple columns for the business 'zmon':

$host="localhost";
$username="user"; 
$password="pass";
$db_name="dbase";
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

$query = "SELECT COUNT(*) FROM forearm WHERE business='zmon' AND rsmed = 'severe' "; 

$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result))
{
echo "There are ". $row['COUNT(*)'] ." employees severe in rsmed.";
}

I am stuck here: I need to count the number of 'severes' in multiple columns (rslat, rsmed, rscentral, rselbow) in the table named 'forearm' for the business named zmon.

So, column business contains a name of a business. The same business can have multiple rows each corresponding to different employees of theirs. The other columns (rslat, rsmed, rscentral, rselbow) contain any of 4 variables: Not significant, low, medium, high and severe.

I hope that is enough information for you.

Thanks, Paul

Upvotes: 3

Views: 353

Answers (3)

RïshïKêsh Kümar
RïshïKêsh Kümar

Reputation: 5092

Try this:

<?php

$host="localhost";
$username="user"; 
$password="pass";
$db_name="dbase";

$conn = mysql_connect("$host", "$username", "$password")or die("cannot connect"); 

mysql_select_db("$db_name")or die("cannot select DB");

$query = "SELECT COUNT(*) FROM forearm WHERE business='zmon' AND rsmed = 'severe' "; 

$result = mysqli_query($conn,$query);

if($result){

  // Return the number of rows in result set

  $rowcount=mysqli_num_rows($result);

  printf("Result set has %d rows.\n",$rowcount);
  // Free result set

  mysqli_free_result($result);
  }

mysqli_close($conn);


?>

Upvotes: 1

Will B.
Will B.

Reputation: 18416

You can manipulate the query to use SUM(criteria) or SUM(IF(condition, 1, 0)) to count each column individually.

SELECT 
    SUM(rslat = 'severe') as rslat_count,
    SUM(rselbow = 'severe') as rselbow_count,
    SUM(rsmed = 'severe') as rsmed_count,
    SUM(rscentral = 'severe') as rscentral_count
FROM forearm
WHERE business='zmon'

Data:

| id | business |  rslat | rselbow |  rsmed | rscentral |
|----|----------|--------|---------|--------|-----------|
|  1 |     zmon | severe |  severe | severe |      good |
|  2 |     zmon | severe |  severe |   good |      good |
|  3 |     zmon |   good |  severe |   good |      good |
|  4 |     zmon | severe |  severe |   good |      good |

Result: http://sqlfiddle.com/#!9/093bd/2

| rslat_count | rselbow_count | rsmed_count | rscentral_count |
|-------------|---------------|-------------|-----------------|
|           3 |             4 |           1 |               0 |

Then you can display the results in php using

$sentence = 'There are %d employees severe in %s';
while ($row = mysql_fetch_assoc($result)) {
    printf($sentence, $row['rslat_count'], 'rslat');
    printf($sentence, $row['rselbow_count'], 'rselbow');
    printf($sentence, $row['rsmed_count'], 'rsmed');
    printf($sentence, $row['rscentral_count'], 'rscentral');
}

UPDATED

To get the derived total of the individual columns, you just need to add them up.

SELECT 
   SUM(counts.rslat_count + counts.rselbow_count + counts.rsmed_count + counts.rscentral_count) as severe_total,
   counts.rslat_count,
   counts.rselbow_count,
   counts.rsmed_count,
   counts.rscentral_count
FROM (
   SELECT 
      SUM(rslat = 'severe') as rslat_count,
      SUM(rselbow = 'severe') as rselbow_count,
      SUM(rsmed = 'severe') as rsmed_count,
      SUM(rscentral = 'severe') as rscentral_count
   FROM forearm
   WHERE business='zmon'
) AS counts

Result http://sqlfiddle.com/#!9/093bd/10

| severe_total | rslat_count | rselbow_count | rsmed_count | rscentral_count |
|--------------|-------------|---------------|-------------|-----------------|
|            8 |           3 |             4 |           1 |               0 |

Then display the severe total

$sentence = 'There are %d employees severe in %s';
while ($row = mysql_fetch_assoc($result)) {
    printf($sentence, $row['rslat_count'], 'rslat');
    printf($sentence, $row['rselbow_count'], 'rselbow');
    printf($sentence, $row['rsmed_count'], 'rsmed');
    printf($sentence, $row['rscentral_count'], 'rscentral');
    echo 'business in ' . $row['severe_total'] . ' severe conditions';
}

Upvotes: 2

aljo f
aljo f

Reputation: 2500

If you are counting how many 'severes' are in the different columns (rslat, rsmed, rscentral, rselbow) you can try modifying your query to something like this:

SELECT COUNT(*) AS employee_count, "rsmed" AS rtype 
FROM forearm WHERE business='zmon' AND rsmed = 'severe'
UNION
SELECT COUNT(*) AS employee_count, "rslat" AS rtype 
FROM forearm WHERE business='zmon' AND rslat = 'severe'
UNION
SELECT COUNT(*) AS employee_count, "rscentral" AS rtype 
FROM forearm WHERE business='zmon' AND rscentral = 'severe'
UNION
SELECT COUNT(*) AS employee_count, "rselbow" AS rtype 
FROM forearm WHERE business='zmon' AND rselbow = 'severe'

Then you can now write your loop like this:

while($row = mysql_fetch_array($result))
{
    echo "There are {$row['employee_count']} employees severe in {$row['rtype']}.";
}

Upvotes: 2

Related Questions