Inserting data from NYT API into DB

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.

I believe the nested foreach loops are the issue, however I cannot quite figure it out.

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);
    }
}