GoFuckYourself.com - Adult Webmaster Forum - Need php/mysql query help, inside!

- Fucking Around & Business Discussion (https://gfy.com/forumdisplay.php?f=26)

- - Need php/mysql query help, inside! (https://gfy.com/showthread.php?t=836602)

Need php/mysql query help, inside!

I have a few tables (osc)

Code:

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

Code:

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

Extra info:

This is inside the table cs

Code:

categories_stores_id  categories_id  categories_storename  

      1 121 name.co.uk

Table categories:

Code:

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

Code:

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

Quote:

Originally Posted by k0nr4d (Post 14361327)

Code:

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?

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'
)
[...]

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.

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 . "%'

...

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