dousha1873
dousha1873
2016-03-08 09:15

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

已采纳

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.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • duanju8431 duanju8431 5年前

    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.

    点赞 评论 复制链接分享
  • douyi1855 douyi1855 5年前

    If your want to see one of the query variant (with the latest profile image), that's it:

    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
            LEFT JOIN profiles ON users.id = profiles.user_id
            LEFT JOIN profilephotos ON users.id = profilephotos.user_id
            WHERE profilephotos.id in (select max(id) from profilephotos group by user_id)
            ORDER BY id
    

    But I think that it is too complicated and may be wrong at all, because I don't know your tables schema.

    点赞 评论 复制链接分享