Rank

[gw32]

I have the following script which works well as it is, but I need to add a column that ranks each player and that column is missing from the output.

 

Script:

 

if (!mysql_connect($db_host, $db_user, $db_pwd))
    die("Can't connect to database");

if (!mysql_select_db($database))
    die("Can't select database");

// Truncate command
$truncate = mysql_query("TRUNCATE TABLE $table");
if (!$truncate) {
    die("Query 1 to show fields from table failed");
}

$sql = mysql_query("INSERT INTO $table(`Entry`, `Name`, `Points`, `Winnings`, `HH`, `Fty`, `Attend`)
SELECT a.playersid as Entry, b.name as Name, sum(a.points) as Points, sum(a.payout) as Winnings, sum(a.high_hand) as HH, sum(a.fifty_fifty) as Fty, sum(attend) as Attend FROM data a, players b where a.playersid = b.playersid group by Entry");

if (!$sql) {
    die("Query 2 to show fields from table failed");
}

// sending query

$result = mysql_query("SELECT Name, Points, Winnings, HH, Fty, Attend FROM $table order by Points desc");

if (!$result) {
    die("Query to show fields from table failed");
}
    
$fields_num = mysql_num_fields($result);

echo "<h3>This Weeks Report: {$table}</h3>";
echo "<table border='1'><tr>";
echo "<tr><th>Rank</th><th>Name</th><th>Points</th><th>Winnings</th><th>High Hand</th><th>Fifty Fifty</th><th>Wks Pld</th></tr>";

// printing table headers
for($i=0; $i<$fields_num; $i++)
{
    $field = mysql_fetch_field($result);

}
echo "</tr>\n";
// printing table rows
while($row = mysql_fetch_row($result))
{
    echo "<tr>";

    // $row is array... foreach( .. ) puts every element
    // of $row to $cell variable
    foreach($row as $cell)
        echo "<td>$cell</td>";

    echo "</tr>\n";
}
mysql_free_result($result);
?>
</body></html>

 

I'm lost and the info I get from searching is confusing. Hope someone can help.

 

Thanks

[Barand]

There is no need for the $table table. Just get the data from the SELECT query.

For the rank, just maintain a counter as you output the rows.

$sql = "SELECT 
            a.playersid as Entry
            , b.name as Name
            , sum(a.points) as Points
            , sum(a.payout) as Winnings
            , sum(a.high_hand) as HH
            , sum(a.fifty_fifty) as Fty
            , sum(attend) as Attend 
        FROM data a
        INNER JOIN players b ON a.playersid = b.playersid 
        GROUP BY Entry
        ORDER BY Points DESC";
$res = mysql_query($sql);

$row = mysql_fetch_assoc($res);
$heads = "<tr><th>Rank</th><th>" . join('</td><td>', array_keys($row)) . "</th></tr>\n";
$rank=1;
$tdata='';
do {
    $tdata .= "<tr><td>$rank</td><td>" . join('</td><td>', $row) . "</td></tr>\n";
    $rank++;
} while ($row = mysql_fetch_assoc($res));
?>

<table border='1'>
<?php echo $heads, $tdata;?>
</table>

And stop using mysql_ functions (See my signature)

[benanamen]

You are using deprecated code that will not work at all in the latest version of Php. You need to use PDO with prepared statements or Mysqli.