Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Sep 2012
    Posts
    21
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Query within Query

    I have a table 'friends' which has username and friend columns so if a user is friends with another user it is saved in this table. I also have another table with posts users have made. I am trying to make it only show posts from the users friends. However it is only running the query for the first 'friend' in the table. What's wrong?


    Code:
    <?php
    
    
    $query = "SELECT * FROM friends WHERE username='$username'";
    $result = mysql_query($query) or die (mysql_error());
    while($row = mysql_fetch_array($result)) {
    
    
    $friend = $row['follow'];
    
    
    
    $query = "SELECT * FROM posts WHERE username='$friend'";
    $result = mysql_query($query) or die (mysql_error());
    while($row = mysql_fetch_array($result)) {
    
    
    $id=$row['id'];
    $post=$row['post'];
    
    
    echo $post;
    echo " posted by ";
    echo $friend;
    echo "<br><br><br>";
    
    
    }
    }
    
    ?>

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Your overwriting $row.
    In any case, this isn't the way to do this. You never query in a loop unless there is no other option. This should be joined.
    Code:
    SELECT p.id, p.post, p.username
    FROM posts p
    INNER JOIN friends f ON f.follow = p.username
    WHERE f.username = '$username'

  • Users who have thanked Fou-Lu for this post:

    penny94 (11-13-2012)

  • #3
    New Coder
    Join Date
    Sep 2012
    Posts
    21
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    Your overwriting $row.
    In any case, this isn't the way to do this. You never query in a loop unless there is no other option. This should be joined.
    Code:
    SELECT p.id, p.post, p.username
    FROM posts p
    INNER JOIN friends f ON f.follow = p.username
    WHERE f.username = '$username'
    Thanks this is working!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •