How would i go about grabbing the ID of a post in my database when it exists in an array from a previous function?

Question

I'm fairly new with PHP and with MySQL/Wampserver/MVC model, as i'm still learning it. In a project i'm working on, i have a database containing multiple tables ( users, posts, commentary ). I've worked out how to 1)register a new post inside the db upon submitting a form, and on an homepage, fetch all the posts to show them in a list. Such has been done through the following code :

 public function getAllPosts()
    {
        $query = "SELECT * FROM posts ORDER BY timestamp(date) desc LIMIT 5";
        $request = $this->db->prepare($query);
        $request->execute();
        $allPost = $request->fetchAll(PDO::FETCH_ASSOC);
        var_dump($allPost);

        return $allPost ? $allPost : [];
    }

$allPost here is returned as an array, containing multiple infos Since this array contains the IDs of every post, my idea to select a specific ID from a specific post is to fetch the ID existing in the array generated to make the list of post, and pass it to the address with the POST method.

Is this a convoluted thing to do ? If so, what would you suggest i do instead ?

The things i tried so far : Create a new public function that grabs the ID with a new query from the database ( "SELECT id FROM tablename WHERE id=?") but this did not work, and simply returned " boolean false " on var_dump

    public function getOnePostById()
    {
        $query = "SELECT id FROM posts WHERE id=?";
        $request = $this->db->prepare($query);
        $request->execute();
        $singlePost = $request->fetch(PDO::FETCH_ASSOC);
        var_dump($singlePost);
    }

I then tried to pass to getOnePostById the resulting array from the previous function while this seems to be accepted by my code, in my controller afterwards, the array $allPost was flagged as an undefined variable

Answer 1

You have to provide a value for a placeholder in the query, either by using bindParam or bindValue method of PDOStatement or adding it as array parameter to execute()

    public function getAllPosts(): array
    {
        $query = "SELECT * FROM posts ORDER BY timestamp(date) desc LIMIT 5";
        $request = $this->db->query($query);
        return $request->fetchAll(PDO::FETCH_ASSOC);
    }

    // if your Post ID is not a number, use string $id
    public function getOnePostById(int $id): ?array
    {
        $query = "SELECT * FROM posts WHERE id = ?";
    
        $request = $this->db->prepare($query);
        $request->execute([$id]);
        return $request->fetch(PDO::FETCH_ASSOC);
    }

    // Here you can just check post existence by given id
    // if your Post ID is not a number, use string $id
    public function isPostExists(int $id): bool
    {
        $query = "SELECT 1 AS exist FROM posts WHERE id = :id";
    
        $request = $this->db->prepare($query);
        // if your Post ID is not a number, use PDO::PARAM_STR
        $request->bindParam('id', $id, PDO::PARAM_INT);
    
        $request->execute();
        
        // FETCH_OBJ example
        return (bool)$request->fetch(PDO::FETCH_OBJ);
    }

P.S. FETCH_ASSOC is bad practice (because it's just an array, it can be a list of entries, or it can be a single entry). PDO::FETCH_OBJ should be preferred.
See more about PDO fetch modes and how to set default fetch mode (for entire PDO instance, not just PDOStatement) and many other settings here.

UPD: And also $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); is good practice, your PDO instance will throw exceptions instead of silent failures.

See difference between bindParam and bindValue - here