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

Question

Scenario

I have a MySQL database with 10,000 rows. Database setup:

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 now have a score of 13, 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 score elements. Right now I’m thinking about percentile ranks , which means that the highest points will be close to 100% and the lowest points will be close to 0%. To do this, I created code that will try to achieve what is shown here: http://www.psychstat.missouristate.edu/introbook/sbk14m.htm

:

$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++;
}

, : , . , 10- :

-10
0
0
0
10
20
20
30
40
50

, , , .

, , Score of 0 : (1/10 + 0.5*1/10) * 100. , 3- (2/10 + 0.5*1/10) * 100, 4- (3/10 + 0.5*1/10) * 100.

5- 10 (4/10 + 0.5*1/10) * 100. ; .

---

, , . - , ! :)

Answer 1

" " ($icount), " " ($score), .

$icount = 0;
$score = null;

$icount $count, $woow == $score ( ). $count , reset $icount 0.

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

, $score $woow :

$score = $woow;

$count, $icount , $score.

:

$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

$sql:

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

while.

Percentile MySQL:

 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

, , .