Counting if a specific variable exists in multiple columns
I would greatly welcome any of you brilliant coders who could help me with this. My coding experience in mysql / php is limited, but I am stubborn.
So far: This successful query below gives the number of employees who have "serious" in only one column "rsmed" for business named "zmon", now I need to count as "serious" from multiple columns for "zmon" for business:
$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'm stuck here: I need to count the number of "breaks" in multiple columns (rslat, rsmed, rscentral, rselbow) in a table named "forearm" for a business named zmon.
So the business columns contains the company name. The same business can have multiple lines, each corresponding to a different employee. The other columns (rslat, rsmed, rscentral, rselbow) contain any of 4 variables: minor, low, medium, high, and severe.
I hope you have enough information.
Thanks Paul
source to share
You can manipulate the query to use SUM(criteria)
or SUM(IF(condition, 1, 0))
to count each column separately.
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 final total of the individual columns, you just need to add them.
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 harsh 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';
}
source to share
If you count how many "breaks" are in different columns (rslat, rsmed, rscentral, rselbow), you can try changing your query 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 write your loop like this:
while($row = mysql_fetch_array($result))
{
echo "There are {$row['employee_count']} employees severe in {$row['rtype']}.";
}
source to share
Try the following:
<?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);
?>
source to share