CODEWITHSUNDEEP
phpsqloptimization
GorillaApe
GorillaApe

Reputation: 3641

SQL queries or php code?

Hello i am in a delima
Suppose that i have 50 products in a category and i want to get all the features of my products...
Ofcourse a SQL join wont help because joining would return product info many times!

So here is the question.. what is better
PLAN A
Get all the products' features in category with one SQL query and then iterate with php each feature and put it in Product.

PLAN B
For each product get the features by calling a query

Other solutions accepted!

EDIT

My table schema outline is..
A table Product which has product info(per row)
A table features which has features (feature id )
A table for features' values
And a table that has Products with their features and values

Upvotes: 1

Views: 120

Answers (4)

jon_darkstar
jon_darkstar

Reputation: 16768

$sql1 = "SELECT * FROM products P,  ". //don't use star, make sure no fields are overwritten
         INNER JOIN products_to_features PTF on P.id = PTF.project_id
         INNER JOIN features F F.id = PTF.feature_id
         ORDER BY P.id";
$r = mysql_query($sql1, $conn);

$arr = array();
$lastProductId = -1;
while ($row = mysql_fetch_assoc($r))
{
   if ($row[p_id] != $lastProductId)
   {
       $lastProductId = $row['p_id'];
       $arr['p_id'] = array('productName' => $row['p_name'], 
                            'productPrice' = $row['p_price'],
                            'productFeatures' = array(),
                             //other fields from product table);
   }
   $arr['p_id']['productFeatures']['f_id'] = array('featureName' => $row['f_name'], blah...);
}

I don't know your fields obviously, and you may want to join on feature_values so that will be more work. You can do keys/values different (ie - product names as keys. Feature-name as keys with feature-value as values, whatever you want) but the point is this is doable (and recommended) in one query.

Upvotes: 2

Joel Coehoorn
Joel Coehoorn

Reputation: 415735

Not Plan B.

Whatever you do this can and should be done with one or at most two total queries (one for headers, one for correctly sorted list of features + id column). Whether that query is Plan A or some unmentioned Plan C depends on your exact table structure, which isn't clear from your question.

Upvotes: 2

bcosca
bcosca

Reputation: 17555

A query inside a loop will greatly degrade performance of your application. Avoid it at all cost.

Show us the schema. There's a strong possibility a single SQL query will solve your problem.

Upvotes: 0

Alex
Alex

Reputation: 65942

Generally, the less database queries you have to make, the better. It greatly depends on your table structure, but I'd go with Plan A.

Upvotes: 0

Related Questions