CODEWITHSUNDEEP
phpmysql
user3443701
user3443701

Reputation: 41

Foreach loop from a Query using PHP

I have a query on my php which prints out a result of one row only, I need to print out all the rows for each and every user.

Here is the php:

<?php
    // see if the form has been completed
    include_once("php_includes/check_login_status.php");
    //include_once("php_includes/db_conx.php");
    // Initialize any variables that the page might echo
    $username = "";
    $weight = "";
    $weighthist = "";
    $id = "";       

    if(isset($_GET["u"])){
        $username = preg_replace('#[^a-z0-9]#i', '', $_GET['u']);
    } 

    $sql = "SELECT users.*, weighthistory.* FROM users JOIN weighthistory USING(id)";

    $user_query = mysqli_query($db_conx, $sql);


    // check if the user exists in the database
    while ($row = mysqli_fetch_array($user_query, MYSQLI_ASSOC)) {
        $id = $row ["id"];
        $username = $row ["username"];
        $weight = $row["weight"];
        $weighthist = $row["weighthist"];
        $point_hist = $row["point_hist"];

        }

        // this is to calculate points score
        $calweight = $weight - $weighthist;     
        $points = $calweight * 10;



        $res = mysqli_query($db_conx,'SELECT sum(point_hist) FROM points_history');
        if (FALSE === $res) die("Select sum failed: ".mysqli_error);
        $row = mysqli_fetch_row($res);
        $sum = $row[0];




?>

Here is the HTML:

<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>Profile Update: <?php echo $u; ?></title>
    <link rel="icon" href="favicon.ico" type="image/x-icon">
    <link rel="stylesheet" type="text/css" href="style.css">
    <script src="js/main.js"></script>
    <script src="js/javascript.js"></script>
    <script src="js/ajax.js"></script>
    <style type="text/css">
        #updateform{
        margin-top:24px;    
        }
        #updateform > div {
            margin-top: 12px;   
        }
        #updateform > input {
            width: 200px;
            padding: 3px;
            background: #F3F9DD;
        }       
    </style>

</head>
<body>
    <p>&nbsp;</p>
    <?php include_once("template_pageTop.php"); ?>
    <div id="pageMiddle">       
    <div id="usernamecss"> Username: <?php echo $username; ?></div>

    <table width="100%" border="0">
      <tr>
        <td>Name</td>
        <td>Weight</td>
        <td>Rank</td>
        <td>Points</td>
      </tr>
      <tr>
        <td><?php echo $username ?></td>
        <td><?php echo $weight?></td>
        <td><?php echo $rank?></td>
        <td><?php echo $sum?></td>
      </tr>
      </table>
    <p>&nbsp;</p>
    <strong></strong>
    <a href="user.php<?php echo "?u=",$username;?>">Go to Profile</a>
    </form>
    </div>
    <?php include_once("template_pageBottom.php"); ?>
</body>
</html>

I am new to this so how can I print all rows for all user ID, I get the idea I have to use a foreach loop.

Upvotes: 1

Views: 214

Answers (1)

Arian Faurtosh
Arian Faurtosh

Reputation: 18491

This is how you could do it...

PHP file

<?php
    // see if the form has been completed
    include_once("php_includes/check_login_status.php");
    //include_once("php_includes/db_conx.php");
    // Initialize any variables that the page might echo
    $username = "";
    $weight = "";
    $weighthist = "";
    $id = "";       

    if(isset($_GET["u"])){
        $username = preg_replace('#[^a-z0-9]#i', '', $_GET['u']);
    } 

    $sql = "SELECT users.*, weighthistory.* FROM users JOIN weighthistory USING(id)";

    $user_query = mysqli_query($db_conx, $sql);


    // check if the user exists in the database
    while ($row = mysqli_fetch_assoc($user_query)) {
        $id = $row ["id"];
        $username = $row ["username"];
        $weight = $row["weight"];
        $weighthist = $row["weighthist"];
        $point_hist = $row["point_hist"];

        // this is to calculate points score
        $calweight = $weight - $weighthist;     
        $points = $calweight * 10;

        $res = mysqli_query($db_conx,'SELECT sum(point_hist) FROM points_history');
        if (FALSE === $res) die("Select sum failed: ".mysqli_error);
        $row = mysqli_fetch_row($res);
        $sum = $row[0];

        ?>
        <div id="pageMiddle">       
            <div id="usernamecss"> Username: <?php echo $username; ?></div>

            <table width="100%" border="0">
                <tr>
                    <td>Name</td>
                    <td>Weight</td>
                    <td>Rank</td>
                    <td>Points</td>
                </tr>
                <tr>
                    <td><?php echo $username ?></td>
                    <td><?php echo $weight?></td>
                    <td><?php echo $rank?></td>
                    <td><?php echo $sum?></td>
                </tr>
            </table>
            <p>&nbsp;</p>
            <strong></strong>
            <a href="user.php<?php echo "?u=",$username;?>">Go to Profile</a>
        </form>
    </div>
        <?php
    }
?>

HTML file

<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>Profile Update: <?php echo $u; ?></title>
    <link rel="icon" href="favicon.ico" type="image/x-icon">
    <link rel="stylesheet" type="text/css" href="style.css">
    <script src="js/main.js"></script>
    <script src="js/javascript.js"></script>
    <script src="js/ajax.js"></script>
    <style type="text/css">
        #updateform{
        margin-top:24px;    
        }
        #updateform > div {
            margin-top: 12px;   
        }
        #updateform > input {
            width: 200px;
            padding: 3px;
            background: #F3F9DD;
        }       
    </style>

</head>
<body>
    <p>&nbsp;</p>
    <?php include_once("template_pageTop.php"); ?>
    <?php include_once("template_pageBottom.php"); ?>
</body>
</html>

Edit:

If username is a column in your points_history table... then you can change this

$res = mysqli_query($db_conx,'SELECT sum(point_hist) FROM points_history');
if (FALSE === $res) die("Select sum failed: ".mysqli_error);
$row = mysqli_fetch_row($res);
$sum = $row[0];

to this

$query = "SELECT sum(point_hist) FROM points_history WHERE username = $username";
$res = mysqli_query($db_conx, $query);
$row = mysqli_fetch_row($res);
$sum = $row[0];

Upvotes: 1

Related Questions