RE: MySQL Query Help

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




SELECT * FROM products p LEFT JOIN criteria_values cv ON p.key=cv.key LEFT
JOIN criteria c ON cv.key=c.key WHERE c.value IS NOT NULL

Hard to answer without more detail, but I am guessing the answer will be
something like the above. Your question makes it hard to understand whether
c or cv is joined to p. So swap em around if I misunderstood. 

iPhone 4. It rocks!

On Nov 21, 2010, at 1:37 AM, Simcha Younger <simcha@xxxxxxxxxxxx> wrote:

> On Sat, 20 Nov 2010 13:54:29 -0700
> "Ben Miller" <biprellim@xxxxxxxxx> wrote:
> 
>> Hi,
>> 
>> I'm building a website for a client in which I need to compare their 
>> products, side-by-side, but only include criteria for which all 
>> selected products have a value for that criteria.
>> 
>> In my database (MySQL), I have a tables named "products","criteria" 
>> and "criteria_values"
>> 
>> If I have something like
>> 
>> $selected_product = array("1"=>"Product 1","2"=>"Product 2"...)  //  
>> All products selected for comparison by the user
>> 
>> I need to get only rows from "criteria" where there is a row in 
>> "criteria_values" matching "criteria.criteria_id" for each 
>> $selected_product
>> - in other words, if any of the $selected_product does not have a row 
>> in "criteria_values" that matches "criteria.criteria_id", that 
>> criteria would not be returned.  I hope that makes sense.
> 
> It would be a lot easier to think about this if you could provide the
table structure or create table statements.
> 
> If I understood correctly, you have products which reference a criteria ID
which has no matching value. If this is the problem you have a to first take
care of the integrity of your data, as this should never happen. 
> 

To help clarify - the 3 tables look something like the following (tableName
=> column,column,column...):

Products => product_id,product_name,product_description...  (key =
product_id)
Criteria => criteria_id,criteria_title,criteria_text,...  (key =
criteria_id)
Criteria_values => product_id,criteria_id,criteria_value,... (key =
product_id & criteria_id)

The user selects up to X product_id's to compare, stored in
$selected_products.

I then need to get each criteria_title and criteria_text from
table(criteria) where there is a matching criteria_id in
table(criteria_values) for each/all $selected_products, also returning the
criteria_value for each $selected_products, ultimately ending up with an
array or object that looks something like:

(Assuming the user selected Product A (product_id=1), Product B
(product_id=2) and Product C (product_id=3)

criteria => Array  (
	[$criteria_id] => Array (
		[title] => query_row[criteria_title]
		[text] => query_row[criteria_text]
		[values] => Array (
			[1] => Product A's value for this criteria
			[2] => Product B's value for this criteria
			[3] => Product C's value for this criteria
		)
	)
	[$criteria_id] => Array (
		.....
	)
)

Again, displaying only/all criteria where there is a matching value for
each/all $selected_products

Thanks again,
Ben



-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux