[Roderik]

Damn should have kept reading till the botom of the page

Since i was bored i made a little function that makes a nice mySQL 3 string of where statements to "replace" the full text indexing.

Function works, didn't try the sql output but it's already 3.00 AM...

Hope you can do something with it to make the thing fully mySQL 3 compatible

PHP Code:

<?php
/**
 * Function that will handle the conversion from the boolean 
 * full text search that is only available for mySQL v4.0.1 
 * and higher to a mySQL v3 alternative.
 *
 * @param       string      $keywords (+xxx -yyy +zzz)
 * @return      string      the string with the sql commands
 * @author      Roderik van der Veer <roderik@vanderveer.be>
 * @version     $Id$
 */


function mysqlVersionThreeFix($keywords){
    $kw = explode(" ", $keywords);
    
    $kwplus = array();
    $kwmin = array();
    
    for ($i=0; $i < sizeof($kw); $i++){
        if ($kw[$i]{0} == '+'){
            array_push($kwplus, substr($kw[$i], 1));
        } else {
            array_push($kwmin, substr($kw[$i], 1));   
        }      
    }

    $sqlstring = 'AND (';
    
    for ($i=0; $i < sizeof($kwplus); $i++){
        if ($i != 0) {
            $sqlstring .= ' AND ';
        }
        $sqlstring .= "(name LIKE '%".$kwplus[$i]."%' OR description LIKE '%".$kwplus[$i]."%')";
    }
    for ($i=0; $i < sizeof($kwmin); $i++){
        $sqlstring .= " AND (name NOT LIKE '%".$kwmin[$i]."%' OR description NOT LIKE '%".$kwmin[$i]."%')";
    }  
        
    $sqlstring .= ') '; 
    
    return $sqlstring;    

}

$keywords = '+word1 +word2 -word3 +word4 -word5';

echo mysqlVersionThreeFix($keywords);

?>

Output:

AND ((name LIKE '%word1%' OR description LIKE '%word1%') AND (name LIKE '%word2%' OR description LIKE '%word2%') AND (name LIKE '%word4%' OR description LIKE '%word4%') AND (name NOT LIKE '%word3%' OR description NOT LIKE '%word3%') AND (name NOT LIKE '%word5%' OR description NOT LIKE '%word5%'))