CREATE TABLE IF NOT EXISTS publishers (
publisher_id INT AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
PRIMARY KEY (publisher_id)
);
CREATE TABLE IF NOT EXISTS books (
book_id INT AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
isbn VARCHAR(13) NULL,
published_date DATE NULL,
publisher_id INT NULL,
PRIMARY KEY (book_id)
);
INSERT INTO books(title, isbn, published_date,publisher_id)
VALUES
('Goodbye to All That','9781541619883','2013-01-05', 3),
('The Mercies','9780316529235','2020-01-28', 3),
('On the Farm','9780763655914','2012-03-27', 2),
('Joseph Had a Little Overcoat','9780140563580','1977-03-15', 2);
DELIMITER $$
CREATE PROCEDURE `get_books_published_after` (IN published_year INT)
BEGIN
SELECT
book_id, title, isbn, published_date, name as publisher
FROM
books b
INNER JOIN publishers p
ON p.publisher_id = b.publisher_id
WHERE year(published_date) > published_year;
END$$
DELIMITER ;
CALL get_books_published_after(2010);
<?php
$sql = 'CALL get_books_published_after(:published_year)';
$publishers = [];
$statement = $pdo->prepare($sql);
$statement->bindParam(':published_year', $published_year, PDO::PARAM_INT);
$statement->execute();
$publishers = $statement->fetchAll(PDO::FETCH_ASSOC);
print_r($publishers);
// Close the cursor to free up the connection for the next query
$statement->closeCursor();
// Now you can safely execute another query
$books = $pdo->query("SELECT * FROM books")->fetchAll(PDO::FETCH_ASSOC);
print_r($books);