Hard mysql Query

[cyberzerocool]

Hello first time posting. I try not to ask because Google is great but I hope I can get a little help with my problem.

 

I'm creating a cell phone accessory store and need to do a large query for categories and/or products based on a phone_id or based on a link in between phone features and product features. I had to extend my system for multiple category ids which make this that much harder.

 

So heres what I got, I'm trying to display categories for a selected phone where there are products in categories and where the product_phone_id equals that phone or the features match.

 

$result1 = mysql_query("SELECT  `cat`.`id` ,  `cat`.`name`, `cat`.`imagethumb`

FROM  `categories`  `cat` ,  `features`  `f` ,  `product_features`  `prf` ,  `phone_features`  `phf` ,  `products`  `p`

WHERE `p`.`cat_ids` LIKE %`cat`.`id`%");

 

// `p`.`cat_ids` = `cat`.`id`

// `phf`.`id` =  `prf`.`id` =  `f`.`id`

//  `phf`.`phone_id` = $phone_id AND 

//HAVING COUNT(  `p`.`id` =  `prf`.`product_id` ) > 0;

 

I've been messing with it for days and cant get it to show categories correctly

 

Please help

Joe

[blacknight]

ok dude your gona get some help ... one thing ial let yea know my swl when your doing some thing your have to be exact when your doing select statements...

 

when using "Select `cat`.`id`" you have to define every field you wana call in the query here from all tables just so you know....

 

so from what i have gathered you have 5 tables 'categories' 'features' 'product_features' 'phone_features' 'products'

 

so here is what i think you are trying to do....

 

SELECT  `cat`.`id` ,  `cat`.`name`, `cat`.`imagethumb` FROM  `categories` AS cat

LEFT JOIN `features` AS f ON `f`.`id` = `cat`.`id`
LEFT JOIN `product_features` AS prf phf`.`id` =  `prf`.`id` =  `f`.`id`
LEFT JOIN `phone_features` AS phf ON `phf`.`phone_id` = $phone_id
LEFT JOIN `products` AS p ON `p`.`cat_ids` = `cat`.`id` WHERE [i]your where statement here[/i]

 

what this does so you can make any changes you need to do ....

" FROM  `categories` AS cat" defines the catagorie table as `cat` so this is used for all table ref's that your gona have ...

"LEFT JOIN `features` AS f ON `f`.`id` = `cat`.`id`" this joins features ref as "f" with catagories On (where basicly) f.id=cat.id

 

now at the end of my code is where your where statement would go...

i hope tjhis helps you out

 

cheers

[cyberzerocool]

Thank you for the help, I am close. I have had it working only, now its returning products that have features but that don't even match. Heres where I'm at with my query.

 

Also your example was matching ids only instead of product_id, phone_id in the product_features and phone_features

 

All I'm trying to do is show categories/products for where products are in the categories, which I got. And the phone features match the product features.

 

SELECT DISTINCT p.id, p.name, p.image1thumb, p.retail_price FROM products p 
LEFT JOIN product_features prf ON p.id = prf.product_id 
LEFT JOIN phone_features phf ON phf.phone_id = 18 
WHERE p.cat_ids REGEXP '[[:<:]]52[[:>:]]'

 

Thanks

[kickstart]

Hi

 

The SQL above uses LEFT OUTER JOINs which will return an empty matching row if there is no match.

 

For example say you had a table of people and a table of children. If you did a normal JOIN (INNER JOIN) between the 2 tables then anyone on the first table who didn't have any children wouldn't be returned. If you use a LEFT OUTER JOIN then anyone on the first table who had no children would be brought back but with the columns from the children table being NULL.

 

You can exclude the unwanted records in the WHERE clause (or if you want in the ON clauses).

 

All the best

 

Keith

[cyberzerocool]

Thanks for the help but I'm still not getting it to work and I get back a product that doesn't match the product/phone features.

 

Should I change the type of Join?

 

Please help.

 

I tried adding an extra WHERE but same thing. Should I try it on the join?

SELECT DISTINCT p.id, p.name, p.image1thumb, p.retail_price FROM products p 
LEFT JOIN product_features prf ON p.id = prf.product_id 
LEFT JOIN phone_features phf ON phf.phone_id = 20 
WHERE phf.feature_id = prf.feature_id AND p.cat_ids REGEXP '[[:<:]]52[[:>:]]'

 

 

[cyberzerocool]

Actually don't I need a "WHERE IN" to only find products that match and not the ones that don't?

 

Also was trying to avoid Joins altogether and tried this but it just looped like crazy.

					$result = mysql_query("SELECT feature_id FROM phone_features WHERE phone_id = $upid");
						while($row = mysql_fetch_array($result)){
							$result = mysql_query("SELECT feature_id FROM product_features");
							while($row2 = mysql_fetch_array($result2)){
								if($row2['feature_id'] = $row['feature_id']){
									echo $row2['feature_id'];
								}
							}
						}

 

 

Thanks

[cyberzerocool]

Also tried adding more to it but I still get the same thing. Why are these not matching correctly?

SELECT DISTINCT p.id, p.name, p.image1thumb, p.retail_price 
FROM products p
LEFT JOIN product_features prf ON p.id = prf.product_id AND prf.feature_id = phi.feature_id
LEFT JOIN phone_features phf ON phf.phone_id = $upid
WHERE p.cat_ids REGEXP '[[:<:]]".$cat_id."[[:>:]]'

 

[kickstart]

Hi

 

Taking a step back, we need to know how the columns join up. What fields on each table match those on other tables?

 

How do things relate to each other?

 

All the best

 

Keith

[cyberzerocool]

Ok,

 

The products table has the product id and categories

The product_features table has an id, product_id, and feature_id

The phone_features table has an id, phone_id, and feature_id

 

The features table isn't really needed for this but it has an id, and name

 

And thats it, I'm just looking to get products/categories where product features has at least one or more matching feature_id.

 

Thanks again for the help. I'm really stuck.

 

[kickstart]

Hi

 

If all you want is the category id and product id of things that have the features you need then something like this will do it

 

SELECT cat.id, product.id, COUNT(*)
FROM categories  cat
INNER JOIN products ON cat.id = Products.cat_ids
INNER JOIN product_features ON products.id = product_features.product_id
WHERE product_features.feature_id IN (somefeature, anotherfeature, etc)
GROUP BY cat.id, product.id

 

All the best

 

Keith

[cyberzerocool]

Thank you but I don't think I described it right.

 

I will paypal $20 if I could get this right.

 

I need a query to show products in a given category with a given phone id that has any matching features out of the product_features and phone_features

 

And a query to show categories with products that match the above query to show categories with products in them

 

 

But I can't use the IN unless a sub select could be in it.

 

 

 

Does this clear it up?

 

Thanks again

[kickstart]

Hi

 

How does the phone table relate to the products or categories tables?

 

I see that a feature belongs to a product or phone and that a product belongs to a category, but I can't see how a phone relates to a product or category.

 

All the best

 

Keith

[cyberzerocool]

I'm sorry. This is a store selling phone accessories. A feature must belong or a product and a phone for the product to show up. Its kinda nested. You pick your phone then choose the categories/subcategories. Or you could just choose a category but none of this would apply then since we wouldn't know the phone show we're showing all.

 

On the admin side you can assign features to products or assign a phone to a product (which I got). You also assign features to phones that way when a new phone comes out like the iPhone 5 all the products that use the 30pin dock connector feature applies when we simply add the iPhone 5.

 

Sorry, I didn't give you the bigger picture. But cool idea huh? new sortof e-commerce

 

Thanks

[kickstart]

Hi

 

Ah, right.

 

So you have a category, which contains products which have features, and products contain phones that have features.

 

So in simple terms the category could be a pre pay deal or a pay monthly deal, while a product under that category could be (say) a £30 a month contract which could have a feature of free insurance or a £15 a month contract with no free insurance, while a phone could also have a feature of free insurance.

 

If so something like this:-

 

SELECT DISTINCT cat.id, product.id, phone_id
FROM categories  cat
INNER JOIN products ON cat.id = Products.cat_ids
INNER JOIN product_features ON products.id = product_features.product_id
INNER JOIN phone ON products.id = phone.product_id
INNER JOIN phone_features ON phone.id = phone_features.phone_id
WHERE product_features.feature_id IN (somefeature, anotherfeature, etc)
OR phone_features.feature_id IN (somefeature, anotherfeature, etc)

 

That gives you a list of all the categories / phoducts / phones where either the product or phone have one of the required features.

 

If you want a list of these which have ALL the required features (either on the product or the category)

 

SELECT DISTINCT cat.id, product.id, phone_id
FROM categories  cat
INNER JOIN products ON cat.id = Products.cat_ids
INNER JOIN product_features ON products.id = product_features.product_id
INNER JOIN phone ON products.id = phone.product_id
INNER JOIN phone_features ON phone.id = phone_features.phone_id
WHERE somefeature IN (product_features.feature_id, phone_features.feature_id
AND anotherfeature IN (product_features.feature_id, phone_features.feature_id

 

All the best

 

Keith