Need help with a query

[Guest]

2.3.4.1 CE and PHP 7.0

Is there a way to modify the following query so I only get results from a particular category or subcat ID number?

This query is pulling up all products in my site.  I only want it to pull items from, for example, category 25.  Or subcat 25_44_77

  $products = tep_db_query("select pd.products_name, p.products_id, p.products_model from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id <> " . (int)$_GET['pID'] . $where_str . " order by p.products_model");
    while($products_values = tep_db_fetch_array($products)) {
      echo "\n" . '<option name="' . $products_values['products_id'] . '" value="' . $products_values['products_id'] . '">' . $products_values['products_name'] . " (" . $products_values['products_model'] . ')</option>';
    }
    echo '</select>';

Thanks for any help!

- Andrea

 

[YePix]

Hi, what file is this?
If you want to query products from a specific category, you still need to include the categories and products_to_categories tables

[Guest]

Hi Peter,

I've been doing a rehab of an old contribution called Bundled Products for my site.  It contains a dropdown box in Admin where you normally create or edit existing items.  It adds a new field on the page where you add your products to a bundle, like this:

The dropdown box at the bottom was problematic, as I have around 6300 items in my site and it pulls up a menu of every single one of them.  I had difficulty when trying to find or select an item to add - the products were being displayed in random order with no ability to filter by category, so....that's why I asked the question.  In the end, I changed the query to sort by products name so at least it's alphabetical now and I'm all good!  

I knew what I asked would involve those category entries you mentioned in the query - I just have no experience reading or writing code so I hoped that someone might give me an example to start messing around with.  But, I think I'm fine with what I have in place now.  Thanks!

- Andrea

[Jack_mcs]

On ‎6‎/‎7‎/‎2019 at 4:36 PM, puggybelle said:

I only want it to pull items from, for example, category 25.

This might work

$products = tep_db_query("select pd.products_name, p.products_id, p.products_model 
from 
 products p left join 
 products_description pd on pd.products_id = p.products_id left join 
 products_to_categories p2c on p.products_id = p2c.products_id
where p2c.categories_id = 25 and pd.language_id = '" . (int)$languages_id . "' and p.products_id <> " . (int)$_GET['pID'] . $where_str . " order by p.products_model");

 

[Guest]

Thank You, @Jack_mcs!  That does work - if your items reside in the parent category.

What trips me up is trying to put something in the query that would display only items in a particular subcategory, where most of my products reside.

Entering where p2c.categories_id = 25 results in an empty dropdown box, because no products are in the parent cat of 25.

I was trying things like p2c.categories_id = 25_5 and I guess the code isn't fond of my underscore there.  It blows the page up a bit.

If you know how to query a subcat, I'd love to learn how.  For the time being, I'm just going to appreciate my alphabetical sort order of ten million products.

Honestly, I'm not complaining...it's WAY better than what I started with!

- Andrea

[Jack_mcs]

5 hours ago, puggybelle said:

What trips me up is trying to put something in the query that would display only items in a particular subcategory, where most of my products reside.

If this is something that doesn't change and the sub-category is in the format of A_B..._Z, like 25_5, you would just use the last number, 5 in this case, in the statement instead of the 25. If it will change based on what you click on, then you need to add code to get the last category ID from the given category string.