CODEWITHSUNDEEP
phpmysql
user1091856
user1091856

Reputation: 3158

PHP: Doing the same query over and over?

I have a Product class that when instantiated, fetch data from "product" table, it also creates a "Category" object inside it (product->category) that fetch data from the category table. My problem is, what happens when multiple products from the same category are listed, multiple equal queries are made in order to create the category object, since they're from the same category. How to solve this?

Upvotes: 0

Views: 80

Answers (4)

Rajan Rawal
Rajan Rawal

Reputation: 6317

Inside the loop executing sql query is not good practice.

What you can do is left join product table with the category tabel. 

$select = SELECT *
FROM product
LEFT JOIN category ON product.category_id = category.id;

Then execute query and then classify your products into category basis like you can run loop

$result = mysql_query($select);
while($data = mysql_fetch_assoc($result)){

   $product_info['product_name'] = $data['product_name'];
   $product_info['price'] = $data['product_price'];

   $product[$data['category']][$data['product_id']] = $product_info;
}

This loop will do the magic that it will help you to classify products falling under same category. And you can display your data in more effective way.

Upvotes: 0

CodeCaster
CodeCaster

Reputation: 151588

You'll have to build a layer that intercepts and/or caches your queries. When it sees you're requesting category with a certain ID, it should present that category from its cache, and if it isn't there, it should retreive and then cache it, so during a request the same row won't be queried more than once.

Doing this manually is a no-go, simply pick a decent ORM that will do this for you.

Upvotes: 2

Maxim Krizhanovsky
Maxim Krizhanovsky

Reputation: 26699

Use factory that creates the objects, and provide the option the data for product/category to be passed in the constructor. The factory loads the data for all products that will be listed, than instantiate the product objects, providing the already fetched data

Upvotes: 1

Dan Blows
Dan Blows

Reputation: 21174

Use a JOIN in your SQL:

SELECT *
FROM product
LEFT JOIN category ON product.category_id = category.id;

Upvotes: 2

Related Questions