<?php
// ()
// initialize base variables for PDO connection
$host = 'localhost';
$user = 'root';
$password = 'bobi1996';
$dbname = 'pdolab';

// initialize DSN
$dsn = 'mysql:host=' . $host . ';dbname=' . $dbname;

// initialize PDO connection
$pdo = new PDO($dsn, $user, $password);

// get all posts
$sql = 'SELECT * FROM posts';
$statement = $pdo->prepare($sql);
$statement->execute([]);
$posts = $statement->fetchAll(PDO::FETCH_ASSOC);

foreach ($posts as $post) {
    echo $post['title'] . '<br>';
}

// get all posts from an inputted author
$author = "John Doe";
$sql = 'SELECT * FROM posts WHERE author = :author';
$statement = $pdo->prepare($sql);
$statement->execute(['author' => $author]);
$posts = $statement->fetchAll(PDO::FETCH_ASSOC);

foreach ($posts as $post) {
    echo $post['title'] . '<br>';
}

// insert a post
$title = "Python & AI";
$code = "a4";
$author = "Ana Sparrow";
$sql = 'INSERT INTO posts(title, code, author) VALUES(:title, :code, :author)';
$statement = $pdo->prepare($sql);
$statement->execute(['title' => $title, 'code' => $code, 'author' => $author]);
echo "Post with the title \"" . $title . "\" is added in the database!";

// search for posts
$keyword = "%data%";
$sql = 'SELECT * FROM posts WHERE title LIKE :keyword';
$statement = $pdo->prepare($sql);
$statement->execute(['keyword' => $keyword]);
$posts = $statement->fetchAll();
foreach ($posts as $post) {
    echo $post['title'] . '<br>';
}