Zen Cart Product Tags (and Tag Cloud): Support Thread

[lat9]

Here is the myDEBUG

[28-Dec-2019 14:55:53 UTC] Request URI: /index.php?main_page=advanced_search_result&keyword=IntelliMouse&search_in_description=1&categories_id=1&inc_subcat=1&manufacturers_id=&pfrom=&pto=&dfrom=&dto=, IP address: 
#1  trigger_error() called at [/.../..../public_html/includes/classes/db/mysql/query_factory.php:171]
#2  queryFactory->show_error() called at [/...../....../public_html/includes/classes/db/mysql/query_factory.php:143]
#3  queryFactory->set_error() called at [/...../...../public_html/includes/classes/db/mysql/query_factory.php:270]
#4  queryFactory->Execute() called at [/....../....../public_html/includes/classes/split_page_results.php:78]
#5  splitPageResults->__construct() called at [/...../....../public_html/includes/modules/pages/advanced_search_result/header_php.php:476]
#6  require(/...../...../public_html/includes/modules/pages/advanced_search_result/header_php.php) called at [/....../....../public_html/index.php:36]
--> PHP Fatal error: 1305:FUNCTION .................17OR does not exist :: select count(distinct p.products_id) as total  FROM (zen_products p              LEFT JOIN zen_manufacturers m              USING(manufacturers_id), zen_products_description pd, zen_categories c, zen_tags t,  zen_products_to_categories p2c )              LEFT JOIN zen_meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 LEFT JOIN zen_tags_to_products t2p ON t2p.tag_mapping_id = p.products_id  WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND p2c.products_id = p.products_id                     AND p2c.products_id = pd.products_id                     AND (p2c.categories_id = 1 OR p2c.categories_id in ( 4, 5, 6, 7, 8, 9, 16, 17OR ( t.tag_id = t2p.tag_id AND t.languages_id = 1 AND t.tag_name LIKE '%intellimouse%') )) AND ((pd.products_name LIKE '%intellimouse%'                                          OR p.products_model                                          LIKE '%intellimouse%'                                          OR m.manufacturers_name                                          LIKE '%intellimouse%' OR (mtpd.metatags_keywords                         LIKE '%intellimouse%'                         AND mtpd.metatags_keywords !='') OR (mtpd.metatags_description                         LIKE '%intellimouse%'                         AND mtpd.metatags_description !='') OR pd.products_description                           LIKE '%intellimouse%') OR ( t.tag_id = t2p.tag_id AND t.languages_id = 1 AND t.tag_name LIKE '%intellimouse%') )) ==> (as called by) /............/............../public_html/includes/classes/split_page_results.php on line 78 <== in /......../............./public_html/includes/classes/db/mysql/query_factory.php on line 171.

Thanks, that's a big help. I should have a correction available 'soon'.

[lat9]

Thanks, that's a big help. I should have a correction available 'soon'.

... and 'soon' is now. I've created an issue on the plugin's GitHub to track the change. You can edit your copy of /includes/classes/observers/auto.zen_tags_search.php, replacing the following 'case' section to correct the issue:

            case 'NOTIFY_SEARCH_WHERE_STRING':
                global $keywords, $where_str;
                if ($this->tID !== false) {
                    $p2c_str = 'p2c.categories_id = c.categories_id';
                    $p2c_pos = strpos($where_str, $p2c_str);
                    $GLOBALS['where_str'] = substr($GLOBALS['where_str'], 0, $p2c_pos + strlen($p2c_str)) . " AND t2p.tag_id = {$this->tID} )";
                } else {
                    if (!empty($GLOBALS['keywords']) && isset($_GET['keyword']) && zen_parse_search_string(stripslashes($_GET['keyword']), $search_keywords)) {
                        $tags_where = 'AND ((t.tag_id = t2p.tag_id AND (';
                        foreach ($search_keywords as $current_keyword) {
                            switch ($current_keyword) {
                                case '(':
                                case ')':
                                case 'and':
                                case 'or':
                                    $tags_where .= " $current_keyword ";
                                    break;
                    
                                default:
                                    $tags_where .= "t.tag_name LIKE '%:keywords%'";
                                    $tags_where = $GLOBALS['db']->bindVars($tags_where, ':keywords', $current_keyword, 'noquotestring');
                                    break;
                            }
                        }
                        $GLOBALS['where_str'] = str_replace('AND ((', $tags_where . ')) OR (', $GLOBALS['where_str']);
                    }
                } 
                break;

[Ingar]

Hi there. Yep this time I got no warning but still there is no "mouse" on the site neither the "pakkard" I am afraid, while on the other these items are on the search results list.

[Ingar]

No while you search mouse in mice there is no warning and no mouse,


but when you try to search it in hardware you receive the warning Funny

[Ingar]

Here is the whole file edited. I guess might have made a mistake somewhere:

<?php
// -----
// Part of the "Zen Tags" plugin for Zen Cart v1.5.6 (and later)
// Copyright (C) 2018-2019, Vinos de Frutas Tropicales (lat9)
// @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0
//
class zcObserverZenTagsSearch extends base 
{
    public function __construct() 
    {
        if (defined('ZEN_TAGS_ENABLE') && ZEN_TAGS_ENABLE == 'true') {
            $this->tID = (isset($_GET['tID']) && ((int)$_GET['tID']) > 0) ? ((int)$_GET['tID']) : false;
            $this->order_by = '';
            $this->search_tags = (defined('ZEN_TAGS_SEARCH_ALWAYS') && ZEN_TAGS_SEARCH_ALWAYS == 'true');
            if ($this->tID !== false || $this->search_tags) {
                $this->attach(
                    $this, 
                    array(
                        'NOTIFY_SEARCH_ORDERBY_STRING',
                        'NOTIFY_SEARCH_FROM_STRING',
                        'NOTIFY_SEARCH_WHERE_STRING',
                        'NOTIFY_SEARCH_SELECT_STRING'
                    )
                );
            }
        }
    }

    public function update(&$class, $eventID) 
    {
        switch ($eventID) {
            case 'NOTIFY_SEARCH_SELECT_STRING':
                break;
                
            // -----
            //
            case 'NOTIFY_SEARCH_FROM_STRING':
                if ($this->tID === false) {
                    $GLOBALS['from_str'] = $this->insertAfter($GLOBALS['from_str'], ' c,', ' ' . TABLE_TAGS . ' t, ');
                }
                $GLOBALS['from_str'] .= PHP_EOL . "LEFT JOIN " . TABLE_TAGS_TO_PRODUCTS . ' t2p ON t2p.tag_mapping_id = p.products_id ';
                break;
                
            case 'NOTIFY_SEARCH_WHERE_STRING':
                global $keywords, $where_str;
                if ($this->tID !== false) {
                    $p2c_str = 'p2c.categories_id = c.categories_id';
                    $p2c_pos = strpos($where_str, $p2c_str);
                    $GLOBALS['where_str'] = substr($GLOBALS['where_str'], 0, $p2c_pos + strlen($p2c_str)) . " AND t2p.tag_id = {$this->tID} )";
                } else {
                    if (!empty($GLOBALS['keywords']) && isset($_GET['keyword']) && zen_parse_search_string(stripslashes($_GET['keyword']), $search_keywords)) {
                        $tags_where = 'AND ((t.tag_id = t2p.tag_id AND (';
                        foreach ($search_keywords as $current_keyword) {
                            switch ($current_keyword) {
                                case '(':
                                case ')':
                                case 'and':
                                case 'or':
                                    $tags_where .= " $current_keyword ";
                                    break;
                    
                                default:
                                    $tags_where .= "t.tag_name LIKE '%:keywords%'";
                                    $tags_where = $GLOBALS['db']->bindVars($tags_where, ':keywords', $current_keyword, 'noquotestring');
                                    break;
                            }
                        }
                        $GLOBALS['where_str'] = str_replace('AND ((', $tags_where . ')) OR (', $GLOBALS['where_str']);
                    }
                } 
                break;
                
            case 'NOTIFY_SEARCH_ORDERBY_STRING':
                // -----
                // If this search was initiated by the "Tag Cloud" sidebox, the search is modified to search **only**
                // for products matching the submitted 'tag-id'.  Modify the 'keywords', displayed on the advanced_search_results
                // page to indicate that the search was for the tag-name submitted.
                //
                if ($this->tID !== false) {
                    $GLOBALS['keywords'] = sprintf(ZEN_TAG_SEARCH_IS_TAG, (string)$_GET['keyword']);
                }
                $GLOBALS['listing_sql'] = str_ireplace('order by', 'order by' . $this->order_by, $GLOBALS['listing_sql']);
                break; 

            default:
                break;
        }
    }
    
    protected function insertAfter($the_string, $insert_after, $insert_string)
    {
        $insert_pos = strpos($the_string, $insert_after);
        if ($insert_pos === false) {
            trigger_error("Missing 'anchor' string ($insert_after) from 'base' string ($the_string); the search does not include any tags.", E_USER_WARNING);
        } else {
            $the_string = substr_replace($the_string, $insert_string, $insert_pos + strlen($insert_after), 0);
        }
        return $the_string;
    }
}

[lat9]

Yep, you dropped a couple of bits. Try this instead:

<?php
// -----
// Part of the "Zen Tags" plugin for Zen Cart v1.5.6 (and later)
// Copyright (C) 2018-2019, Vinos de Frutas Tropicales (lat9)
// @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0
//
class zcObserverZenTagsSearch extends base 
{
    public function __construct() 
    {
        if (defined('ZEN_TAGS_ENABLE') && ZEN_TAGS_ENABLE == 'true') {
            $this->tID = (isset($_GET['tID']) && ((int)$_GET['tID']) > 0) ? ((int)$_GET['tID']) : false;
            $this->order_by = '';
            if ($this->tID !== false || (defined('ZEN_TAGS_SEARCH_ALWAYS') && ZEN_TAGS_SEARCH_ALWAYS == 'true')) {
                $this->attach(
                    $this, 
                    array(
                        'NOTIFY_SEARCH_ORDERBY_STRING',
                        'NOTIFY_SEARCH_FROM_STRING',
                        'NOTIFY_SEARCH_WHERE_STRING',
                        'NOTIFY_SEARCH_SELECT_STRING'
                    )
                );
            }
        }
    }

    public function update(&$class, $eventID) 
    {
        switch ($eventID) {
            case 'NOTIFY_SEARCH_SELECT_STRING':
                if (!empty($GLOBALS['keywords']) && !empty($_GET['keyword']) && zen_parse_search_string(stripslashes($_GET['keyword']), $search_keywords)) {
                    $in_tag_select = '';
                    foreach ($search_keywords as $current_keyword) {
                        switch ($current_keyword) {
                            case '(':
                            case ')':
                            case 'and':
                            case 'or':      //- Fall-through from above ...
                                $in_tag_select .= " $current_keyword ";
                                break;

                            default:
                                $in_tag_select .= "t.tag_name LIKE '%:keywords%'";
                                $in_tag_select = $GLOBALS['db']->bindVars($in_tag_select, ':keywords', $current_keyword, 'noquotestring');
                                break;
                        }
                    }
                    $GLOBALS['select_str'] .= ", IF (t2p.tag_mapping_id = p.products_id and t2p.tag_id = t.tag_id AND ($in_tag_select), 1, 0) AS in_tag ";
                    $this->order_by = ' in_tag DESC,';
                }
                break;
                
            case 'NOTIFY_SEARCH_FROM_STRING':
                $GLOBALS['from_str'] = $this->insertAfter($GLOBALS['from_str'], ' c,', ' ' . TABLE_TAGS . ' t, ');
                $GLOBALS['from_str'] .= PHP_EOL . "LEFT JOIN " . TABLE_TAGS_TO_PRODUCTS . ' t2p ON t2p.tag_mapping_id = p.products_id ';
                break;
                
            case 'NOTIFY_SEARCH_WHERE_STRING':
                global $keywords, $where_str;
                if ($this->tID !== false) {
                    $p2c_str = 'p2c.categories_id = c.categories_id';
                    $p2c_pos = strpos($where_str, $p2c_str);
                    $GLOBALS['where_str'] = substr($GLOBALS['where_str'], 0, $p2c_pos + strlen($p2c_str)) . " AND t2p.tag_id = {$this->tID} )";
                } else {
                    if (!empty($GLOBALS['keywords']) && isset($_GET['keyword']) && zen_parse_search_string(stripslashes($_GET['keyword']), $search_keywords)) {
                        $tags_where = 'AND ((t.tag_id = t2p.tag_id AND (';
                        foreach ($search_keywords as $current_keyword) {
                            switch ($current_keyword) {
                                case '(':
                                case ')':
                                case 'and':
                                case 'or':
                                    $tags_where .= " $current_keyword ";
                                    break;
                    
                                default:
                                    $tags_where .= "t.tag_name LIKE '%:keywords%'";
                                    $tags_where = $GLOBALS['db']->bindVars($tags_where, ':keywords', $current_keyword, 'noquotestring');
                                    break;
                            }
                        }
                        $GLOBALS['where_str'] = str_replace('AND ((', $tags_where . ')) OR (', $GLOBALS['where_str']);
                    }
                } 
                break;
                
            case 'NOTIFY_SEARCH_ORDERBY_STRING':
                if ($this->tID !== false) {
                    $GLOBALS['keywords'] = "tag ({$GLOBALS['keywords']})";
                }
                $GLOBALS['listing_sql'] = str_ireplace('order by', 'order by' . $this->order_by, $GLOBALS['listing_sql']);
                break; 

            default:
                break;
        }
    }
    
    protected function insertAfter($the_string, $insert_after, $insert_string)
    {
        $insert_pos = strpos($the_string, $insert_after);
        if ($insert_pos === false) {
            trigger_error("Missing 'anchor' string ($insert_after) from 'base' string ($the_string); the search does not include any tags.", E_USER_WARNING);
        } else {
            $the_string = substr_replace($the_string, $insert_string, $insert_pos + strlen($insert_after), 0);
        }
        return $the_string;
    }
}

[Ingar]

No, unfortunately "There is no product that matches the search criteria" as yet.

[lat9]

@Ingar, please check your Private Messages (PMs).

[lat9]

Thanks to @Ingar for access to his site so that I could "see" what was going on. I've just submitted v1.0.1 of the plugin to the Zen Cart moderators for review and will post back here when it's available for download.

This release contains changes associated with the following GitHub issues:

#5: Advanced Search: Correct MySQL error when sub-categories included.
#6: Advanced Search: Correct "no search results returned" when no tags are defined.
#7: Don't display Tag Cloud sidebox if no tags are defined.

[Ingar]

Thank you @lat9 Everything works as it supposed to. Great contribution!