Hi all,
my first post here. I am working on a personal test project building a MVC framework to insert NYT Book API data into a database and display the content in the front end. I am using XAMPP for this project.
Currently the categories are loaded onto the database, however I wanted to include the buy links for each book as well by adding a foreign key book_id and populating it with the collected id from lastInsertId() of the book. However I am running into maximum execution exceeded errors.
What am I doing wrong?
How would I also limit the uploads to the DB? Since currently with each refresh it’s reloading the data in the DB.
public function getBooks()
{
$apiKey = 'apikey';
$genresURL = "https://api.nytimes.com/svc/books/v3/lists/names.json?api-key=" . $apiKey;
$response = file_get_contents($genresURL);
$genresData = json_decode($response, true);
$genres = [];
foreach ($genresData['results'] as $genre) {
$genres[] = str_replace(' ', '-', $genre['list_name']);
}
$sqlGenre = "INSERT INTO genres (name) VALUES (:name) ON DUPLICATE KEY UPDATE name = name;";
foreach ($genres as $genre) {
$this->db->query($sqlGenre);
$this->db->bind(':name', $genre);
try {
$this->db->execute();
} catch (Exception $e) {
echo "Error inserting genre: " . $e->getMessage();
}
}
foreach ($genres as $genre) {
$genresApi = "https://api.nytimes.com/svc/books/v3/lists/current/" . urlencode($genre) . ".json?api-key=" . $apiKey;
$resp = file_get_contents($genresApi);
$booksData = json_decode($resp, true);
if (!empty($booksData) && isset($booksData['results']['books'])) {
foreach ($booksData['results']['books'] as $bookDetail) {
$publishedDate = $booksData['results']['published_date'];
$sql = "INSERT INTO books (title, isbn13, isbn10, author, description, publisher, published_date, cover_image_url, page_count, language, average_rating, rating_count, availability_status, availability_count, format, edition, contributor, price, age_group, book_image_url, amazon_product_url, rank, rank_last_week, weeks_on_list, book_image_width, book_image_height, book_uri)
VALUES (:title, :isbn13, :isbn10, :author, :description, :publisher, :published_date, :cover_image_url, :page_count, :language, :average_rating, :rating_count, :availability_status, :availability_count, :format, :edition, :contributor, :price, :age_group, :book_image_url, :amazon_product_url, :rank, :rank_last_week, :weeks_on_list, :book_image_width, :book_image_height, :book_uri)
$this->db->query($sql);
$this->db->bind(':title', $bookDetail['title'] ?? '');
$this->db->bind(':isbn13', $bookDetail['primary_isbn13']);
$this->db->bind(':isbn10', $bookDetail['primary_isbn10']);
$this->db->bind(':author', $bookDetail['author']);
$this->db->bind(':description', $bookDetail['description']);
$this->db->bind(':publisher', $bookDetail['publisher']);
$this->db->bind(':published_date', $publishedDate);
$this->db->bind(':rank', $bookDetail['rank']);
$this->db->bind(':rank_last_week', $bookDetail['rank_last_week']);
$this->db->bind(':weeks_on_list', $bookDetail['weeks_on_list']);
$this->db->bind(':book_image_width', $bookDetail['book_image_width']);
$this->db->bind(':book_image_height', $bookDetail['book_image_height']);
$this->db->bind(':book_uri', $bookDetail['book_uri']);
$this->db->bind(':cover_image_url', $bookDetail['cover_image_url'] ?? '');
$this->db->bind(':page_count', $bookDetail['page_count'] ?? '');
$this->db->bind(':language', $bookDetail['language'] ?? '');
$this->db->bind(':average_rating', $bookDetail['average_rating'] ?? '');
$this->db->bind(':rating_count', $bookDetail['rating_count'] ?? '');
$this->db->bind(':availability_status', $bookDetail['availability_status'] ?? '');
$this->db->bind(':availability_count', $bookDetail['availability_count'] ?? '');
$this->db->bind(':format', $bookDetail['format'] ?? '');
$this->db->bind(':edition', $bookDetail['edition'] ?? '');
$this->db->bind(':contributor', $bookDetail['contributor']);
$this->db->bind(':price', $bookDetail['price']);
$this->db->bind(':age_group', $bookDetail['age_group']);
$this->db->bind(':book_image_url', $bookDetail['book_image'] ?? '');
try {
$this->db->execute();
$last_id = $this->db->lastInsertId();
foreach ($bookDetail['buy_links'] as $buyLink) {
$buyLink_sql = "INSERT INTO buy_links (book_id, name, url) VALUES (:book_id, :name, :url)";
$this->db->query($buyLink_sql);
$this->db->bind(':book_id', $last_id);
$this->db->bind(':name', $buyLink['name']);
$this->db->bind(':url', $buyLink['url']);
$this->db->execute();
}
} catch (Exception $e) {
echo "Error inserting book: " . $e->getMessage();
}
}
}
sleep(15);
}
}