to give example of what i mean
if i
Code: Select all
SELECT from products, attributes LEFT JOIN on attributes WHERE...Code: Select all
do{ SELECT from attributes WHERE productid.... }while(mysql_fetch_assoc());Kendall
Moderator: General Moderators
Code: Select all
SELECT from products, attributes LEFT JOIN on attributes WHERE...Code: Select all
do{ SELECT from attributes WHERE productid.... }while(mysql_fetch_assoc());are you guys telling me that there is a way arong this?do{ echo $productID SELECT FROM features WHERE ID = $ID}while($mysql_fetch_assoc());
Given these tables, here is the SQL you need to retrieve the dataproducts----
ID| NAME| DEscription
123 | product 1 | some text
321 | product 2 | another text
345 | product 3 | this text
features-----
ID | productId | feature
1 | 123 | one feature
2 | 123 | another feature
3 | 123 | 3rd feature
4 | 345 | good feature
5 | 213 | basic features
6 | 213 | ok enuff with this
Code: Select all
sql = 'SELECT ' .
' products.id, products.name ' . // fields from products table
' , features.feature ' . // fields from features table
' FROM ' .
' products ' . // table names
' , features ' .
' WHERE ' .
' (products.id = features.productId) ' . // how the tables are joined
' ORDER BY ' . // odering clauses
' products.id ASC ' .
' , products.name ASC ';and even if i were to do a DISTINCT query I wouldnt get the expected results123 | product 1 | some text | one feature
123 | product 1 | some text | another feature
123 | product 1 | some text | one feature
123 | product 1 | some text | 3rd feature
thus if iOne thing though. I find it hard to impliment a single complex query in cases where i am searching for results that have a many to one relation and grouping is not a option.
to give e.g.
I have 2 tables products and features
obviously the features table would have many rows related to the one id in products in a case like this this is why i have to
Quote:
do{ echo $productID SELECT FROM features WHERE ID = $ID}while($mysql_fetch_assoc());
are you guys telling me that there is a way arong this?
Code: Select all
SELECT * FROM productsHowever,ID| NAME| DEscription
123 | product 1 | some text
321 | product 2 | another text
345 | product 3 | this text
Code: Select all
do{
SELECT * from features WHERE product = product['ID']
}while(mysql_fetch_assoc())and i repliedusually it's better to use one complex query to fetch all the data you need at once.
because using a complex query in this case to look up the 2 tables would yield duplicating results.I find it hard to impliment a single complex query in cases where i am searching for results that have a many to one relation and grouping is not a option.
Code: Select all
SELECT * FROM products, features LEFT JOIN features ON products.ID = features.productID // assuming this is correctI needed to get| 123 | product 1 | one feature |
| 123 | product 1 | another feature|
| 123 | product 1 | 3rd feature |
| 345 | product 3 | good feature |
+-----+-----------+------------------+
NOT WAT I WANT
but i cant get that can i? thus re-iterating i had to get the id from the products first then use a "do" loop to get the features information123|product 3|one feature|another feature|3rd feature
Actually you get what you need, 1 query. Now iterate over the results likekendall wrote:i would getCode: Select all
SELECT * FROM products, features LEFT JOIN features ON products.ID = features.productID // assuming this is correctI needed to get| 123 | product 1 | one feature |
| 123 | product 1 | another feature|
| 123 | product 1 | 3rd feature |
| 345 | product 3 | good feature |
+-----+-----------+------------------+
NOT WAT I WANTbut i cant get that can i? thus re-iterating i had to get the id from the products first then use a "do" loop to get the features information123|product 3|one feature|another feature|3rd feature
There was the implication that i could do a single query but i think i misread the comments because there isnt as far as i see. so my concern would more likely be performance of multiple select queries
Kendall
Code: Select all
$result = mysql_query("SELECT * FROM products, features LEFT JOIN features ON products.ID = features.productID");
// read first row from resultset
$row = mysql_fetch_assoc($result);
// initialise feature info
$info = '';
// initialise current product
$current_product = $row['product_name'];
// as long as there is a row
while ($row)
{
// this feature doesn't belong to the product we are processing anymore
if ($current_product != $row['product_name'])
{
// all the features for $current_product are in $info - use it
echo "PRODUCT: $current_product FEATURES: $info </br>";
// clear feature info
$info = '';
// move to next product
$current_product = $row['product_name'];
}
// add feature info
$info .= $row['feature'] . " | ";
// fetch next row
$row = mysql_fetch_assoc($result);
}