从一个foreach语句中检索变量并在另一个foreach语句中显示

Question

I am trying to use a variable from one array within a foreach statement of another array.

I have a grid view that displays a user's profile photo, their display name, current location and availability as well as using their username to complete the link applied to the box.

The user's table holds; the username (and user id) should I need that in the future.

The profiles table holds; the display name.

The profilephotos table holds; the profile photo.

The locations table holds; current location.

All are linked in the tables by both the user_id and username columns which match back up with the users table.

The code I have for the user box is;

<?php foreach($rows as $row): ?> 
<div class="box">
    <div class="boxInner">
      <a href="profile.php?username=<?php echo htmlentities($row['username'], ENT_QUOTES, 'UTF-8'); ?>&uid=<?php echo htmlentities($row['id'], ENT_QUOTES, 'UTF-8'); ?>">


      <img src="uploads/profile-photos/<?php echo htmlentities($pphoto['profilephoto_file'], ENT_QUOTES, 'UTF-8'); ?>" />


      <div class="titleBox" style="text-align:left; line-height:20px;">
      <span style="font-size:18px; font-weight:bold;"><i class="fa fa-user"></i> <?php echo htmlentities($row['profile_displayname'], ENT_QUOTES, 'UTF-8'); ?>,
      <?php echo htmlentities($row['profile_displayage'], ENT_QUOTES, 'UTF-8'); ?></span>
      <br />
      <span style="font-size:14px;"><i class="fa fa-map-marker"></i> City Name &nbsp;|&nbsp; <i class="fa fa-clock-o"></i> Now</span>
      </div></a>
    </div>
  </div>
<?php endforeach; ?> 

My SQL query and array code is;

$query = " 
        SELECT 
            users.id, 
            users.username,
            users.email,
            profiles.profile_displayname,
            profiles.profile_displayage,
            profiles.profile_photo
        FROM users, profiles
        WHERE users.id = profiles.user_id;
    "; 

    try 
    { 
        // These two statements run the query against your database table. 
        $stmt = $db->prepare($query); 
        $stmt->execute(); 
    } 
    catch(PDOException $ex) 
    { 
        // Note: On a production website, you should not output $ex->getMessage(). 
        // It may provide an attacker with helpful information about your code.  
        die("Failed to run query: " . $ex->getMessage()); 
    } 

    // Finally, we can retrieve all of the found rows into an array using fetchAll 
    $rows = $stmt->fetchAll(); 

    $query = " 
        SELECT 
            users.id, 
            users.username,
            profilephotos.user_id,
            profilephotos.profilephoto_file
        FROM users, profilephotos
        WHERE users.id = profilephotos.user_id;
    "; 

    try 
    { 
        // These two statements run the query against your database table. 
        $stmt = $db->prepare($query); 
        $stmt->execute(); 
    } 
    catch(PDOException $ex) 
    { 
        // Note: On a production website, you should not output $ex->getMessage(). 
        // It may provide an attacker with helpful information about your code.  
        die("Failed to run query: " . $ex->getMessage()); 
    } 

    // Finally, we can retrieve all of the found rows into an array using fetchAll 
    $profilephotos = $stmt->fetchAll(PDO::FETCH_ASSOC);

No matter what I try - I just can't get the foreach to pull in the right image. I've managed to pull in an image but the foreach statement applied the same image to every user regardless of the ID I instructed the query for $profilephotos to look for.

What am I doing wrong? Am I even going about this the right way?

Any help would be greatly appreciated - please note though I am new to PHP.

Answer 1

Your need one query instead of two. The query may look like this:

SELECT 
            users.id AS id, 
            users.username AS username,
            users.email as email,
            profilephotos.profilephoto_file AS file_photo,
            profiles.profile_displayname AS file_displayname,
            profiles.profile_displayage AS displaypage,
            profiles.profile_photo AS photo
        FROM users
        JOIN profilephotos ON users.id = profilephotos.user_id
        JOIN profiles ON users.id = profiles.user_id;

Your need two use joins -- it is better practise. And pay attention on keyword 'AS' -- it help to disambiguate identical column names in different tables.