Help with search using "

[ROYW1000]

Hi

I have an ecommerce cart software and the search works fine if you enter for example: 23" LCD TV. The results will return.

However it also has a Search Suggest API as per code below but if you enter 23" LCD TV firstly the search suggest does not show any results nor do the actual results upon pressing search.

Is there anything I can change within the script below to allow it to work if it uses like 23" within its name. If I remove the " from the name it works fine.

The database is storing the " as " rather than ". There maybe other special characters that don't work as well so I would like to have some kine of array which we can set which ones to include.

Thanks in advance.

PHP Code:

<?php
    $internalID = "api";
    include("static/config.php");
    include("routines/dbAccess_".$databaseType.".php");
    include("routines/general.php");
    include("./routines/jCache.php");
    dbConnect($dbA);
    $cache = new jCache($jssCacheDir);
    $opts = grabAllOptionsFront();
    $optionsArray = $opts;
    $command = makeSafe(getFORM("c"));
    switch ($command) {
        case "searchsuggest":
            if (retrieveOption("searchSuggestOn") != "Y") {
                break;
            }
            $xSearch = makeSafe(getFORM("xSearch"));
            $xSearch2 = html_entity_decode($xSearch);
            $xSearch2 = stringNoAccent($xSearch2);

            $xSearch = str_replace("&amp;","&",$xSearch);
            $searchlength = strlen($xSearch);
            $xSearch2 = str_replace("&amp;","&",$xSearch2);
            $xSearch2 = htmlentities($xSearch2);

            if (retrieveOption("searchSuggestStart") == 0) {
                $searchbit = "%".$xSearch2."%";
            } else {
                $searchbit = $xSearch2."%";
            }
            $field = retrieveOption("searchSuggestField");
            $limit = makeInteger(retrieveOption("searchSuggestLimit"));
            $trigger = makeInteger(retrieveOption("searchSuggestTrigger"));
            if ($limit == 0) { $limit = 1; }
            if ($trigger > $searchlength) {
                header('Content-type: text/xml');
                echo "<"."?xml version=\"1.0\"?".">";
?>

<response>
    <code>0</code>
    <literal>Success</literal>
    <results>
    </results>
</response>
<?php
                $dbA->close();
                exit;
            }

            $fieldsearch = $field;
            $fieldextra = "";
            $fieldorder = $field;
            if ($field == "codename") {
                $fieldsearch = "concat(code,' ',name)";
                $fieldextra = ",".$fieldsearch." as joinedinfo";
                $fieldorder = "code,name";
            }
            if ($field == "namecode") {
                $fieldsearch = "concat(name,' ',code)";
                $fieldextra = ",".$fieldsearch." as joinedinfo";    
                $fieldorder = "name,code";
            }
            if (retrieveOption("featureStockControl") == 1) {
                if (retrieveOption("stockWarningNotZero") == 0) {
                    $scBit = "((scActionZero = 1 and scEnabled='Y' and scLevel > 0) or (scEnabled != 'Y') or (scEnabled = 'Y' and (scActionZero < 1 or scActionZero > 1)))";
                } else {
                    $scBit = "((scActionZero = 1 and scEnabled='Y' and scLevel > scWarningLevel) or (scEnabled != 'Y') or (scEnabled = 'Y' and (scActionZero < 1 or scActionZero > 1)))";
                }
                $stockControlClause = "$scBit and ($tableProducts.productID > 1 and $tableProducts.visible = 'Y') and ";
            } else {
                $stockControlClause = "($tableProducts.productID > 1 and $tableProducts.visible = 'Y') and ";
            }
            $acClause = "($tableProducts.accTypes like '%;0;%') and ";
            if (array_key_exists(retrieveOption("cookieName"),@$_COOKIE) && @$_COOKIE[retrieveOption("cookieName")] != "") {
                $cookieCart = makeSafe($_COOKIE[retrieveOption("cookieName")]);
                $result = $dbA->query("select * from $tableCarts where cartID=\"$cookieCart\"");
                if ($dbA->count($result) > 0) {
                    $record = $dbA->fetch($result);
                    $acClause = "($tableProducts.accTypes like '%;".$record["accTypeID"].";%' or $tableProducts.accTypes like '%;0;%') and ";
                }
            }
            $stockControlClause .= $acClause;
            $result = $dbA->query("select $tableProducts.name,$tableProducts.code$fieldextra from $tableProducts where $stockControlClause $fieldsearch like \"$searchbit\" order by $fieldorder limit $limit");
            $count = $dbA->count($result);
            header('Content-type: text/xml');
            echo "<"."?xml version=\"1.0\"?".">";
?>
<response>
    <code>1</code>
    <literal>Success</literal>
    <results>
<?php
            for ($f = 0; $f < $count; $f++) {
                $record = $dbA->fetch($result);
                if ($field == "code" || $field == "name") {
                    $output = $record[$field];
                }
                if ($field == "codename") {
                    $output = $record["name"];
                    if ($record["code"] != "") {
                        $output = $record["code"]." ".$output;
                    }
                }
                if ($field == "namecode") {
                    $output = $record["name"];
                    if ($record["code"] != "") {
                        $output = $output." ".$record["code"];
                    }
                }
?>
        <result>
            <name><![CDATA[<?php print htmlentities($output); ?>]]></name>
            <hilite><![CDATA[<?php print htmlentities(hiliteConvert($output,$xSearch2)); ?>]]></hilite>
        </result>
<?php
            }
?>
    </results>
</response>
<?php
            break;
        default:
            header('Content-type: text/xml');
            echo "<"."?xml version=\"1.0\"?".">";
?>
<response>
    <code>0</code>
    <literal>Unrecognised Command</literal>
    <results>
    </results>
</response>
<?php
            break;
    }
    $dbA->close();

    function stringNoAccent($str) {
        $search = explode(",","ç,æ,œ,á,é,í,ó,ú,à,è,ì,ò,ù,ä,ë,ï,ö,ü,ÿ,â,ê,î,ô,û,å,e,i,ø,u,ñ,ã,õ,ý");
        $replace = explode(",","c,ae,oe,a,e,i,o,u,a,e,i,o,u,a,e,i,o,u,y,a,e,i,o,u,a,e,i,o,u,n,a,o,y");
        $str = str_replace($search, $replace, $str);
        return $str;
    }

    function hiliteConvert($str,$search) {
        $strv = stringNoAccent($str);
        for ($f = 0; $f < strlen($strv); $f++) {
            if (strtolower(substr($strv,$f,strlen($search))) == strtolower($search)) {
                if ($f > 0) {
                    $start = substr($str,0,$f);
                } else {
                    $start = "";
                }
                $str = $start."@@".substr($str,$f,strlen($search))."@".substr($str,$f+strlen($search),strlen($str) - $f);
                $f = $f + strlen($search);
                return $str;
            }
        }
        return $str;
    }    
?>

[Redcoder]

Use htmlspecialchars() when storing in DB and htmlspecialchars_decode() when retrieveing from DB for display.

Those two functions will convert to character entities and from character entities to normal characters respectively.

No need for an array where you predefine everything....too long.

[ROYW1000]

Hi

Is there an easy way to correct this code without having to change how the database is stored.

I notice on other sites that use search suggest like http://www.ebuyer.com/
as you type 23" LCD TV it shows 23 LCD TV instead as an option.

Thanks
Roy

[Redcoder]

Well, you could code and run a script that will fetch all values in the necessary columns, strip " and other characters, then store them again. It will do this recursively for the whole database. Therefore when you are storing and searching, you just strip the " and store it or when searching, strip the " and then retrieve the matching data. This is to ensure uniformity.