Okay, looks like Giga's solution did the trick.
Tried them both.
Reading up on those Joins now.
@Sysk: Would mysql give an error when it does not support subqueries? I did not get one and i like the way that could work. I have all kinds of run-arounds for stuff, like this;)
Code:
$parentId = explode("_",$cPath);
$i=0;
$qm = $or;
$len = sizeof(explode("_",$cPath));
while ($i < $len) {
$matchMe = $parentId[$i];
$qm .= " $or (c.parent_id = ".$matchMe." OR c.categories_id = ".$matchMe.")";
$or = "OR";
$i++;
}
And then this is my query:
Code:
...
WHERE (c.parent_id = ".$matchMe." OR c.categories_id = ".$matchMe.")
...
Today's word is?
A-M-A-T-E-U-R
One new problem though, the following gives zero results, but it should give everything there's in the dbase. No specified store means, we sell everything;)
Code:
SELECT DISTINCT p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, pd.products_description,
IF (
s.status, s.specials_new_products_price, NULL
) AS specials_new_products_price, p.products_date_added, m.manufacturers_name
FROM products p, categories c, categories_stores cs, products_to_categories p2c
INNER JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
INNER JOIN products_description pd ON p.products_id = pd.products_id
INNER JOIN specials s ON p.products_id = s.products_id
WHERE c.categories_status =1
AND cs.categories_storename = '" . GLOBAL_CURRENT_STORE_NAME . "'
AND pd.language_id = '4'
AND p.products_id = p2c.products_id
AND c.categories_id = p2c.categories_id
AND products_status = '1'
ORDER BY RAND( )
LIMIT 4
This wont work
Code:
...
AND cs.categories_storename = '%" . GLOBAL_CURRENT_STORE_NAME . "%'
...