Sort order :: specials products

Hi !

Is some one to show me how ken i put on specials page an drop down menu with category of my products page, and if i select one category to show mee the products with special price (specials products from that category) ?

I working on this option a week unsuccessfully.

Please help someone!

How ken i put in this conditions to show me the specials products from the categori with id=120 ?

$specials_query_raw = "SELECT p.products_id, p.products_image, pd.products_name, pd.products_description, p.master_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 )
WHERE p.products_id = s.products_id and p.products_id = pd.products_id and p.products_status = '1'
AND s.status = 1
AND pd.language_id = :languagesID

                   ORDER BY s.specials_date_added DESC";

Please help !

Nobody ?:(

I try to show the special products from category with id=120, and i using that interogation to mysql:

$specials_query_raw = "SELECT p.products_id, p.products_image, pd.products_name, pd.products_description, p.master_categories_id, cd.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 )
WHERE p.products_id = s.products_id and p.products_id = pd.products_id and p.products_status = '1' and cd.categories_id = 120
AND s.status = 1
AND cd.categories_id = 120
AND pd.language_id = :languagesID
ORDER BY s.specials_date_added DESC";

The results the same "IN THE SPECIAL PAGE I HAVE ALL TLE PRODUCTS WITH SPECIALS PRICE" is not show the products with SPECIAL PRICE from category with id 120

Anyone ken help me please !

To limit the Specials to categories_id on that select, use:
AND p.master_categories_id = 120

vs the:
AND cd.categories_id = 120

This forces the Master Category of the products table to be 120 ...

I'change :

$specials_query_raw = "SELECT p.products_id, p.products_image, pd.products_name, pd.products_description, p.master_categories_id, cd.categories_id
FROM (" . TABLE_PRODUCTS . " p

                     LEFT JOIN " . TABLE_CATEGORIES_DESCRIPTION . " cd on cd.categories_id = 120 and cd.categories_name = 'Aparatori' 
                     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  )
                     WHERE p.products_id = s.products_id and p.products_id = pd.products_id and p.products_status = '1' AND p.master_categories_id = 120
                     AND s.status = 1
                     AND p.master_categories_id = 120
                     AND pd.language_id = :languagesID
                     ORDER BY s.specials_date_added DESC";

and now I no longer show any product in specials page .

Check the Products that you think are on Special and in categories_id 120 and see if they also are using the master_categories_id 120 in the products table ...

Yes, and i have:

index.php?main_page=product_info&cPath=120&products_id=1923

and the product with id=1923 is on specials

In database i have: on table products: master_categories_id=0 (all are 0)

Try this ... you have the code in there twice at least:

$specials_query_raw = "SELECT p.products_id, p.products_image, pd.products_name, pd.products_description, p.master_categories_id, cd.categories_id
FROM (" . TABLE_PRODUCTS . " p

LEFT JOIN " . TABLE_CATEGORIES_DESCRIPTION . " cd on cd.categories_id = 120 and cd.categories_name = 'Aparatori'
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 )
WHERE p.products_id = s.products_id and p.products_id = pd.products_id and p.products_status = '1'
AND s.status = 1
AND p.master_categories_id = 120
AND pd.language_id = :languagesID
ORDER BY s.specials_date_added DESC";

Nothing ...

I think the interogation in database is wrong..

I'm not sure if the code i made for interogation is ok.
All i want is that:

I'will put in the specials page the links with products categorys.
If the visitors want to see if in category with the name:
ex. category 1 is products with spacials price, click on that category.

I dont want in the specials page the specials products to be displayed all mixed.

The original code from tpl_specials_default.php is that:

$specials_query_raw = "SELECT p.products_id, p.products_image, pd.products_name, pd.products_description, p.master_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 )
WHERE p.products_id = s.products_id and p.products_id = pd.products_id and p.products_status = '1'
AND s.status = 1
AND pd.language_id = :languagesID
ORDER BY s.specials_date_added DESC";

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 !

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 !

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

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

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

How do you have the dropdown written?

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>

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 ?

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

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

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