History: MySQL4searchHack

Despite the advertised ability, I was unable to get working searches on quoted phrases or booleans using MySQL 4.0.15.

Unless I'm missing something, the TikiWiki search engine doesn't work as expected either. Quoted strings just act like simple ORs and boolean +/- prefixes cause lots of error messages. I had the same symptoms on my 1.8 implementation until I made the following changes in searchlib:

I realize this may break other databases.

original:
function _find($h, $words = '', $offset = 0, $maxRecords = -1, $fulltext = false) {
$words = trim($words);
$sql = sprintf(
'SELECT %s AS name, LEFT(%s, 240) AS data, %s AS hits, %s AS lastModif, %s AS pageName',
$h'name', $h'data', $h'hits', $h'lastModif', $h'pageName');
$id = $h'id';
for ($i = 0; $i < count($id); ++$i)
$sql .= ',' . $id$i . ' AS id' . ($i + 1);
if (count($id) < 2)
$sql .= ',1 AS id2';

$sql2 = ' FROM ' . $h'from' . ' WHERE 1';
$search_fields = array($h'name');
if ($h'data' && $h'name' != $h'data')
array_push($search_fields, $h'data');
$orderby = (isset($h'orderby') ? $h'orderby' : $h'hits');
if ($fulltext) {
if 'search'" class="wiki wikinew text-danger tips count">$h'search'
if (!preg_match('/\./', $h'search'0))
$search_fields = array_merge($search_fields, $h'search');
$qwords = $this->db->quote($words);
$sqlft = 'MATCH(' . join(',', $search_fields) . ') AGAINST (' . $qwords . ')';
$sql2 .= ' AND ' . $sqlft . ' >= 0';
$sql .= ', ' . $sqlft . ' AS relevance';
$orderby = 'relevance desc, ' . $orderby;

} else if ($words) {
$sql .= ', -1 AS relevance';
$vwords = split(' ',$words);
foreach ($vwords as $aword) {
//$aword = $this->db->quote('[[:<:]]' . strtoupper($aword) . '[[:>:]]');
$aword = $this->db->quote('.*'.strtoupper($aword).'.*');
$sql2 .= ' AND (';
for ($i = 0; $i < count($search_fields); ++$i) {
if ($i)
$sql2 .= ' OR ';
$sql2 .= 'UPPER(' . $search_fields$i . ') REGEXP ' . $aword;
}
$sql2 .= ')';
}
} else {
$sql .= ', -1 AS relevance';
}

$cant = $this->getOne('SELECT COUNT(*)' . $sql2);

if (!$cant) {
return array('data' => array(), 'cant' => 0);
}
$sql .= $sql2 . ' ORDER BY ' . $orderby . ' DESC LIMIT ' . $offset . ',' . $maxRecords;
$result = $this->query($sql);
$ret = Array();
while ($res = $result->fetchRow(DB_FETCHMODE_ASSOC)) {
$href = sprintf(urldecode($h'href'), $res'id1', $res'id2');
$ret[] = array(
'pageName' => $res"pageName",
'data' => $res"data",
'hits' => $res"hits",
'lastModif' => $res"lastModif",
'href' => $href,
'relevance' => round($res"relevance", 3),
);
}
return array('data' => $ret, 'cant' => $cant);
}

revised:
function _find($h, $words = '', $offset = 0, $maxRecords = -1, $fulltext = false) {
$words = trim($words);
$sql = sprintf(
'SELECT %s AS name, LEFT(%s, 240) AS data, %s AS hits, %s AS lastModif, %s AS pageName',
$h'name', $h'data', $h'hits', $h'lastModif', $h'pageName');

$id = $h'id';
for ($i = 0; $i < count($id); ++$i)
$sql .= ',' . $id$i . ' AS id' . ($i + 1);
if (count($id) < 2)
$sql .= ',1 AS id2';

$sql2 = ' FROM ' . $h'from' . ' WHERE ';
$sql2 .= 'filter'" class="wiki wikinew text-danger tips isset">$h'filter'? $h'filter' : '1';

$orderby = (isset($h'orderby') ? $h'orderby' : $h'hits');

if ($fulltext) {
$qwords = $this->db->quote($words);
$sqlft = 'MATCH(' . join(',', $h'search'). ') AGAINST (' . $qwords . ' IN BOOLEAN MODE)';
$sql2 .= ' AND ' . $sqlft ;
$sql .= ', ' . $sqlft . ' AS relevance';
$orderby = 'relevance desc, ' . $orderby;

} else if ($words) {
$sql .= ', -1 AS relevance';
$vwords = split(' ',$words);
foreach ($vwords as $aword) {
//$aword = $this->db->quote('[[:<:]]' . strtoupper($aword) . '[[:>:]]');
$aword = $this->db->quote('.*'.strtoupper($aword).'.*');
$sql2 .= ' AND (';
for ($i = 0; $i < count($search_fields); ++$i) {
if ($i)
$sql2 .= ' OR ';
$sql2 .= 'UPPER(' . $search_fields$i . ') REGEXP ' . $aword;
}
$sql2 .= ')';
}
} else {
$sql .= ', -1 AS relevance';
}

$cant = $this->getOne('SELECT COUNT(*)' . $sql2);

//if (!$cant) { // no result
// if ($fulltext && $words) // try a simple search
// return $this->_find($h, $words, $offset, $maxRecords, false);
// else
// return array(
// 'data' => array(),
// 'cant' => 0
// );
//}

$sql .= $sql2 . ' ORDER BY ' . $orderby . ' DESC LIMIT ' . $offset . ',' . $maxRecords;

$result = $this->query($sql);
$ret = array();

while ($res = $result->fetchRow(DB_FETCHMODE_ASSOC)) {
$href = sprintf(urldecode($h'href'), $res'id1', $res'id2');

$ret[] = array(
'pageName' => $res"pageName",
'data' => $res"data",
'hits' => $res"hits",
'lastModif' => $res"lastModif",
'href' => $href,
'relevance' => round($res"relevance", 3),
);
}

return array('data' => $ret, 'cant' => $cant);
}

diff:
5a6
>
12,15c13,15
< $sql2 = ' FROM ' . $h'from' . ' WHERE 1';
< $search_fields = array($h'name');
< if ($h'data' && $h'name' != $h'data')
< array_push($search_fields, $h'data');

> $sql2 = ' FROM ' . $h'from' . ' WHERE ';
> $sql2 .= 'filter'" class="wiki wikinew text-danger tips isset">$h'filter'? $h'filter' : '1';
>
16a17
>
18,20d18
< if 'search'" class="wiki wikinew text-danger tips count">$h'search'
< if (!preg_match('/\./', $h'search'0))
< $search_fields = array_merge($search_fields, $h'search');
22,23c20,21
< $sqlft = 'MATCH(' . join(',', $search_fields) . ') AGAINST (' . $qwords . ')';
< $sql2 .= ' AND ' . $sqlft . ' >= 0';

> $sqlft = 'MATCH(' . join(',', $h'search'). ') AGAINST (' . $qwords . ' IN BOOLEAN MODE)';
> $sql2 .= ' AND ' . $sqlft ;
44a43
>
47,49c46,56
< if (!$cant) {
< return array('data' => array(), 'cant' => 0);
< }

> //if (!$cant) { // no result
> // if ($fulltext && $words) // try a simple search
> // return $this->_find($h, $words, $offset, $maxRecords, false);
> // else
> // return array(
> // 'data' => array(),
> // 'cant' => 0
> // );
> //}
>
>
50a58
>
52a61,62
>
>
54a65
>
63a75
>