I have three tables that relate to eachother.
Products
id,
name,
code,
image,
thumbnail,
description
Custom_ProdFields
id,
code,
name
Custom_ProdValues
field_id,
product_id,
value
The product table is the basic product information every product has. The other two tables extend the first table and allow any number of additional product fields. There are two additional "custom" field values with the field_ids of 1 and 5 I need to retrieve with my select from the products table. This data will come from the Custom_ProdValues table, but it relates to the Products table through the ProdFields table. So, I need to do something like:
Code: Select all
SELECT
*
FROM
`s01_Products`
LEFT JOIN
`s01_CFM_ProdValues`
ON
`s01_CFM_ProdValues`.`product_id` = `s01_Products`.`id`
WHERE
`code`
REGEXP
'^[A-Z]{3}[0-9]+([A-Z]{2}|[B-Z])$'
AND
`s01_CFM_ProdValues`.`field_id`
IN
(5,1)
LIMIT
100desired result set wrote: [id] => 1797
[name] => Stella Tote/Pack Small
[thumbnail] => graphics/products/ELL/thumbs/ELL001C.jpg
[image] => graphics/products/ELL/ELL001C.jpgCode: Select all
=> ELL001C [value-color] => Pearl [value-manufacturer] => ELL[/quote] Instead of returning two seperate arrays like it currently is: [quote="current result set"] [id] => 1797 [name] => Stella Tote/Pack Small [thumbnail] => graphics/products/ELL/thumbs/ELL001C.jpg [image] => graphics/products/ELL/ELL001C.jpgCode: Select all
=> ELL001C [value] => Pearl [id] => 1797 [name] => Stella Tote/Pack Small [thumbnail] => graphics/products/ELL/thumbs/ELL001C.jpg [image] => graphics/products/ELL/ELL001C.jpgCode: Select all
=> ELL001C [value] => ELL[/quote] I apologize for the horrible explanation. I reworded this like 10 times and I can't come up with a better way to ask.