Print PHP table from database

user_9758005 0
Viewed 253

I want to print/echo a 'users' table from my database 'xxx' in order of who has the biggest amount (number/int) of money but I need the sum between two columns from that table "money" + "bank".

So we have a database name: 'xxx' Table name: 'users' Columns: 'money' and 'bank'

I want to make a very simple HTML table with the ranking of the users with their total money. Also, I want to give positions to the table like 1,2,3,4.

Example here: enter image description here

What I've done till now is this:


<?php 

$servername = "svname.com";
$username = "";
$password = "";
$dbname = "xxx";

$conn = mysqli_connect($servername, $username, $password, $dbname) OR DIE ('Unable to connect to database! Please try again later.');

<?php 
    include 'db.php';
?>

<!DOCTYPE html>
<html>
    <head>
        <link rel="stylesheet" type="text/css" href="style.css">
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.0/jquery.min.js"></script>
        <script>
            $(document).ready(function() {
                var rankingCount = 10;
                $("button").click(function () {
                    rankingCount = rankingCount + 10;
                    $("#ranking").load("load-users.php", {
                        rankingNewCount: rankingCount
                    });
                });
            });
        </script>
    </head>

    <body>
        <div id="ranking">
            <?php
                $sql = "SELECT * FROM users LIMIT 10";
                $result = mysqli_query($conn, $sql);
                if (mysqli_num_rows($result) > 0) {
                    while ($row = mysqli_fetch_assoc($result)) {
                        echo "<p>" . "<span>Name</span>" . $row['firstname'] . "<span>Money</span>" . $row['money'] . "<span>Bank</span>" . $row['bank'] . "</p>";
                    }
                } else {
                    echo "There are no users!";
                }
            ?>
            
            <table width="100%" border="0" cellspacing="0" cellpadding="0">
                <tbody>
                <tr>
                    <th scope="col">No</th>
                    <th scope="col">Name</th>
                    <th scope="col">Money</th>
                    <th scope="col">Bank</th>
                    <th scope="col">Total</th>
                </tr>
                <tr>
                    <th scope="row">1</th>
                    <td>&nbsp;</td>
                    <td>&nbsp;</td>
                    <td>&nbsp;</td>
                    <td>&nbsp;</td>
                </tr>
                <tr>
                    <th scope="row">2</th>
                    <td>&nbsp;</td>
                    <td>&nbsp;</td>
                    <td>&nbsp;</td>
                    <td>&nbsp;</td>
                </tr>
                <tr>
                    <th scope="row">3</th>
                    <td>&nbsp;</td>
                    <td>&nbsp;</td>
                    <td>&nbsp;</td>
                    <td>&nbsp;</td>
                </tr>
                <tr>
                    <th scope="row">4</th>
                    <td>&nbsp;</td>
                    <td>&nbsp;</td>
                    <td>&nbsp;</td>
                    <td>&nbsp;</td>
                </tr>
                </tbody>
            </table>
        </div>

        <button>More 10 results here</button>
    </body>

</html>

<?php
    include 'db.php';

    $rankingNewCount = $_POST['rankingNewCount'];

    $sql = "SELECT * FROM users LIMIT $rankingNewCount";
    $result = mysqli_query($conn, $sql);
    if (mysqli_num_rows($result) > 0) {
        while ($row = mysqli_fetch_assoc($result)) {
            echo "<p>" . "<span>Name</span>" . $row['firstname'] . "<span>Money</span>" . $row['money'] . "<span>Bank</span>" . $row['bank'] . "</p>";
        }
    } else {
        echo "There are no users!";
    }
?>
mysql php jquery javascript
2 Answers

You could change the query to calculate the total: SELECT *, (money + bank) AS total FROM users LIMIT 10

About the index, you could add it in a while-loop when you making the table.

Example:

<?php 
    include 'db.php';
?>

<!DOCTYPE html>
<html>
    <head>
        <link rel="stylesheet" type="text/css" href="style.css">
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.0/jquery.min.js"></script>
        <script>
            $(document).ready(function() {
                var rankingCount = 10;
                $("button").click(function () {
                    rankingCount = rankingCount + 10;
                    $("#ranking").load("load-users.php", {
                        rankingNewCount: rankingCount
                    });
                });
            });
        </script>
    </head>

    <body>
        <div id="ranking">
            <table width="100%" border="0" cellspacing="0" cellpadding="0">
                <tbody>
                <tr>
                    <th scope="col">No</th>
                    <th scope="col">Name</th>
                    <th scope="col">Money</th>
                    <th scope="col">Bank</th>
                    <th scope="col">Total</th>
                </tr>
                <?php
                    $sql = "SELECT *, (money + bank) AS total FROM users LIMIT 10";
                    $result = mysqli_query($conn, $sql);
                    if (mysqli_num_rows($result) > 0) {
                        $index = 1;
                        while ($row = mysqli_fetch_assoc($result)) {
                            echo '<tr>
                                <td scope="row">' . $index . '</td>
                                <td>' . $row['firstname'] . '</td>
                                <td>' . $row['money'] . '</td>
                                <td>' . $row['bank'] . '</td>
                                <td>' . $row['total'] . '</td>
                            </tr>';
                            $index++;
                        }
                    } else {
                        echo '<tr>
                            <td colspan="4">There are no users!</td>
                        </tr>';
                        echo ;
                    }
                ?>
                </tbody>
            </table>
        </div>

        <button>More 10 results here</button>
    </body>

</html>```
user_70202550

You need to append results everytime, so the JS code should probably be:

$(document).ready(function() {
     var rankingCount = 10;
     $("button").click(function () {
         rankingCount = rankingCount + 10;
         $.ajax({url:'load-users.php',
                 data: {rankingCount: rankingCount},
                 complete: function(resp){
                     $("#ranking").html( $('#ranking').html() + resp );

         }});
  });

Php should be as simple as:

<?php
$offset = $_POST['rankingCount'] || 0;
$sql = "SELECT name as Name, money as Money, bank as Bank, (money+bank) as total FROM users LIMIT 10, ".$offset;
$result = mysqli_query($conn, $sql);
$buff = '<table width=""><thead><col>'. implode('</col><col>', array_keys($result[0]) ).'</col></thead><tr>';

if (mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)){
        $buff .= implode('</tr><tr>', array_map( function($i){ return '<span>'.$i.'</span>'; }, $row ) );
    }
} else {
    $buff .= '<td colspan="4">There are no users!</td>';
}
$buff .= '</tr></table>';
echo $buff;

?>

user_145300970
Related