PHP MySQL Multiple SQL Queries or JOIN for each query

Question

If I have two tables with their respective columns:

=====================
product_categories
=====================
id | name
=====================

=======================
products
=======================
id | category_id | name
=======================

And I have the following PHP functions:

// Returns an array of all rows from the products
function getProducts() { ... } 

// returns the corresponding row to the given id of the product category
function getProductCategory($category_id) { ... }

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.

Would it be good practice if the getProducts() function include all it's category information using a JOIN in the SQL statement?

I would suggest you to create another method. getProducts() looks like a simple CRUD operation, what you need it is more "customized". It is a good practice in terms of performace to create a custom query with the fields that you want — xkothe
– xkothe, Commented Aug 2, 2018 at 11:59

Mickaël Leger · Accepted Answer · 2018-08-02 12:54:34Z

1

If you want to get id, category_name and product_name each time, you should make ONE method and avoid to make one select, then a foreach on each product and an other select to get all value.

So try something like this maybe :

function getProductsData() {

    $querie = 'SELECT 
                 p.id, p.name,
                 pc.name as Category
               FROM products as p
               LEFT JOIN product_categories as pc on pc.id = p.category_id
               ORDER BY p.name; -- optionnal
              ';

    // Get the data and return them
    // You should get an array with one row = 
    // id (of the product) / name (of the product) / Category (the name of the category)


}

edited Aug 2, 2018 at 12:54

answered Aug 2, 2018 at 12:08

Mickaël Leger

3,4482 gold badges21 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nagesh · Accepted Answer · 2018-08-02 12:08:14Z

1

Yes, Join will be better. It will reduce the number of queries for fetching the category. Otherwise, you can do the separate query for getting the categories as below code, but I will suggest Join.

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

You have to modify the query in getProductCategory() and add where in condition for comparing category ID In getProductCategory(), you can implode the category IDs, for adding it into the where in clause.

answered Aug 2, 2018 at 12:08

Nagesh

4373 silver badges13 bronze badges

Collectives™ on Stack Overflow

PHP MySQL Multiple SQL Queries or JOIN for each query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related