Pdo fetching data based on current logged in user

[Jeffw]

I’m stuck trying to figure out how to display the currently logged in users posts, but only their posts in their profile page. They are able to log in and the session appears to work, but i’m struggling trying to pull only that users posts based on their session information. this is where i’m at so far.

 $id = $_SESSION['email'];

if ($id) {

$stmt = $pdo->prepare("SELECT * FROM posts WHERE member_id=:member_id");

$stmt->execute([$id]);

// $posts = $stmt->fetch();

$posts = $stmt->fetchAll(PDO::FETCH_ASSOC);

[Strider64]

if ($id) {
    $stmt = $pdo->prepare("SELECT * FROM posts WHERE member_id=:member_id");

    // bind the id to the member_id parameter
    $stmt->bindParam(':member_id', $id, PDO::PARAM_INT);

    $stmt->execute();

    // Fetch all posts
    $posts = $stmt->fetchAll(PDO::FETCH_ASSOC);
}

or

if ($id) {
    $stmt = $pdo->prepare("SELECT * FROM posts WHERE member_id=:member_id");
    
    // bind the id to the member_id parameter in execute method
    $stmt->execute([':member_id' => $id]);

    // Fetch all posts
    $posts = $stmt->fetchAll(PDO::FETCH_ASSOC);
}

The : in $stmt->execute([':member_id' => $id]); is really not needed. (Though it might be just later versions of PHP)

[Jeffw]

Thank you. I’m not getting any errors but still not getting data. I’m wondering if I need to adjust the query I was thinking it would be as simple as "SELECT * FROM posts WHERE member_id=:member_id", but wondering if it would need to be more like this, this is the one used to show all of the articles for the admin to monitor.

"SELECT a.id, a.title, a.summary, a.created, a.category_id, a.member_id, a.published,
               c.name     AS category,
               CONCAT(m.forename, ' ', m.surname) AS author,
               i.file     AS image_file,
               i.alt      AS image_alt 
          FROM posts    AS a
          JOIN category   AS c   ON a.category_id = c.id
          JOIN member     AS m   ON a.member_id   = m.id
          LEFT JOIN image AS i   ON a.image_id    = i.id
         ORDER BY a.id DESC;"

[Strider64]

You do have error reporting turned on?

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

as for using more than one table someone here would be better off than me as I just use one table at time. The one thing I can’t multitask with is using more than one table at the same time.

[phdr]

The query you write is dependent on the data that you want. If the first select query gets the data that you want, that’s all you need. The second query gets the category name, member name, and any image data.

What result or output do you get?

The code is dependent on there being a TRUE $id value. Does your code actually validate that the session variable isset? What does var_dump($_SESSION); show?

Next, while the email does uniquely identify a user, in relational data, you should use the auto-increment primary index from main/parent table in any related data. The member_id column in the posts data should be the member table’s id value, and in fact, based on the last posted JOIN query it is - JOIN member AS m ON a.member_id = m.id

Your login system should (only) store the member table’s id in the session variable to indicate who the logged in user is. You should query on each page request to get any other user data, such as the username, email, user permissions, …

[Jeffw]

my goal for this query is to show the logged in users posts so they can then edit the post, delete it, and see if its published and see overall their list of posts.
<th>Image</th><th>Title</th><th class="created">Created</th><th class="pub">Published</th><th class="edit">Edit</th><th class="del">Delete</th>

When I var_dump($_SESSION); it displays an array of the logged in users details, but when I var_dump($posts); it displays 0.