Hey there! Nice work getting your API to pull post images and descriptions—you're halfway there. To include the username and avatar from the users table, you just need to join your Posts and users tables in your SQL query, since each post should be linked to a user via a foreign key (like user_id in the Posts table that matches the id in the users table).

Here's how to adjust your code step by step:

1. Update the SQL Query with a JOIN #

Replace your current SELECT statement with a joined query to pull in user data along with post details. I'll use INNER JOIN here (it only returns posts that have a matching user in the users table; use LEFT JOIN if you want to keep posts even if the user data is missing):

// Adjust the column names (user_id, id, username, avatar) to match your actual table schema!
$sql = "SELECT Posts.*, users.username, users.avatar 
        FROM Posts 
        INNER JOIN users ON Posts.user_id = users.id";

2. Clean Up Your PHP Code #

Your original loop had a small quirk where you were reassigning $tem = $row each time—here's a cleaner way to build your array of posts and output JSON:

$sql = "SELECT Posts.*, users.username, users.avatar 
        FROM Posts 
        INNER JOIN users ON Posts.user_id = users.id";
$result = $conn->query($sql);

$posts = [];
if ($result->num_rows > 0) {
    // Loop through each row and add it to the posts array
    while($row = $result->fetch_assoc()) {
        // Optional: If your avatar is stored as a file path, convert it to a full URL
        // $row['avatar'] = "https://your-app-domain.com/avatars/" . $row['avatar'];
        $posts[] = $row;
    }
    // Encode the array to JSON and output it
    echo json_encode($posts);
} else {
    // Return an empty array if there are no posts
    echo json_encode([]);
}

Important Notes #

• Double-check your table and column names! If your users table uses a different primary key (not id) or your Posts table uses a different foreign key (not user_id), update those in the JOIN clause.
• If your avatar column stores a local file path (like user123.jpg), you'll want to prepend your server's base URL to it (as shown in the commented line) so your Android app can load the image directly.

内容的提问来源于stack exchange，提问作者Vine