CODEWITHSUNDEEP
phphtmlmysqlsql
Elias Poulsen
Elias Poulsen

Reputation: 1

Only shows one row when making another query inside of a query

I have a friendlist on my website, and I've stored the usernames inside the database and not their full names. So I'd just make another query inside the friendlist...

while($r = mysqli_fetch_array($result)) {
    $username = $r['username'];
    // Wanna find the real name
    $sql = "SELECT * FROM users WHERE username = '$username'";
    $result = mysqli_query($con, $sql);
    $row = mysqli_fetch_array($result);
    $fullname = $row['fullname'];
    echo $fullname;
}

It works fine, but it only displays one row, and not all rows. When I just display the username, it shows all the rows.

Upvotes: 0

Views: 28

Answers (2)

tadman
tadman

Reputation: 211740

There's a multitude of problems here, but it boils down to doing a so-called N+1 query. You do one query to fetch N rows, and then you make N more queries, one for each row, to retrieve additional details.

You can do that all in one shot with a JOIN:

SELECT x, users.username FROM y WHERE (x conditions)
  LEFT JOIN users ON users.username=y.username

That way you can grab all that data at once. It's worth noting that using username as a key like this is often severely problematic as you'll either need to layer in a whole bunch of cascading trigger conditions to track referential integrity, or you won't have any referential integrity at all. Try and use numerical values as primary keys within your database, and use things like username only as labels for humans.

The risk here is if someone deletes their account and another person creates an account with exactly the same name, they "inherit" those friends from the former person. This has shown up on big sites and caused tons of trouble where people effectively gain ownership of content someone else has abandoned.

Upvotes: 1

Nigel Ren
Nigel Ren

Reputation: 57131

You re-use the $result used from your original query...

while($r = mysqli_fetch_array($result)) {
    $username = $r['username'];
    // Wanna find the real name
    $sql = "SELECT * FROM users WHERE username = '$username'";
    $result1 = mysqli_query($con, $sql);
    $row = mysqli_fetch_array($result1);
    $fullname = $row['fullname'];
    echo $fullname;
}

I've just changed the inner query to use $result1

You should also look into prepared statements and not inline your field values.

Upvotes: 0

Related Questions