douyou2234
2013-12-09 17:38
浏览 51
已采纳

排名更新问题

I have this code, its gives the output of the Database, now I want to order it with respect to TeamPoints. The team with higher points gets the first position. How do I Implement it.

I have been trying to add a TeamRank field in the Database, which updates a team Rank by increment or decrement. But it's not working so I decided to just order them by points, but now I am facing issues in arranging them in increasing order.

<?php
    $con=mysqli_connect("", "", ", "");

    // Check connection:
    if (mysqli_connect_errno())
    {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

    mysqli_query($con,"CREATE VIEW Rankings AS
        SELECT TeamID, TeamName, TeamLeader, TeamEmail, P_1, P_2, P_3, P_4, P_5, S_1, S_2, TeamWins, TeamLoss, TeamPoints
        FROM team
        WHERE TeamID>0");

    $result = mysqli_query($con,"SELECT * FROM team");

    echo "<table border='1'>
    <tr>
        <th>TeamID</th>
        <th>TeamName</th>
        <th>TeamLeader</th>
        <th>TeamEmail</th>
        <th>Player #1</th>
        <th>Player #2</th>
        <th>Player #3</th>
        <th>Player #4</th>
        <th>Player #5</th>
        <th>Subsitute #1</th>
        <th>Subsitute #2</th>
        <th>Total Wins</th>
        <th>Total Losses</th>
        <th>Total Points</th>
    </tr>";

    while($row = mysqli_fetch_array($result))
    {
        echo "<tr>";
            echo "<td>" . $row['TeamID'] . "</td>";
            echo "<td>" . $row['TeamName'] . "</td>";
            echo "<td>" . $row['TeamLeader'] . "</td>";
            echo "<td>" . $row['TeamEmail'] . "</td>";
            echo "<td>" . $row['P_1'] . "</td>";
            echo "<td>" . $row['P_2'] . "</td>";
            echo "<td>" . $row['P_3'] . "</td>";
            echo "<td>" . $row['P_4'] . "</td>";
            echo "<td>" . $row['P_5'] . "</td>";
            echo "<td>" . $row['S_1'] . "</td>";
            echo "<td>" . $row['S_2'] . "</td>";
            echo "<td>" . $row['TeamWins'] . "</td>";
            echo "<td>" . $row['TeamLoss'] . "</td>";
            echo "<td>" . $row['TeamPoints'] . "</td>";
        echo "</tr>";
    }

    echo "</table>";
    mysqli_close($con);
?>
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • douluo3256 2013-12-10 09:31
    已采纳

    Adding a rank to the select and outputting it, along with limiting the output to a page (of 10 - easily changed):-

    <?php
        $con=mysqli_connect("", "", "", "");
    
        // Check connection:
        if (mysqli_connect_errno())
        {
            echo "Failed to connect to MySQL: " . mysqli_connect_error();
        }
    
        $PageStart = 10 * intval($_POST['page']);
    
        $result = mysqli_query($con,"SELECT TeamID,
                                    TeamName,
                                    TeamLeader,
                                    TeamEmail,
                                    P_1,
                                    P_2,
                                    P_3,
                                    P_4,
                                    P_5,
                                    S_1,
                                    S_2,
                                    TeamWins,
                                    TeamLoss,
                                    TeamPoints,
                                    TeamRnk
                                    FROM
                                    (
                                        SELECT TeamID,
                                                TeamName,
                                                TeamLeader,
                                                TeamEmail,
                                                P_1,
                                                P_2,
                                                P_3,
                                                P_4,
                                                P_5,
                                                S_1,
                                                S_2,
                                                TeamWins,
                                                TeamLoss,
                                                TeamPoints,
                                                @Rank := @Rank + 1 AS TeamRnk
                                        FROM SomeTable
                                        CROSS JOIN (SELECT @Rank:=0) Sub0
                                        ORDER BY TeamPoints DESC
                                    ) Sub1
                                    LIMIT $PageStart, 10");
    
        echo "<table border='1'>
        <tr>
            <th>TeamRnk</th>
            <th>TeamID</th>
            <th>TeamName</th>
            <th>TeamLeader</th>
            <th>TeamEmail</th>
            <th>Player #1</th>
            <th>Player #2</th>
            <th>Player #3</th>
            <th>Player #4</th>
            <th>Player #5</th>
            <th>Subsitute #1</th>
            <th>Subsitute #2</th>
            <th>Total Wins</th>
            <th>Total Losses</th>
            <th>Total Points</th>
        </tr>";
    
        while($row = mysqli_fetch_assoc($result))
        {
            echo "<tr>";
                echo "<td>" . $row['TeamRnk'] . "</td>";
                echo "<td>" . $row['TeamID'] . "</td>";
                echo "<td>" . $row['TeamName'] . "</td>";
                echo "<td>" . $row['TeamLeader'] . "</td>";
                echo "<td>" . $row['TeamEmail'] . "</td>";
                echo "<td>" . $row['P_1'] . "</td>";
                echo "<td>" . $row['P_2'] . "</td>";
                echo "<td>" . $row['P_3'] . "</td>";
                echo "<td>" . $row['P_4'] . "</td>";
                echo "<td>" . $row['P_5'] . "</td>";
                echo "<td>" . $row['S_1'] . "</td>";
                echo "<td>" . $row['S_2'] . "</td>";
                echo "<td>" . $row['TeamWins'] . "</td>";
                echo "<td>" . $row['TeamLoss'] . "</td>";
                echo "<td>" . $row['TeamPoints'] . "</td>";
            echo "</tr>";
        }
    
        echo "</table>";
        mysqli_close($con);
    ?>
    
    打赏 评论
  • dpd46554 2013-12-09 17:43

    Is this what your looking for?

    SELECT  TeamID,TeamName,TeamLeader,TeamEmail,P_1,P_2,P_3,P_4,P_5,S_1,S_2,TeamWins,TeamLoss,TeamPoints
    FROM team
    where TeamID>0
    ORDER BY TeamPoints DESC;
    
    打赏 评论
  • doutang1856 2013-12-09 17:47
        CREATE VIEW Rankings AS
        SELECT  TeamID
             , TeamName
             , TeamLeader
             , TeamEmail
             , P_1        -- none 
             , P_2        -- of
             , P_3        -- these
             , P_4        -- belong
             , P_5        -- in a 
             , S_1        -- teams
             , S_2        -- table
             , TeamWins   -- and even
             , TeamLoss   -- these are
             , TeamPoints -- questionable
          FROM team
         where TeamID > 0;
    
    打赏 评论

相关推荐 更多相似问题