Searching is case sensitive?

[mfong]

Hi! Let me start by saying that this is a great app! Kudos to the Team!

Question though - currently when running searches, we're getting inconsistent results with case sensitivity. For example, we search "adobe" or "Adobe" in one article and it works fine - all instances of Adobe are highlighted. However, there are instances of "Lexis" in another article, and the result is only returned if I search for "Lexis". Searching "lexis" does not return results.

I think this issue is related to keywords. If the keyword is specified in lower case, then it will find it in the article, regardless of case. If the keyword is not specified, then the body text is case sensitive.

This is probably done for performance reasons, but is there any way to change this so searches are not dependent on keywords, or are case insensitive? We don't have that large a KB, and a very small user base.

Thanks!
M

[Thorsten]

Hi,

which database do you use?

bye
Thorsten

[mfong]

Ah, sorry, that would be important.

Running on
Windows XP
Apache 2.2.16
PHP 5.3.3
MySQL 5.1.51-Community
EDIT: using phpMyFAQ 2.6.9

All default install setups on a single host.

[Thorsten]

Hi,

you could change the line 75 in inc/PMF_Search/Database/Mysql.php from

Code: Select all

MATCH (%s) AGAINST ('%s' IN BOOLEAN MODE)

to

Code: Select all

MATCH (LOWER(%s)) AGAINST (UPPER('%s') IN BOOLEAN MODE)

I didn't tried it, but should work.

bye
Thorsten

[mfong]

Hmmm. Didn't work for me. It actually seemed to have no effect. I still have the same result effect as the situation above.

Here's the code from my Mysql.php file - the line for me is actually on Line 71 instead of 75, but I think that's where you wanted it:

Code: Select all

<?php
/**
 * phpMyFAQ MySQL (ext/mysql) search classes
 *
 * PHP Version 5.2
 *
 * The contents of this file are subject to the Mozilla Public License
 * Version 1.1 (the "License"); you may not use this file except in
 * compliance with the License. You may obtain a copy of the License at
 * http://www.mozilla.org/MPL/
 *
 * Software distributed under the License is distributed on an "AS IS"
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
 * License for the specific language governing rights and limitations
 * under the License.
 * 
 * @category  phpMyFAQ
 * @package   PMF_Search_Database
 * @author    Thorsten Rinne <thorsten@phpmyfaq.de>
 * @copyright 2010 phpMyFAQ Team
 * @license   http://www.mozilla.org/MPL/MPL-1.1.html Mozilla Public License Version 1.1
 * @link      http://www.phpmyfaq.de
 * @since     2010-06-06
 */

/**
 * PMF_Search_Database_Mysql
 *
 * @category  phpMyFAQ
 * @package   PMF_Search_Database
 * @author    Thorsten Rinne <thorsten@phpmyfaq.de>
 * @copyright 2010 phpMyFAQ Team
 * @license   http://www.mozilla.org/MPL/MPL-1.1.html Mozilla Public License Version 1.1
 * @link      http://www.phpmyfaq.de
 * @since     2010-06-06
 */
class PMF_Search_Database_Mysql extends PMF_Search_Database
{
    /**
     * Constructor
     * 
     * @param PMF_Language $language Language
     * 
     * @return PMF_Search_Abstract
     */
    public function __construct(PMF_Language $language)
    {
        parent::__construct($language);
    }
    
    /**
     * Prepares the search and executes it
     * 
     * @param string $searchTerm Search term
     * 
     * @return resource
     * 
     * @throws PMF_Search_Exception
     */
    public function search($searchTerm)
    {
        if (is_numeric($searchTerm)) {
            parent::search($searchTerm);
        } else {
            $query = sprintf("
                SELECT
                    %s
                FROM 
                    %s %s %s
                WHERE
  				    MATCH (LOWER(%s)) AGAINST (UPPER('%s') IN BOOLEAN MODE)
                    %s",
                $this->getResultColumns(),
                $this->getTable(),
                $this->getJoinedTable(),
                $this->getJoinedColumns(),
                $this->getMatchingColumns(),
                $this->dbHandle->escape_string($searchTerm),
                $this->getConditions());
            
            $this->resultSet = $this->dbHandle->query($query);
            
            // Fallback for searches with less than three characters
            if (0 == $this->dbHandle->num_rows($this->resultSet)) {
                
                $query = sprintf("
                    SELECT
                        %s
                    FROM 
                        %s %s %s
                    WHERE
                        %s
                        %s",
                    $this->getResultColumns(),
                    $this->getTable(),
                    $this->getJoinedTable(),
                    $this->getJoinedColumns(),
                    $this->getMatchClause($searchTerm),
                    $this->getConditions());
            }
            
            $this->resultSet = $this->dbHandle->query($query);
        }
        
        return $this->resultSet;
    }
}

[Thorsten]

Hi,

sorry, I'm dumb. It has to be UPPER() and UPPER(), not LOWER() and UPPER()...

bye
Thorsten

[mfong]

That didn't quite work either. I did a little research, and MySQL searches are case insensitive by default. This should work without modification.

I have limited coding skills, but I think there's something else. For example, I can delete lines 66 through 72 on mysql.php without issue or change to the search function. This is what's left:

Code: Select all

  (line65)     $query = sprintf("",
                $this->getResultColumns(),
                $this->getTable(),
                $this->getJoinedTable(),
                $this->getJoinedColumns(),
                $this->getMatchingColumns(),
                $this->dbHandle->escape_string($searchTerm),
                $this->getConditions());

However, modifications to lines 87 to 93 immediately affect the results. I'll take a few more looks - i wish I could code better though.

[Thorsten]

Hi,

phpMyFAQ is executing 2 queries by hitting the search button: one with fulltext search and one with LIKE. I think the second one causes the issue. I'll check this.

bye
Thorsten