Need php/mysql query help, inside!

[Naughty]

I have a few tables (osc)

This is important:
cs.categories_storename = 'name.co.uk'

Right now, i get all products from any category, this is not supposed to happen. Does anybody have a clue what is wrong with our query? We tried to fix it by adding a line one by one, no dice

If you need any more info, please ask or ping me on icq 125586484 / msn inpresif/@/hotmail . com

SELECT cs.categories_storename,cs.categories_id,c.categories_id,c.parent_id,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, products_to_categories p2c
LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
LEFT JOIN products_description pd ON p.products_id = pd.products_id
LEFT JOIN categories_stores cs ON c.parent_id = cs.categories_id
LEFT JOIN specials s ON p.products_id = s.products_id
WHERE c.categories_status = 1
AND cs.categories_storename = 'name.co.uk'
AND products_status = '1'
AND c.categories_id = p2c.categories_id
AND pd.language_id = '4'
LIMIT 4

[Naughty]

Extra info:

This is inside the table cs

categories_stores_id  categories_id  categories_storename  
      1 121 name.co.uk

Table categories:

categories_id categories_image parent_id sort_order date_added last_modified categories_status 
      118 NULL 0 0 2008-06-08 21:09:59 NULL 1 
      119 NULL 118 0 2008-06-08 21:10:22 NULL 1 
      120 NULL 0 0 2008-06-09 15:06:57 NULL 1 
      121 NULL 120 0 2008-06-09 15:07:19 NULL 1 
      122 NULL 121 0 2008-06-09 15:07:32 NULL 1 
      123 NULL 120 0 2008-06-09 21:29:03 NULL 1 
      124 NULL 123 0 2008-06-09 21:29:22 NULL 1

[k0nr4d]

SELECT cs.categories_storename,cs.categories_id,c.categories_id,c.parent_id,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, products_to_categories p2c
LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
LEFT JOIN products_description pd ON p.products_id = pd.products_id
LEFT JOIN categories_stores cs ON c.parent_id = cs.categories_id
LEFT JOIN specials s ON p.products_id = s.products_id
WHERE c.categories_status = 1
AND cs.categories_storename = 'name.co.uk'
AND products_status = '1'
AND c.categories_id = p2c.categories_id
AND pd.language_id = '4'
AND p2c.categories_id = '<category id number goes here>'
LIMIT 4

[Naughty]

Originally Posted by k0nr4d

SELECT cs.categories_storename,cs.categories_id,c.categories_id,c.parent_id,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, products_to_categories p2c
LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
LEFT JOIN products_description pd ON p.products_id = pd.products_id
LEFT JOIN categories_stores cs ON c.parent_id = cs.categories_id
LEFT JOIN specials s ON p.products_id = s.products_id
WHERE c.categories_status = 1
AND cs.categories_storename = 'name.co.uk'
AND products_status = '1'
AND c.categories_id = p2c.categories_id
AND pd.language_id = '4'
AND p2c.categories_id = '<category id number goes here>'
LIMIT 4

Thanks, but i need more help then ;)
You added just this right??
AND p2c.categories_id = '<category id number goes here>'

Is there a way to get this cid number in this same query?
As you could see, this is also in the categories_stores table.

Do we need a small query first to get these category ID's, or is that double-up?

[sysk]

uh... You want all products/categories from store "name.co.uk" ??

Then you will need to use IN()

[...]
AND p2c.categories_id IN(
SELECT cs.categories_id FROM categories_stores cs WHERE cs.categories_storename = 'name.co.uk'
)
[...]

[gigapornlist]

Hi,

maybe because your using left join which is an outer join which means it includes record that doesn't have a partner or related field including NULL. Better use inner join. I think that is what you expect.

[Naughty]

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;)

  $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:

...
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;)

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

...
AND cs.categories_storename = '%" . GLOBAL_CURRENT_STORE_NAME . "%'
...

[Naughty]

I already got that last remark fixed by putting it in an if-then clause