Foreach loop from a Query using PHP

Question

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.

You said you have the idea that you have to use foreach loop. That is all you need to do — WordsWorth
– WordsWorth, Commented Mar 27, 2014 at 18:21

Arian Faurtosh · Accepted Answer · 2014-03-27 20:22:24Z

1

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];

edited Mar 27, 2014 at 20:22

answered Mar 27, 2014 at 18:30

Arian Faurtosh

18.6k23 gold badges81 silver badges117 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Arian Faurtosh Over a year ago

@user3443701 I updated the answer above, I changed mysqli_fetch_array to mysqli_fetch_row... it should work now.

user3443701 Over a year ago

its giving me an error on the mysqli_fetch_row this is the message mysqli_fetch_row() expects exactly 1 parameter

Arian Faurtosh Over a year ago

@user3443701 I updated my answer again...try it again... I changed it to mysqli_fetch_assoc

user3443701 Over a year ago

now its returning all the same with a massive gap, check the page here. https://studentnet.kingston.ac.uk/k1003140/leaderboard.php @arian

user3443701 Over a year ago

Okay I changed the query and now I have everything good but the point total, for the users are coming up as the same total, where do you think is this error, is there a way to add the total of a user individual so it wont clash all together. you can see my link. and thanks @arian

|

Collectives™ on Stack Overflow

Foreach loop from a Query using PHP

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related