Hi guys,
I have a strange SQL query problem that someone might have a clue how to fix.
I\'m using a search called DGS Search (http://www.digitalgenesis.com) on our Intranet site for searching our phonelist. The reason I use it is because it has both file search (which willb e used later) and DB search + why create something from scratch when it\'s already done 🙂.
It works great expect for a strange thing - when using any of our Swedish special characters å,ä or ö inside a search (like search for Björn) it returns 0 results even though we are 7 people with that name here. If, however, I search for Bj I get those 7 results.
BTW - I have mailed the guys at digital Genesis but not received any response yet.
I dug down into the DB.php file to see what kind of SQL-string it creates since I suspected that the error was here. I put in an echo-statement just after the SQL-string has been constructed and this is what the whole sQL-string looks like:
When searching for Bj it looks like this:
SELECT IDnr, FName, EName, Phone, Mobile, Email, Function FROM tbl_main WHERE LOWER(IDnr) LIKE \'%bj%\' OR LOWER(FName) LIKE \'%bj%\' OR LOWER(EName) LIKE \'%bj%\' OR LOWER(Phone) LIKE \'%bj%\' OR LOWER(Mobile) LIKE \'%bj%\' OR LOWER(FaxNr) LIKE \'%bj%\' OR LOWER(Email) LIKE \'%bj%\' OR LOWER(Function) LIKE \'%bj%\' ORDER BY IDnr, FName, EName, Phone, Mobile, Email, Function
When searching for björn it looks like this:
SELECT IDnr, FName, EName, Phone, Mobile, Email, Function FROM tbl_main WHERE LOWER(IDnr) LIKE \'%björn%\' OR LOWER(FName) LIKE \'%björn%\' OR LOWER(EName) LIKE \'%björn%\' OR LOWER(Phone) LIKE \'%björn%\' OR LOWER(Mobile) LIKE \'%björn%\' OR LOWER(FaxNr) LIKE \'%björn%\' OR LOWER(Email) LIKE \'%björn%\' OR LOWER(Function) LIKE \'%björn%\' ORDER BY IDnr, FName, EName, Phone, Mobile, Email, Function
Ok, taking the exact query and inserting it in phpmyAdmin, the Björn-query works. If I put in ($statement is the variable where the sql string is stored):
$statement=\"SELECT IDnr, FName, EName, Phone, Mobile, Email, Function FROM tbl_main WHERE LOWER(IDnr) LIKE \'%björn%\' OR LOWER(FName) LIKE \'%björn%\' OR LOWER(EName) LIKE \'%björn%\' OR LOWER(Phone) LIKE \'%björn%\' OR LOWER(Mobile) LIKE \'%björn%\' OR LOWER(FaxNr) LIKE \'%björn%\' OR LOWER(Email) LIKE \'%björn%\' OR LOWER(Function) LIKE \'%björn%\' ORDER BY IDnr, FName, EName, Phone, Mobile, Email, Function\";
inside the file (in other words - hardwire the search to always search for björn) it works. This leads me to conclude that the problem is in the creation of the SQL-string - not the actual routine that searches the database. But the SQL-string that it produces, when printed out in an echo statement, is the one that works ....
You might understand now that I\'m completely lost here. Here are the routine that builds the SQL-string. Maybe somone who has worked a bit more with PHP and MySQL can spot a potential reason for the problem?
$q - String value. This is the submitted query.
$r - Integer value. This is the number of results to display per page.
If this value is 0, then all results are displayed.
$o - Integer value. Offset into $retVal of the current results that will be displayed.
$s - Integer value. This cached size of the $retVal from a previous search.
$c - Integer value. This is the current count of matches at the time of calling this
search function.
$statement = \'SELECT \';
$orderBy = \' ORDER BY \';
if ($s > 0 && $r > 0) {
$limit = \' LIMIT \';
if ($o > 0)
$limit .= sprintf(\'%d,%d\', $o, $r);
else
$limit .= $r - ($c % $r);
}
$i = 0;
reset($rfields);
while (list(, $entry) = each($rfields)) {
$statement .= $entry;
if ($i < $orderByDepth || $orderByDepth < 0)
$orderBy .= $entry;
$i++;
if ($i < count($rfields)) {
$statement .= \', \';
if ($i < $orderByDepth || $orderByDepth < 0)
$orderBy .= \', \';
}
}
$statement .= \' FROM \';
$i = 0;
reset($tables);
while (list(, $entry) = each($tables)) {
$i++;
$statement .= $entry;
if ($i < count($tables))
$statement .= \', \';
}
$statement .= \' WHERE \';
if (strlen($tableAssoc) > 0)
$statement .= $tableAssoc . \' AND (\';
$i = 0;
reset($sfields);
while (list(, $entry) = each($sfields)) {
$i++;
if ($forceLower) {
$statement .= \'LOWER(\' . $entry . \')\';
$q = strtolower($q);
} else {
$statement .= $entry;
}
if (!strcmp($wildcard, \'none\')) {
$statement .= \' = \\\'\';
} else {
$statement .= \' LIKE \\\'\';
}
if (!strcmp($wildcard, \'left\') || (strcmp($wildcard, \'right\') && strcmp($wildcard, \'none\')))
$statement .= \'%\';
$statement .= $q;
if (!strcmp($wildcard, \'right\') || (strcmp($wildcard, \'left\') && strcmp($wildcard, \'none\')))
$statement .= \'%\';
$statement .= \'\\\'\';
if ($i < count($sfields))
$statement .= \' OR \';
}
if (strlen($tableAssoc) > 0)
$statement .= \') \';
if ($orderByDepth != 0)
$statement .= $orderBy;
if ($limit)
$statement .= $limit;
Thanks in advance
Björn Tidal
