Sort order :: specials products

[xergos055]

I put pictures in the database tables from which I consider to be extracted, in order to display the specials products per category. Hope you can help me solve this problem !

[xergos055]

I try in this way but nothing change, all the specials products are displayed at oance

 $specials_query_raw = "SELECT p.products_id, p.products_image, pd.products_name, pd.products_description, p.master_categories_id,      cd.categories_description, c.categories_id
                         FROM (" . TABLE_PRODUCTS . " p
                         LEFT JOIN " . TABLE_SPECIALS . " s on p.products_id = s.products_id
                         LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id 
                         LEFT JOIN " . TABLE_CATEGORIES_DESCRIPTION . " cd on cd.categories_id = 120 
                         LEFT JOIN " . TABLE_CATEGORIES . " c on c.categories_id = 120  )
                         WHERE p.products_id = s.products_id and p.products_id = pd.products_id and p.products_status = '1'
                         AND s.status = 1 AND cd.categories_id = '120' AND c.categories_id = '120'
                         
                         AND pd.language_id = :languagesID
                         ORDER BY s.specials_date_added DESC"; 

Nobody ken help mee, please !

[Ajeh]

Let's step back a minute ...

Where are you when you want the Specials to show for a specific categories_id ...

Are you trying to *always* limit it to 120 or is that an example ...

Be very specific in the details about what you are trying to do ...

[xergos055]

I'm on the specials page. I have a drop down menu with the products categoris (not subcategoris).

If i select the categori Adidas with id=21, i want to view all the specials products from that categori

[Ajeh]

What variable are you storing the categories_id to when the dropdown is used?

How do you have the dropdown written?

[xergos055]

What variable are you storing the categories_id to when the dropdown is used?

How do you have the dropdown written

Yes i have the categories_id in variable

<b>Selectati categoria </b><br>
<select name="categorie" class="input" style="width=495;" id="p0" onChange="this.form.submit()" >
<option value="Select categori" SELECTED></option>
<OPTION VALUE="120">Categorie1
<OPTION VALUE="121">Categorie2
<OPTION VALUE="88">Categorie3
<OPTION VALUE="118">Categorie4
</select>
</FORM> 

[xergos055]

I resolve in that way:
$valoare_id = "id from master category";
ex: $valoare_id = "21";

 $specials_query_raw = "SELECT s.products_id, d.products_name,pp.products_image, s.specials_new_products_price, s.status, p.categories_id, c.categories_name, c1.parent_id
                       FROM (
                       specials AS s
                       LEFT JOIN products_to_categories AS p ON s.products_id = p.products_id
                       LEFT JOIN products AS pp ON s.products_id = pp.products_id
                       LEFT JOIN categories_description AS c ON p.categories_id = c.categories_id
                       LEFT JOIN products_description AS d ON s.products_id = d.products_id
                       LEFT JOIN categories AS c1 ON p.categories_id = c1.categories_id
                            )
                       WHERE s.status =1 and c1.parent_id = $valoare_id 
                       ORDER BY s.specials_date_added DESC"; 

but i have a problem.

If i have specials products in to an category without subcategory, i mean if i have specials directly in the main category that products is not displayed. Is any way to resolv this problem ?

[Ajeh]

And if you use the categories_id in the products_to_categories table such as:
WHERE s.status =1 and p.categories_id = $valoare_id

[xergos055]

If I use in that way is work for the categori without subcategory , but the specials products from categori with subcategori is not displayed.

[Ajeh]

You may need to make some customization to use one method for the SELECT when in a Category with subcategories and one method when in a Category with Products ...