Quote Originally Posted by mc12345678 View Post
Not in front of a computer, but unless every product in your store is also tracked by attributes, my guess is the product not stocked by attributes is no longer locateable by search.

For one thing the SBA table needs to be left joined on the query. But then if a search is performed on a sba tracked item on a field not in the sba table for a product tracked by sba, then one row for each variant of the product will be returned. Pair this with the second query returns the customid (when not doing a search) and if it does so for some purpose of display then would want to do the same thing in the search query.

My thought though and I still need to run the query through a database test, would be to either apply distinct to the first (search) query, left join the sba table on products_id, apply a like instead of = on the search, and remove pas.customid from the second query, but duplicate the above to it as well...

Otherwise, might add an additional query ahead of these two such that the sba table is searched, when it has a positve result of a product_id, then feed that into the search queryresult as one of the where comparisons.

Just a thought...
So my eyes certainly deceived me... The pas.customid was in both queries...

Here for your specific situation is how I would rewrite this query... Mind you the products_upc field is not standard to ZC, so if/when this modification is incorporated, that field/others are likely to not be included with SBA out-of-the box.

Code:
$products_query_raw = ("select DISTINCT p.products_type, p.products_id, pd.products_name, p.products_quantity,
                                       p.products_image, p.products_price, p.products_date_added,
                                       p.products_last_modified, p.products_date_available,
                                       p.products_status, p2c.categories_id,
                                       p.products_model,
                                       p.products_quantity_order_min,  p.products_quantity_order_units, p.products_priced_by_attribute,
                                       p.product_is_free,  p.product_is_call, p.products_quantity_mixed,  p.product_is_always_free_shipping,
p.products_upc, pas.customid,
                                       p.products_quantity_order_max, p.products_sort_order,
                                       p.master_categories_id
                                from " . TABLE_PRODUCTS . " p LEFT JOIN "
                                       . TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . " pas on (pas.products_id = p.products_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd, "
                                       . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, "
                                       . TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . " pas
                                where p.products_id = pd.products_id
and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
                                and (p.products_id = p2c.products_id
                                and p.master_categories_id = p2c.categories_id)
                                and (
                                pd.products_name like '%" . zen_db_input($_GET['search']) . "%'
                                or pd.products_description like '%" . zen_db_input($_GET['search']) . "%'
                                or p.products_id = '%" . zen_db_input($_GET['search']) . "%'
                                or p.products_upc = '%" . zen_db_input($_GET['search']) . "%'
                                or pas.customid like '%" . zen_db_input($_GET['search']) . "%'
                                or p.products_model = '%" . zen_db_input($_GET['search']) . "%')" .
                                $order_by);

    } else {
      $products_query_raw = ("select p.products_type, p.products_id, pd.products_name, p.products_quantity,
                                       p.products_image, p.products_price, p.products_date_added,
                                       p.products_last_modified, p.products_date_available,
                                       p.products_status, p.products_model,
                                       p.products_quantity_order_min,  p.products_quantity_order_units, p.products_priced_by_attribute,
                                       p.product_is_free,  p.product_is_call, p.products_quantity_mixed,  p.product_is_always_free_shipping,
p.products_upc, pas.customid,
                                       p.products_quantity_order_max, p.products_sort_order
                                from " . TABLE_PRODUCTS . " p LEFT JOIN "
                                       . TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . " pas on (pas.products_id = p.products_id), " .  TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . "  p2c, " . TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . " pas
                                where p.products_id = pd.products_id
                               and pas.products_id = p.products_id
                                and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
                                and p.products_id = p2c.products_id
                                and p2c.categories_id = '" . (int)$current_category_id . "'" .
                                $order_by);
    }
The above will produce results that will list all variants of a product when either listing the products in a category or when searching on a value that is not in the customid of the sba variant(s). The returned data has an additional field (pas.customid) that if not used should be removed to restore the "normal" returned dataset but searchable by the customid. If the returned pas.customid is kept in the returned data, then suggest changing up the row's display action as the normal product_info page has no direct relatonship with the tracked SBA quantities.

Btw, I too had some trouble using the search function with the original posted code, but found there were some spaces whether intially entered or some odd posting fluke known to occasionally happen I do not know, but the above worked in a sql query.