1

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

Improve this question
2
  • 3
    you should post more on your schema. How are your tables organized? Commented Nov 22, 2010 at 4:08
  • Yes, please update your question with your schema. A join may be actually be useful, depending on how exactly data is organised, and what exactly you are trying to get out of the data. Commented Nov 22, 2010 at 4:18

4 Answers 4

Reset to default
2 
$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.

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

2 Comments

Thank you for your time.. There is an extra table for feature VALUES.. Would you might telling me if i have to do a seperate query?Thanks again
no, you still don't its just one more join i'm not really sure what your table looks like, but i get the impression your products-to-features-to-feature-values table is unnecessary (if thats what it is). there should be a many to many between products and feature values. feature values are an instance of a type of feature. (ie - feature 'flavor' feature value 'strawberry'). once the query is done, just add the new values to #arr in similar fashion. you may want to have each feature be a key for its respective value
2

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.

Improve this answer

Comments

0

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.

Improve this answer

Comments

0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.