Search SQL - Suggest: lower() + strtolower()

[texdc]

I'm using PHP5 & MySQL5 and according to the MySQL docs, the LIKE comparator is case-sensitive on strings. Seeing as the zen_parse_search_string function drops everything to lowercase, the advanced_search_result/header_php.php should have LOWER() modifiers put in around lines 282-326:

if (isset($keywords) && zen_not_null($keywords)) {
  if (zen_parse_search_string(stripslashes($_GET['keyword']), $search_keywords)) {
    $where_str .= " AND (";
    for ($i=0, $n=sizeof($search_keywords); $i<$n; $i++ ) {
      switch ($search_keywords[$i]) {
        case '(':
        case ')':
        case 'and':
        case 'or':
        $where_str .= " " . $search_keywords[$i] . " ";
        break;
        default:
        $where_str .= "(LOWER(pd.products_name) LIKE '%:keywords%'
                                         OR LOWER(p.products_model)
                                         LIKE '%:keywords%'
                                         OR LOWER(m.manufacturers_name)
                                         LIKE '%:keywords%'";

        $where_str = $db->bindVars($where_str, ':keywords', $search_keywords[$i], 'noquotestring');
        // search meta tags
        $where_str .= " OR (LOWER(mtpd.metatags_keywords)
                        LIKE '%:keywords%'
                        AND mtpd.metatags_keywords !='')";

        $where_str = $db->bindVars($where_str, ':keywords', $search_keywords[$i], 'noquotestring');

        $where_str .= " OR (LOWER(mtpd.metatags_description)
                        LIKE '%:keywords%'
                        AND mtpd.metatags_description !='')";

        $where_str = $db->bindVars($where_str, ':keywords', $search_keywords[$i], 'noquotestring');

        if (isset($_GET['search_in_description']) && ($_GET['search_in_description'] == '1')) {
          $where_str .= " OR LOWER(pd.products_description)
                          LIKE '%:keywords%'";

          $where_str = $db->bindVars($where_str, ':keywords', $search_keywords[$i], 'noquotestring');
        }
        $where_str .= ')';
        break;
      }
    }
    $where_str .= " )";
  }
}

HTH, Thanks.

[wilt]

Hi,

Will log this as a bug. I agree with that LIKE and its other incarnations are case sensitive. Will need to check other code as well wher we use LIKE in a query.

Thanks for the report

[DrByte]

While I understand the theory presented, I can't create the problem described. Testing on MySQL 4.1.14.

I was thinking this is only a problem if the collation method set on the database is set to a binary collation (ie: latin1_bin) or case-sensitive (ie: latin1_general_cs) method instead of a case-insensitive (ie: latin1_general_ci) collation method.
But, when I set up a new database using latin1_general_cs as collation method, anticipating case-sensitive behaviour, the searches still work fine. Same when choosing latin1_bin (binary), as well as latin1_general_ci and latin1_swedish_ci (which are case-insensitive collation methods).

From the MySQL manual:

Normally, if any expression in a string comparison is case sensitive, the comparison is performed in case-sensitive fashion.

Since the syntax of the LIKE comparison is "expr LIKE pattern", it would seem that "expr" is the database-content portion (case not converted), and "pattern" is the pattern we're matching against, which is pre-converted to lowercase.

It would seem at the present time that MySQL4 (well 4.1 anyway) doesn't suffer from the case-sensitive issue you mention. Either that or it has nothing to do with collation.

Notwithstanding that the theory you present is logical, I can't create the problem described in order to confirm that the suggested fix is truly the best approach.