PhP/MySQL: how to dynamically change my (large and always changing) database

Question

Scenario

I have a MySQL database with 10.000 rows. Setup of the database:

ID   UniqueKey   Name     Url           Score   ItemValue
1    5Zvr3       Google   google.com    13      X
2    46cfG       Radio    radio.com     -20     X
3    2fg64       Yahoo    yahoo.com     5       X
.... etc etc etc

As you can see, each item has a score. The score is constantly changing. Google may have a score of 13 now, but tomorrow it may be 80, or -50.

What I want:

I want to create a system that creates a hierarchy in my current database, based on the score of the items. Right now I'm thinking about percentile ranks, meaning that the highest scoring items will be close to 100%, and the lowest scoring items will be close to 0%. For this I created some code that will try to achieve what is shown here: http://www.psychstat.missouristate.edu/introbook/sbk14m.htm

This is my code:

$sql = "SELECT * FROM database order by Score";
$result = $conn->query($sql);
$count = 0;
while ($row = $result->fetch_assoc()) {
    $woow = $row['Score'];
    $sql = "SELECT * FROM database WHERE Score = $woow";
    $resultnew = $conn->query($sql);
    $somanythesame = $resultnew->num_rows;

    $itemPercentile = ( ($count/$result->num_rows + 0.5*$somanythesame/$result->num_rows) * 100 );

    $rowID = $row['ID'];
    $sql2 = "UPDATE database SET itemValue = $itemPercentile WHERE ID = $rowID";
    $conn->query($sql2);
    $count++;
}

This works, but for one problem it does not: There are many items in my database, many with the same score. To illustrate my problem, here is a very simple 10-row database with only the Scores:

Scores

-10
0
0
0
10
20
20
30
40
50

The problem with my code is that it doesn't give the same percentile for the items with the same Score, because it takes in account all previous rows for the calculation, including the ones with the same Score.

So, for the 2nd, 3rd and 4th item with a Score of 0, it should be like this: (1/10 + 0.5*1/10) * 100. Problem is, that for the 3rd item it will do (2/10 + 0.5*1/10) * 100 and the 4th item it will do (3/10 + 0.5*1/10) * 100.

Then, for the 5th item with a score of 10, it should do (4/10 + 0.5*1/10) * 100. This is going well; only not for the items with te same score.

---

I'm not sure if I explained this well, I find it hard to put my problem in the right words. If you have any questions, let me know! Thank you for your time :)

Answer 1

You need to maintain an "identical count" ($icount) variable that tracks the number of items with an identical score and a "current score" ($score) that tracks the current score.

$icount = 0;
$score = null;

Increment $icount instead of $count when $woow == $score (identical value check). Otherwise, add it to your $count and increment, and then reset the $icount value to 0.

if ($woow == $score) {
    $icount++;
} else {
    $count += $icount + 1;
    $icount = 0;
}

Finally, set your $score value to the latest $woow for testing in the next iteration of the loop:

$score = $woow;

This will allow items with the same Score to have the same $count value, while incrementing an additional $icount times when a new $score is found.

Your final code will look like this:

$sql = "SELECT * FROM database order by Score";
$result = $conn->query($sql);
$count = 0;
$icount = 0;
$score = null;
while ($row = $result->fetch_assoc()) {
    $woow = $row['Score'];
    $sql = "SELECT * FROM database WHERE Score = $woow";
    $resultnew = $conn->query($sql);
    $somanythesame = $resultnew->num_rows;

    $itemPercentile = ( ($count/$result->num_rows + 0.5*$somanythesame/$result->num_rows) * 100 );

    $rowID = $row['ID'];
    $sql2 = "UPDATE database SET itemValue = $itemPercentile WHERE ID = $rowID";
    $conn->query($sql2);
    if ($woow == $score) {
        $icount++;
    } else {
        $count += $icount + 1;
        $icount = 0;
    }
    $score = $woow;
}

Answer 2

You can change $sql query:

 $sql = "SELECT *,count(*) FROM database group by Score order by Score";

In this case, you fetch score with counts and no more select needed in the while loop.

Even you can select Percentile in MySQL query:

 Select t2.* , @fb as N , ((t2.fb1 + 0.5 * t2.fw)/@fb*100) as percentile from (
      Select t1.* , (@fb := @fb + t1.fw) as fb1 from (
           Select score,count(*) as fw From tablename group by score order by score ASC
           ) as t1
      ) as t2

I think this query returns most of columns which you may needs to check results.