advanced search by type ahead

[ReneH4]

Excellent fix @radhavallabh !

The old code was:

Code: Select all

$query_exploded_new = substr($query_exploded_new, 0, -6);

So what is the role of the parameter here that makes the difference?

[radhavallabh]

Excellent fix @radhavallabh !

The old code was:

Code: Select all

$query_exploded_new = substr($query_exploded_new, 0, -6);

So what is the role of the parameter here that makes the difference?

It basically reads and removes all the the 6 letters from the end of the string returned. This was for some reason hindering the procedure of displaying the word properly, when I tried 7 it removed the latter part of product name rather than altering the highlighted word only, but when I tried 5 it worked like a charm and displayed the complete word and the remaining part of the product name perfectly.

Regds./
radhavallabh

[Pierre_P]

Somewhere there exist an error, i could not replicate the error but is showing up a lot in error logs.
After dong some searching in files this type ahead (excellent module) is likely the culprit.

DB: [1064] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND cd.language_id = '1'' at line 1 from <SELECT distinct(c.categories_id), cd.categories_name, c.parent_id FROM categories_description cd, categories c WHERE cd.categories_id = c.categories_id AND AND cd.language_id = '1'>

If anyone knows what is causing this error please post
Thx

Code: Select all

<?php
/*
  $Id$ version 1.0 for Phoenix

  osCommerce, Open Source E-Commerce Solutions
  http://www.oscommerce.com

  Copyright (c) 2020 osCommerce

  Released under the GNU General Public License
*/

chdir('../../');

require('includes/application_top.php');
include('includes/languages/' . $_SESSION['language'] . '/modules/store/st_store_search.php');

if (isset($_POST['query'])) {
    $query = Text::input($_POST['query']);
} else {
    //nothing to do
    $query = "";
    exit();
}

//  if (strlen($query) < 3) { exit(); }

//here we can replace certain phrases that people may search for that are wrong, i have left my examples below.
//for example i have people add food or foods onto the end of search phrases, but food is rarely used in product names.
//or for if people add spaces where there shouldnt be or remove spaces when there should be

if ($language == 'english') {
    $query = str_replace('/', '', $query); // avoid highlight crash
    $query = str_replace(' food', '', $query);
    $query = str_replace(' foods', '', $query);
}

if ($language == 'french') {
    $query = str_replace('/', '', $query); // avoid highlight crash
}

//Explode This Query
$query_exploded = array();
$query_exploded = explode(' ', $query);
$query_exploded = array_unique($query_exploded);


//if a characters are only "b" or "B" do nothing!
if (($key = array_search("b", $query_exploded)) !== false) { unset($query_exploded[$key]); }
if (($key = array_search("B", $query_exploded)) !== false) { unset($query_exploded[$key]); }

//for highlight rule
arsort($query_exploded);
$query_exploded_new = '';

foreach ($query_exploded as $highlight) {
    //<b> is not search engine sensitive
    $query_exploded_new .= '<b>' . $highlight . '</b>' . PHP_EOL;
}
$query_exploded_new = substr($query_exploded_new, 0, -5);

$query_exploded_highlight = explode(PHP_EOL, $query_exploded_new);


//Generate Like Statement for Each Word To Find Categories, Second Level, That Match
$like_statement_category = '';

foreach ($query_exploded as $category) {
    //Prevent SQL Injection Attempts
    $category = str_replace(array("'", ";", "*", "(", ")"), '', $category);

    // categories_name or categories_htc_title_tag_alt search
    $like_statement_category .= "(cd." . STORE_STORE_SEARCH_CATEGORY_NAME_FIELD . " LIKE '%" . $GLOBALS['db']->escape($category) . "%') AND ";
}

//Remove The Last AND
$like_statement_category = substr($like_statement_category, 0, -4);

//Select categories, that are second level, and that match our query
$sqlquery = $GLOBALS['db']->query("SELECT distinct(c.categories_id), cd." . STORE_STORE_SEARCH_CATEGORY_NAME_FIELD . ", c.parent_id FROM categories_description cd, categories c WHERE cd.categories_id = c.categories_id AND " . $like_statement_category . " AND cd.language_id = '" . (int)$languages_id . "'");

if (mysqli_num_rows($sqlquery) && STORE_STORE_SEARCH_MAX_CATEGORY > 0) {
    $c = 0;
    while ($row = $sqlquery->fetch_assoc()) {
        $c++;
        if ($c > STORE_STORE_SEARCH_MAX_CATEGORY) {
            $array[] = array('icon'  => '<span style="float:left; margin-right:10px;"><i class="fa fa-exclamation-triangle fa-2x"></i></span>',
                'title' => sprintf(STORE_STORE_SEARCH_MORE_CATEGORY, STORE_STORE_SEARCH_MAX_CATEGORY, mysqli_num_rows($sqlquery)),
                'href'  => null,
                'price' => null);
            break;
        } else {
            $url_title = ucwords(strtolower($row[STORE_STORE_SEARCH_CATEGORY_NAME_FIELD]));

            //highlight
            $url_title = str_ireplace($query_exploded, $query_exploded_highlight, $url_title);

//		$array[] = array('icon'  => "sitemap",
            $array[] = array('icon' => '<span style="float:left; margin-right:10px;"><i class="fa fa-sitemap fa-2x"></i></span>',
                'title' => $url_title,
                'href'  => $GLOBALS['Linker']->build('index.php', 'cPath=' . $row['categories_id']),
                'price' => null);
        }
    }
}
//We Have All Suggested Categories


//Find Suggested Products
$like_statement_product = '';

foreach ($query_exploded as $product) {
    //Prevent SQL Injection Attempts
    $product = str_replace(array("'", ";", "*", "(", ")"), '', $product);

    //Set Keywords Search Field
    //Add products_gtin + manufacturers_name
    $like_statement_product .= "(pd.products_name LIKE '%" . $GLOBALS['db']->escape($product) . "%' 
	" . (STORE_STORE_SEARCH_PRODUCT_MODEL == 'True' ? "OR p.products_model LIKE '%" . $GLOBALS['db']->escape($product) . "%'" : "") . " 
	" . (STORE_STORE_SEARCH_PRODUCT_GTIN == 'True' ? "OR p.products_gtin LIKE '%" . $GLOBALS['db']->escape($product) . "%'" : "") . " 
	" . (STORE_STORE_SEARCH_PRODUCT_MANUFACTURER == 'True' ? "OR m.manufacturers_name LIKE '%" . $GLOBALS['db']->escape($product) . "%'" : "") . " 
	" . (STORE_STORE_SEARCH_PRODUCT_KEYWORDS_FIELD != 'None' ? "OR pd." . STORE_STORE_SEARCH_PRODUCT_KEYWORDS_FIELD . " LIKE '%" . $GLOBALS['db']->escape($product) . "%'" : "") . "
	) AND ";
}

//Remove the Last And
$like_statement_product = substr($like_statement_product, 0, -4);

//Add products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id
//Add p.products_image + p.products_quantity
$sqlquery = $GLOBALS['db']->query("SELECT distinct(p.products_id), pd.products_name, p.products_image, p.products_quantity, p.products_price, p.products_tax_class_id FROM products_description pd, products p " . (STORE_STORE_SEARCH_PRODUCT_MANUFACTURER == 'True' ? "left join manufacturers m on p.manufacturers_id = m.manufacturers_id" : "") . " WHERE " . $like_statement_product . " AND pd.products_id = p.products_id and pd.language_id = '" . (int)$_SESSION['languages_id'] . "' and p.products_status = 1");

$p = 0; //Set Row
if (mysqli_num_rows($sqlquery) && STORE_STORE_SEARCH_MAX_PRODUCT > 0) {
    while ($row = $sqlquery->fetch_assoc()) {
        $p++;
        $url_title = str_replace('’', '', $row['products_name']);

        //highlight
        $url_title = str_ireplace($query_exploded, $query_exploded_highlight, $url_title);

        if ($p > STORE_STORE_SEARCH_MAX_PRODUCT) {
//        $array[] = array('icon'  => "cart-plus",
            $array[] = array('icon'  => '<span style="float:left; margin-right:10px;"><i class="fa fa-plus-circle fa-2x"></i></span>',
                'title' => sprintf(STORE_STORE_SEARCH_MORE_PRODUCT, STORE_STORE_SEARCH_MAX_PRODUCT, mysqli_num_rows($sqlquery)),
                'href'  => $GLOBALS['Linker']->build('advanced_search_result.php', 'keywords=' . urlencode(str_replace(' ', ' ', $query))) . '&search_in_description=' . (STORE_STORE_SEARCH_FUNCTIONS == 'Descriptions' ? 1 : 0),
                'price' => null);
            break;
        } else {
            if ($new_price = product_by_id::build(Product::build_prid($row['products_id']))->get('specials_new_products_price')) {
                // tep_get_products_special_price($row['products_id'])) {
                $price = '<s>' . $currencies->display_price($row['products_price'], Tax::get_rate($row['products_tax_class_id'])) . '</s> <span class="productSpecialPriceStoreSearch">' . $currencies->display_price($new_price, Tax::get_rate($row['products_tax_class_id'])) . '</span>';
            } else {
                $price = $currencies->display_price($row['products_price'], Tax::get_rate($row['products_tax_class_id']));
            }

            // image or icon for products
            if (STORE_STORE_SEARCH_IMAGE_OR_ICON == 'Image') {

                if ($row['products_image'] != '') {
                    $product_image = $row['products_image'];
                } else {
                    $product_image = 'picture_o_trans.png';
                }

                $image_product = '<span class="d-block d-sm-none" style="float:left; margin-right:10px;"><img src="images/' . $product_image . '" width="' . STORE_STORE_SEARCH_IMAGE_WIDTH_XS . '" height="auto"></span>';
                $image_product .= '<span class="d-none d-sm-block d-md-none" style="float:left; margin-right:10px;"><img src="images/' . $product_image . '" width="' . STORE_STORE_SEARCH_IMAGE_WIDTH_SM . '" height="auto"></span>';
                $image_product .= '<span class="d-none d-md-block d-lg-none" style="float:left; margin-right:10px;"><img src="images/' . $product_image . '" width="' . STORE_STORE_SEARCH_IMAGE_WIDTH_MD . '" height="auto"></span>';
                $image_product .= '<span class="d-none d-lg-block d-xl-none" style="float:left; margin-right:10px;"><img src="images/' . $product_image . '" width="' . STORE_STORE_SEARCH_IMAGE_WIDTH_LG . '" height="auto"></span>';
                $image_product .= '<span class="d-none d-xl-block" style="float:left; margin-right:10px;"><img src="images/' . $product_image . '" width="' . STORE_STORE_SEARCH_IMAGE_WIDTH_XL . '" height="auto"></span>';

            } else {
                $image_product = '<span style="float:left; margin-right:10px;"><i class="fa fa-cart-plus fa-2x"></i></span>';
            }

//        $array[] = array('icon'  => "cart-plus",
            $array[] = array('icon'  => $image_product,
                'title' => $url_title,
                'href'  => $GLOBALS['Linker']->build('product_info.php', 'products_id=' . $row['products_id']),
                'price' => $price);
        }
    }
} else {
//    $array[] = array('icon'  => "wrench",
    $array[] = array('icon'  => '<span style="float:left; margin-right:10px;"><i class="fa fa-exclamation-triangle fa-2x"></i></span>',
        'title' => STORE_STORE_SEARCH_PRODUCT_NOT_FOUND,
        'href'  => $GLOBALS['Linker']->build('advanced_search.php', 'keywords=' . urlencode(str_replace(' ', ' ', $query))),
        'price' => null);
}

// build json
echo json_encode($array);

[Omar_one]

@Pierre_P
On this lines

Code: Select all

//Select categories, that are second level, and that match our query
$sqlquery = $GLOBALS['db']->query("SELECT distinct(c.categories_id), cd." . STORE_STORE_SEARCH_CATEGORY_NAME_FIELD . ", c.parent_id FROM categories_description cd, categories c WHERE cd.categories_id = c.categories_id AND " . $like_statement_category . " AND cd.language_id = '" . (int)$languages_id . "'");

Try to change

Code: Select all

. (int)$languages_id .

To this

Code: Select all

 . (int)$_SESSION['languages_id'] .

[14Steve14]

I have tried this addon on my 1.7.11 store and it works great but the final line where you should be able to click to see more results just clicks into a product in the search list. Because it now only shows a list of 10 lines, where the search list can be up to 50 lines long I am loosing customers finding what they want.

Any suggestions on how to get the last line where you have the big Plus sign plus the text 'View More Products' to work as it should.

[Pierre_P]

@Omar_one
Thanks, i have changed it

Looking at the double AND in ...WHERE cd.categories_id = c.categories_id AND AND

@14Steve14
I checked with my store, search gives list of 12 and + button returns 733 results example, clicking it goes to advance search results showing pages for all 733 results.
Would have been nice so that when the results was returned product filters to be shown at this stage or search within results for larger stores.

[ecartz]

Looking at the double AND in ...WHERE cd.categories_id = c.categories_id AND AND

Try changing

Code: Select all

if (isset($_POST['query'])) {
    $query = Text::input($_POST['query']);
} else {
    //nothing to do
    $query = "";
    exit();
}

to

Code: Select all

if (empty($_POST['query']) || (trim($query) === '')) {
  exit();
}

$query = Text::input($_POST['query']);

It's saying that $like_statement_category is empty.

[14Steve14]

@14Steve14
I checked with my store, search gives list of 12 and + button returns 733 results example, clicking it goes to advance search results showing pages for all 733 results.
Would have been nice so that when the results was returned product filters to be shown at this stage or search within results for larger stores.

Which version of the code are you using. No matter what I try the bottom line only opens a random product from the search results even though the link should redirect to advanced search. I have just tried a remove and reinstall and its exactly the same. Can you add a copy of your files here.

[zipurman]

Does anyone have this working on Phoenix? If so, PM me a link so I can have a look. I am thinking of writing a mod for this as I have a client wanting it.

[Omar_one]

Does anyone have this working on Phoenix? If so, PM me a link so I can have a look. I am thinking of writing a mod for this as I have a client wanting it.

On the first post on this typic there is a link ..
We had it on 1.0.8.16 , but it's not enabled now..but I can enable it, if you would like a look

Br
Omar