PHP foreach loop array

Question

I have a script where it's displaying user info on a leader board. It's grabbing each user's display info through the 'registrations' table as shown in the top sql, however their back-end info (username, user id, ip address etc) is stored in the 'users' table. The registrations table has a column 'user_id', which I'm trying to link with the users table which can be seen after the foreach.

The goal is to have the username (stored in 'users') to be displayed for each user - which is shown at <td class='center'>" . $userdata['username'] . "</td>. However, the username isn't displaying.

At the bottom, I'm trying to insert a record to another table which also uses this same username value, however it inserts as an empty string. Please note the INSERT should only insert for the user that was clicked on.. can this even be achieved?

-Also, I am aware this PHP isn't the best and should be using prepared statements etc, however that is not the current goal at the moment.

<?php
$sql = mysqli_query($DB, "SELECT * FROM registrations LIMIT 5");

foreach ($sql as $row)
{
    $userdata = mysqli_fetch_assoc(mysqli_query($DB, "SELECT * FROM users WHERE id='" . $row['user_id'] . "'"));
    echo "
                    <tr class='gradeX'>
                        <td class='center'>" . $userdata['username'] . "</td>
                        <td class='center'>" . $row['balance'] . "</td>
                        <td class='center'>" . $row['wins'] . " - " . $row['losses'] . "</td>
            <td class='center'><form method='post' action=''><input type='submit' class='btn btn-success' name='invite' value='Request Game'/></form></td>
                        <td class='center'><a href='' class='btn btn-success'>WATCH STREAM</a></td>
                    </tr>";
}

if (isset($_POST['invite']))
{
    $query = "INSERT INTO matches (id,sender,receiver,time,console,rank_sender,rank_receiver,status) values ('0', '" . $_SESSION['username'] . "', '" . $userdata['username'] . "', '" . time() . "', '" . $row['type'] . "', '" . $GETD['rank'] . "', '$rank', 'pending')";
    mysqli_query($DB, $query);
}
?>

Thanks for any help :)

Why you didn't take directly all data from your first query? Then in your loop, you just have to display the data of a row — Eiji, Mar 12 '19 at 06:09
You need to do `mysqli_fetch_assoc` to loop for first query also. — Sougata Bose, Mar 12 '19 at 06:09
@Eiji, the data is from 2 different tables so I'm unsure how you would do this in one query, would using `INNER JOIN` work? — Noah Sinist3rSaint, Mar 12 '19 at 06:11
Yes of course. I don't know your table but it seems like the `INNER JOIN users ON users.id = registrations.user_id` be something close to your DB. — Eiji, Mar 12 '19 at 06:12
`foreach ($sql as $row)` doesn't make much sense, as that's the query-object, and not any fetched data. — Qirel, Mar 12 '19 at 06:14
@Qirel , would `while ($row = mysql_fetch_assoc($sql)) {` be a better option for this then? — Noah Sinist3rSaint, Mar 12 '19 at 06:19
`$sql = mysqli_query($DB, "SELECT * FROM registrations JOIN users on on users.id=registrations.user_id LIMIT 5"); $res = mysqli_fetch_assoc($sql); foreach($res as $row){ }` you should use `JOIN` in the query, then fetch the result and then loop it. — siddiq, Mar 12 '19 at 06:19
Agree with @quirel. You can replace foreach($sql as $row) with while($row = mysqli_fetch_assoc($sql)) — Hichem BOUSSETTA, Mar 12 '19 at 06:22
In your `INSERT` query, you are trying to use `$userdata` and `$row` which are outside the scope of the `foreach` as well since it's inserted after submit (`POST`) you need to pass those values at least with your form submission. You can use hidden form fields to do this. — Shakeel, Mar 12 '19 at 06:25

Qirel · Accepted Answer · 2019-03-12T06:36:03.887

Using a JOIN, you can execute a single query and fetch the data you need. I also converted this to using a prepared statement - which should be used over the standard mysqli::query() method, even without variables in the query.

Your INSERT query quite doesn't make sense (judging by the variables you had in your question), so you should have a look at the variables and set them as they should be (your code in the question would insert the username of the last fetched username, and that is not necessarily the value you expect it to be).

I also removed your id from the INSERT query, this should instead be an auto-increment in your matches table.

You need to assign the proper values to the lines with TODO in the appending comment of that line.

$stmt = $DB->prepare("SELECT u.username, r.balance, r.wins, r.losses
                      FROM registrations r 
                      JOIN users u 
                        ON u.id=r.user_id
                      LIMIT 5
                      ");
$stmt->bind_result($username, $balance, $wins, $losses);
$stmt->execute();

while ($stmt->fetch()) {
    ?>
    <tr class='gradeX'>
        <td class='center'><?php echo $username; ?></td>
        <td class='center'><?php echo $balance; ?></td>
        <td class='center'><?php echo $wins; ?> - <?php echo $losses; ?></td>
        <td class='center'>
            <form method='post' action=''>
                <input type='submit' class='btn btn-success' name='invite' value='Request Game'/>
            </form>
        </td>
        <td class='center'><a href='' class='btn btn-success'>WATCH STREAM</a></td>
    </tr>
    <?php 
}
$stmt->close();

if (isset($_POST['invite'])) {
    // Assign the variables here. 
    $username_sender = 'somevalue';  // TODO: Define this. 
    $time = time();
    $rank_sender = $GETD['rank'];
    $rank_receiver = $rank;          // TODO: Define this. 
    $console = $type;                // TODO: Define this. 
    $status = 'pending';

    $stmt = $DB->prepare("INSERT INTO matches (sender, receiver, time, console, rank_sender, rank_receiver, status)
                            VALUES (?, ?, ?, ?, ?, ?, ?)");
    $stmt->bind_param("sssssss", $_SESSION['username'], $username_sender, $time, $console, $rank_sender, $rank_receiver, $status);
    $stmt->execute();
    $stmt->close();
}

Amazing! Thankyou so much. I seem to get an error however, `Fatal error: Uncaught Error: Call to a member function bind_result() on bool in ~~~~~ Stack trace: #0 {main} thrown in ~~~~~~~`. Would you happen to know the cause for this? — Noah Sinist3rSaint, Mar 12 '19 at 07:02
The `prepare()` failed. Print out `$DB->error;` and `$stmt->error;` — Qirel, Mar 12 '19 at 07:36
`Trying to get property 'error' of non-object in ~~~~~ on line 82` — Noah Sinist3rSaint, Mar 12 '19 at 07:43
Did `$DB->error` give anything? What is the ID column named in the `users` table? — Qirel, Mar 12 '19 at 07:49
My goodness.. turn's out the id column in user's is user_id and not id like I thought, I'm an idiot. I got it working! Thanks so much for the help. — Noah Sinist3rSaint, Mar 12 '19 at 07:52
Glad to have helped! I recommend you take a moment to read up on how these prepared statements work, and incorporate it throughout all your queries. :-) — Qirel, Mar 12 '19 at 08:03

score 0 · Answer 2 · answered Mar 12 '19 at 06:51

I have done improvements to your code. I didn't make much change for your easy understanding. I am trying to give you some hints, on passing variables in a post request (like form submission).

Note: retrieved post values must be sanitized before use in the query. You may use prepared statements to do so after you have understood this answer.

    <?php
$sql = mysqli_query($DB, "
  SELECT * FROM registrations
  JOIN users on users.id=registrations.user_id
   WHERE users.id='" . $row['user_id'] . "' LIMIT 5");

$result = mysqli_fetch_assoc($sql);

foreach ($result as $row) {
    echo "
<tr class='gradeX'>
    <td class='center'>" . $row['username'] . "</td>
    <td class='center'>" . $row['balance'] . "</td>
    <td class='center'>" . $row['wins'] . " - " . $row['losses'] . "</td>
    <td class='center'>
        <form method='post' action=''>
        <input type='submit' class='btn btn-success' name='invite' value='Request Game'/>
        <input type='hidden' name='username' value='". $row['username'] . "' />
        <input type='hidden' name='type' value='". $row['type'] . "' />
        </form>
    </td>
    <td class='center'><a href='' class='btn btn-success'>WATCH STREAM</a></td>
</tr>";
}

if (isset($_POST['invite'])) {
    $query = "INSERT INTO matches (id,sender,receiver,time,console,rank_sender,rank_receiver,status) values ('0', '" . $_SESSION['username'] . "', '" . $_POST['username'] . "', '" . time() . "', '" . $_POST['type'] . "', '" . $GETD['rank'] . "', '$rank', 'pending')";
    mysqli_query($DB, $query);
}

PHP foreach loop array

2 Answers2