优化PHP函数和SQL查询以从MySQL数据库中提取相关数据

Question

If I have two tables with their respective columns:

• product_catrgories: id, name
• products: id, category_id, name

And I have the following PHP functions:

• function getProducts() { ... } // Returns an array of all rows from the products
• function getProductCategory($category_id) { ... } // returns the corresponding row to the given id of the product category

Currently, I am displaying a list of all products in a table with the following headings:

• id
• Category
• Name

Where category is the name of the category corresponding to the category_id of the product.

Currently I am using a foreach loop to iterate through the product and calling the getProductCategory(...) function for each product:

<?php
...

$products = getProducts();

foreach ($products as $product) {
    $product_category = getProductCategory($product['category_id']);
    ...
}

...
?>

Which would be an extensive amount of queries to the database if there are lots of products with many repeated queries.

Is there a way I can optimize this operation?

Answer 1

As suggested in the comments, I decided to use eager loading through the use of SQL JOIN statements for example I would get the ids of all the products and use WHERE id IN(...) in my sql query:

<?php
$products = getProducts();
$product_ids = array();

foreach ($products as $product) {
    $product_ids[] = $product->id;
}

$product_categories_data = $db->query('SELECT * FROM products_categories WHERE id IN(?, ?, ?, ..., ?)', $product_ids);
?>

Then performing the rest of my operations on the results.