How to count max occurence of rating

Question

Sophia_1 0 Junior Poster in Training

10 Years Ago

Hi everyone...i have this table ie Kra,Year,Max(Rating1),Max(Rating2),Tot_Rating...the task is to display the max occurence of rating.There are 2 types of rating ie "good" or "problematic"..if Max(Rating1)="good" and Max(Rating2)="good", Tot_Rating="good".Below is the coding but it didn't display as what suppose to...please advise...thanks a lot.

<!DOCTYPE html>
<html>
<body>

<?php
$connection = mysql_connect("localhost","pqa","") or die("Database connection failed!<br>");
$result=mysql_select_db("pqad") or die("Database could not be selected!");
echo(max (array("Max(Rating1)","Max(Rating2)" . "<br>")));

?>
</body>
</html>

php

Edited 10 Years Ago by diafol because: you forgot to use code tags

4 Contributors
14 Replies
337 Views
1 Week Discussion Span
Latest Post 10 Years Ago Latest Post by diafol

pzuurveen 90 Posting Whiz in Training

10 Years Ago

You don't do a query
checkout www.tizag.com,
www.w3schools.com

Edited 10 Years Ago by pzuurveen

diafol

10 Years Ago

Aren't you looking for the "max of count". Max() will give the highest value in a column (field) - not the "mode" value. A simple mode on a single field:

SELECT COUNT(field) AS mode FROM table GROUP BY field ORDER BY mode DESC LIMIT 1;

Reference: http://lists.mysql.com/mysql/155540

Edited 10 Years Ago by diafol

diafol

10 Years Ago

Please repost with correct code formatting. Place your code into the code editor (after pressing the </> Code button).

diafol

10 Years Ago

WHat are you trying to colour? You're just printing out the hex value of the last row's 'mode' to the screen below the table.

$class = ($row['mode'] == 'good') ? 'green' : 'red';
echo "<td class='$class'>" . $row['mode'] . "</td>";

For this you need:

.green{
    color: #008000; 
}

.red{
    color: #FF0000; 
}

In your css or style tags

Sophia_1 commented: Hi...am trying to color the Status column/field...pls advise...thanks +0

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Hi...am trying to color the Status column/field...pls advise...thanks

raicabogdan 0 Newbie Poster · Answer 1 · 2014-10-03T15:09:59+00:00

Indeed, you forgot to make your mysql_query there. The mysql query will get you the exact value you need.

also keep in mind, we all here recommend you at least switch to mysqli_* functions!

Sophia_1 0 Junior Poster in Training · Answer 2 · 2014-10-06T04:14:43+00:00

Hi everyone...thanks a lot for your help...really appreciate it...now i have to display different color based on the Tot_Rating,so if the Tot_Rating="Good" the Status field is "Green" and if Tot_Rating="Problematic", the Status field is "Red".Below is the coding but it doesn't display as supposed to.Please advise...Thanks a lot

<?php
$con=mysqli_connect("localhost","pqa","","pq");
// Check connection

if (mysqli_connect_errno()) {

echo "Failed to connect to MySQL: " . mysqli_connect_error();

$result = mysqli_query($con,"SELECT * FROM total_rating");
echo "<table border='1'>
<tr>
<th>KRA(Key Result Areas)</th>
<th>Rating</th>
<th>Status</th>

</tr>";

while($row = mysqli_fetch_array($result)) {

echo "<tr>";
echo "<td>" . $row['Kra'] . "</td>";
echo "<td>" . $row['mode'] . "</td>";
echo "</tr>";

echo "</table>";

switch ($row['mode']) { 



case "good"; 
    echo "#008000"; 
    break; 
default;     
    echo "#FF0000"; 
    break;     


}

mysqli_close($con);
?>

Sophia_1 0 Junior Poster in Training · Answer 3 · 2014-10-07T02:00:41+00:00

Hi everyone...thanks a lot for your help...really appreciate it...now i have to display different color based on the Tot_Rating,so if the Tot_Rating="Good" the Status field is "Green" and if Tot_Rating="Problematic", the Status field is "Red".Below is the coding but it doesn't display as supposed to. Please advise...Thanks a lot

<?php
$con=mysqli_connect("localhost","pqa","","pq");
// Check connection

if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();

}
$result = mysqli_query($con,"SELECT * FROM total_rating");
echo "<table border='1'>
<tr>
<th>KRA(Key Result Areas)</th>
<th>Rating</th>
<th>Status</th>

</tr>";

while($row = mysqli_fetch_array($result)) {
echo "<tr>";
echo "<td>" . $row['Kra'] . "</td>";
echo "<td>" . $row['mode'] . "</td>";
echo "</tr>";

}
echo "</table>";

switch ($row['mode']) { 
case "good"; 
    echo "#008000"; 
    break; 
default;     
    echo "#FF0000"; 
    break;     
} 
mysqli_close($con);
?>

Sophia_1 0 Junior Poster in Training · Answer 4 · 2014-10-08T07:07:33+00:00

Hi everyone...sorry but i still can't get the Tot_Rating for the above..Below is the mysql select statement but there is error...The mode seems to take the Max(Rating2) value, not the most occurred value...Please advise...Thanks you.

SELECT COUNT(Max(Rating1),Max(Rating2)) as mode FROM progress GROUP BY (Max(Rating1),Max(Rating2)) ORDER BY mode DESC LIMIT 1;

Sophia_1 0 Junior Poster in Training · Answer 5 · 2014-10-11T05:09:55+00:00

Hi...am trying to color the Status column/field...pls advise...thanks...

diafol · Answer 6 · 2014-10-11T11:14:57+00:00

WHy are you doing this:

COUNT(Max(Rating1),Max(Rating2))

ANd:

GROUP BY (Max(Rating1),Max(Rating2))

That doesn't make any sense to me.
Why have you got two ratings fields?
What type of data is stored in both?
Are they the same?

Please give an example of output of 5 records to give us an idea of what we're looking at.

Sophia_1 0 Junior Poster in Training · Answer 7 · 2014-10-13T00:59:48+00:00

Hi...am trying to output the mode or Tot_Rating for the Max(Rating1),Max(Rating2), where Max(Rating1) is the maximum occurence from many Kra(Key result area) for Quarter 1 and Max(Rating2) is the maximum occurence from many Kra(Key result area) for Quarter 2. These Kra has many progress. Pls advise....Thank you...

Output example:

Kra Tot_Rating Status
Tutoring Good Green color column
Research Problematic Red color column
Staff Good Green color column
Cost Problematic Red color column
Admin Problematic Red color column

diafol · Answer 8 · 2014-10-13T08:47:41+00:00

I meant the records in the original database tables - I am not convinced that you've got this right. Show any linked db tables too.

Sophia_1 0 Junior Poster in Training · Answer 9 · 2014-10-13T09:39:58+00:00

The Kra has many progress. Below is example of the table records:

a.Kra table
Kraid Kra
1. Tutoring
2. Research
3. Staff
4. Cost
5. Admin

b.Progress table

Progressid Quarter1 Rating1      Quarter2 Rating2      Kraid

1 1 Good 2 Good 1
2 1 Problematic 2 Problematic 1
3 1 Good 2 Good 1

The Max(Rating1) is the max occurence for Rating1 and Max(Rating2) is the max occurence for Rating2. In addition would also want to find the Tot_Rating ie mode for Max(Rating1) and Max(Rating2).

Appreciate for your advise. Thanks a lot.

diafol · Answer 10 · 2014-10-13T09:57:56+00:00

Progress table could be:

progress_id [pk]
kraid [fk]
quarter [accept tinyint 1-4]
rating [tinyint 1-2 1= good,2=prob]