|  Mysql2Search

Mysql2Search

Mysql 2 Search

I decided to publish these classes for the public. I’ve been using this class for my own systems and recently also added support for Sphinx search. I’ve only been introduced to Sphinx recently and it is pretty awesome search engine. Due to a cluster setup of my database I had to switch engines from MyISAM to InnoDB and InnoDB does not support full text search (I know since version 5.6 Mysql added support for full text for InnoDB however most of the world is only running 5.1). So searching using ‘like’ is not really advisable so I decided to search for other options to search in InnoDB tables and ended up with Sphinx.

I haven’t even started to get to know Sphinx and all its options so the implementation in the class bellow is very minimal and yet to be extended when I have the need for it. I hope my classes can help you. Note that ‘sphinxapi.php’ is not made by me and comes with the Sphinx installation file.

Sphinx site: sphinxsearch.com

Step 1.
Sphinx config (on debian located at /etc/sphinxsearch/sphinx.conf) is the most important file for using the sphinx search engine. In this file you define the data source and index. The example config file that comes with sphinx is very extensive and has a lot of useful examples and explanation. I would suggest to read through the whole file. Basically what you need to do in this file is creating source+index combis:

source src1
{
    type                    = mysql

    # some straightforward parameters for SQL source types
    sql_host                = localhost
    sql_user                = user
    sql_pass                = pw
    sql_db                    = db
    sql_port                = 3306    # optional, default is 3306

    # UNIX socket name
    sql_sock                = /var/lib/mysql/mysql.sock

    sql_query                = \
        SELECT id, searchcol1, searchcol2 \
        FROM dbtobesearched

    sql_ranged_throttle    = 0
    sql_query_info        = SELECT * FROM ics_supp_phones WHERE id=$id
}

index indx1
{
    source            = src1

    # index files path and file name, without extension
    # mandatory, path must be writable, extensions will be auto-appended
    path            = /var/lib/sphinxsearch/data/suppphones

    # document attribute values (docinfo) storage mode
    # optional, default is ‘extern’
    # known values are ‘none’, ‘extern’ and ‘inline’
    docinfo            = extern

    # memory locking for cached data (.spa and .spi), to prevent swapping
    # optional, default is 0 (do not mlock)
    # requires searchd to be run from root
    mlock            = 0

    # morphology     = stem_en, stem_ru, soundex
    # morphology    = libstemmer_german
    # morphology    = libstemmer_sv
    morphology        = stem_en
    # stopwords            = /var/lib/sphinxsearch/data/stopwords.txt

    # minimum indexed word length
    # default is 1 (index everything)
    min_word_len        = 3

    # charset encoding type
    # optional, default is ‘sbcs’
    # known types are ‘sbcs’ (Single Byte CharSet) and ‘utf-8′
    charset_type        = sbcs

    # minimum word infix length to index
    # optional, default is 0 (do not index infixes)
    #
    min_infix_len        = 3
    enable_star        = 1
    html_strip        = 0
    index_exact_words    = 1
}

You can make as many source and index combinations as you want (you can even use inheritance see example sphinx.conf for that) and you can name them as you like. In the source you define the Mysql query which reads all the data that will be indexed and thus will be searchable. You can do joins in your query no problem. Only obligatory thing is that you need a primary key (int) called ‘id’ as the first column that is selected as that is the id that will be returned as result of the search. The line ‘sql_query_info’ is the info that is displayed in search results but ONLY when using the command line so not that useful for use with PHP.

For use with my Mysql2Search class you should limit yourself to searching in 1 table and name the index(ces) in sphinx.conf the same as the table name(s) you are searching in. If you need to search in more then one table just create extra src+index combi for it in sphinx.conf and a second Mysql2Search object. Download all the code:  Mysql2Search

Code:

Mysql2Search class:

<?PHP
/**************************************************************/
/* Mysql2Search class                                         */
/*                                                            */
/*                                                               */
/*                                                            */
/* Use:                                                          */
/* Set config (set_config()) with 2 vars; $searchtable and       */
/* $fields. $searchtable is the table with the data to be     */
/* search. $fields is an array with columns names from that   */
/* table to be searched, put the columns names in the array      */
/* in order of importance!                                        */
/*                                                               */
/* Then set the searchterm using set_searchterm()             */
/* Then get te searchresults get_results()                    */
/*                                                            */
/* Results are returned as array in $_searchresult            */
/* The keys as ID’s from the db values and the array value    */
/* as relevancy (the higher number the higher relevancy)      */
/*                                                               */
/* The array is already ordered by relevancy when it’s        */
/* returned                                                   */
/*                                                               */
/* Version: 0.6 (11 Nov 2011)                                  */
/* Copyright: Steven van der Peijl – stevenvanderpeijl.com      */
/*                                                            */
/* Use example                                                   */
/*        $searchdb = “blogdb”;                                  */
/*        $searchfields = array(‘blog_mess’, ‘blog_replies’);      */
/*        $mysql2search = new icssearch();                      */
/*        $mysql2search->SetSearchType(“sphinx”) #OPTIONAL      */
/*        $mysql2search->set_config($searchdb, $searchfields);  */
/*        $mysql2search->set_searchterm($searchterm);              */
/*                                                              */
/*        $sresult = $mysql2search->get_results();              */
/*        $searchtime = $mysql2search->get_searchtime();          */
/*                                                              */
/*                                                              */
/**************************************************************/
if(file_exists(“sphinx.class.php”)){
    include(“sphinx.class.php”);
    define(‘SPHINX’, true);     
}

class icssearch {
    var $_dbuser            = “username”;                                //database connection username
    var $_dbpass            = “password”;                                //database connection password
    var $_dbname            = “dbname”;                                    //database name
    var $_host                = “localhost”;                                //database host (ip or localhost)
    var $_dblink;
    var $_searchresult         = array();                                    //hier worden alle resultaten in opgeslagen + prioriteit
    var $_zoekterm             = array();                                    //de zoekterm
    var $_fullterm;                                                        //fullterm
    var $_searchfields        = array();                                    //set the table columns to search in
    var $_begintime;                                                    //begin tijd van het zoeken
    var $_endtime;                                                        //end time
    var $_zoekdb;                                                        //the table to be searched
    var $_extra;                                                        //extra sql restraints
    var $_SearchType         = “like”;                                    //SearchType ‘sphinx’ | ‘fulltext’ | ‘like’
    var $_fulltxts             = array();                                    //available fulltext columns in searchdb.
    var $_showsearcherrors     = true;                                        //show search type error
    var $_debug             = false;
    
    function icssearch(){
        $time = microtime();
        $time = explode(‘ ‘, $time);
        $time = $time[1] + $time[0];
        $this->_begintime = $time;                                                        //set begintime
    }
    
    function set_config($searchtable, $fields, $debug = false){                                                 //$fields must be an array!
        $this->_zoekdb = $searchtable;
        $this->_searchfields = $fields;
        if($this->_SearchType == “sphinx” AND SPHINX != true){
            echo “Error; search set to sphinx but sphinx class not present”;
            $this->_SearchType = “like”;
        }
        if($this->_SearchType == “fulltext”){
            $this->SetFullTxt($searchtable);
            foreach($fields as $field){
                if(!in_array($field,$this->_fulltxts)){
                    $this->_SearchType = “like”;
                    if($this->_showsearcherrors == true)
                        echo “<br /><strong>Error</strong> $field does not have a fulltext index”;
                }
            }
        }        
        if($debug === true) $this->_debug = true;
    }
    
    private function SetFullTxt($zoekdb){
        $sql = “SHOW INDEX FROM “.$zoekdb.” WHERE Index_type = ‘FULLTEXT’”;
        $result = $this->dosql($sql) or die(“Mysql error or database connection error at “.__LINE__);
        while($row = mysql_fetch_assoc($result)){
            $this->_fulltxts[] = $row['Column_name'];
        }
    }
    
    function SetSearchType($stype){
        $searchtype = trim(strtolower($stype));
        if($searchtype == “sphinx” AND SPHINX != true){
            $searchtype = “like”;    
        }
        $this->_SearchType = $searchtype;
    }
    
    function GetSearchType(){
        return $this->_SearchType;
    }
    
    function set_extra($extra){                                                            //set extra contrains for mysql search queries
        $this->_extra = $extra;
    }
    
    function set_searchterm($searchterm){
        $searchterm = trim($searchterm);
        $this->_fullterm = $searchterm;
        $this->_zoekterm = explode(” “, $searchterm);                                    //set the searchterm in a array (explode on space to make difference)    
    }
    
    function get_results(){
        $searchresult = array();
        switch($this->_SearchType){
            case “fulltext”:
                $searchresult = $this->get_results_fulltext();
                break;
            case “like”:
                $searchresult = $this->get_results_like();
                break;
            case “sphinx”:
                $searchresult = $this->get_results_sphinx();
                break;
        }
        return $searchresult;
    }
    
    private function FullTermClean($sterm){
        $cleanfullterm = trim(strtolower($sterm));
        $cleanfullterm = preg_replace(“/[^A-Za-z0-9\s\s+]/”,””,$cleanfullterm);
        //use this function for example to filter some common words which don’t help the search
        //However when using sphinx you can also use stopword list (see manual)
        return $cleanfullterm;    
    }
    
    function get_results_sphinx(){
        include(“sphinx.class.php”);
        $sphinx = new spinx_search($this->_fullterm);
        $sphinx->SetmatchModeExt(“all”);
        $sphinx->SetSearchIndex($this->_zoekdb);
        $sphinxres = $sphinx->GetSearchResults();
        if(!is_array($sphinxres)){
            echo “Error result is no array 1″;    
        }else{
            if($sphinxres['status'] == “error” AND ($this->_showsearcherrors OR $this->_debug)){
                echo “Search status: “.$sphinxres['status'];
                echo “<br />Message: “.$sphinxres['status_message'];
            }else{
                foreach($sphinxres['keys'] as $phoneid){
                    $this->_searchresult[$phoneid] = “10″;    
                }
            }
        }
        
        //round 2
        $sphinx = new spinx_search($this->_fullterm);
        $sphinx->SetmatchModeExt(“any”);
        $sphinx->SetSearchIndex($this->_zoekdb);
        $sphinxres = $sphinx->GetSearchResults();
        if(!is_array($sphinxres)){
            echo “Error result is no array 2″;    
        }else{
            if($sphinxres['status'] == “error” AND ($this->_showsearcherrors OR $this->_debug)){
                echo “Search status: “.$sphinxres['status'];
                echo “<br />Message: “.$sphinxres['status_message'];
            }else{
                foreach($sphinxres['keys'] as $phoneid){
                    if(!array_key_exists($phoneid, $this->_searchresult)){
                        $this->_searchresult[$phoneid] = “7″;
                    }    
                }
            }
        }
        
        //round 3
        $term = substr($this->_fullterm,0,-1).”*”;
        $sphinx = new spinx_search($term);
        $sphinx->SetmatchModeExt(“any”);
        $sphinx->SetSearchIndex($this->_zoekdb);
        $sphinxres = $sphinx->GetSearchResults();
        if(!is_array($sphinxres)){
            echo “Error result is no array 2″;    
        }else{
            if($sphinxres['status'] == “error” AND ($this->_showsearcherrors OR $this->_debug)){
                echo “Search status: “.$sphinxres['status'];
                echo “<br />Message: “.$sphinxres['status_message'];
            }else{
                foreach($sphinxres['keys'] as $phoneid){
                    if(!array_key_exists($phoneid, $this->_searchresult)){
                        $this->_searchresult[$phoneid] = “5″;
                    }    
                }
            }
        }
        
        //round 4
        $term = “*”.substr($this->_fullterm,1);
        $sphinx = new spinx_search($term);
        $sphinx->SetmatchModeExt(“any”);
        $sphinx->SetSearchIndex($this->_zoekdb);
        $sphinxres = $sphinx->GetSearchResults();
        if(!is_array($sphinxres)){
            echo “Error result is no array 2″;    
        }else{
            if($sphinxres['status'] == “error” AND ($this->_showsearcherrors OR $this->_debug)){
                echo “Search status: “.$sphinxres['status'];
                echo “<br />Message: “.$sphinxres['status_message'];
            }else{
                foreach($sphinxres['keys'] as $phoneid){
                    if(!array_key_exists($phoneid, $this->_searchresult)){
                        $this->_searchresult[$phoneid] = “5″;
                    }    
                }
            }
        }        
                
        //could be extended with using boolean or extended search and special operators or use of wildcards like *
        $time = microtime();
        $time = explode(” “, $time);
        $time = $time[1] + $time[0];
        $this->_endtime = $time;
        return $this->_searchresult;
    }
    
    function get_results_like(){
        $sterm = $this->FullTermClean($this->_fullterm);
        $likecol = “”;
        foreach($this->_searchfields as $col){
            $likecol .= “$col LIKE ‘”.$sterm.”‘ OR “;
        }
        $likecol = substr($likecol,0,-4);
        $sql = “SELECT id,phone_model FROM “.$this->_zoekdb.” WHERE $likecol ORDER BY id DESC”;
        $result = $this->dosql($sql, true) or die(“Mysql error or database connection error at “.__LINE__);
        $count = mysql_num_rows($result);
        if($count > 0 ){
            while($row = mysql_fetch_assoc($result)){
                if(!array_key_exists($row['id'], $this->_searchresult)){        //als de key niet al bestaat (anders dubbele zoekresultaten)
                    $this->_searchresult[$row['id']] = 10;
                    //echo “<br />1 Found: “.$row['phone_model'].” id: “.$row['id'].” score: 10″;
                }    
            }    
        }
        
        $likecol = “”;
        foreach($this->_searchfields as $col){
            $likecol .= “$col LIKE ‘”.$sterm.”%’ OR “;
        }
        $likecol = substr($likecol,0,-4);
        $sql = “SELECT id,phone_model FROM “.$this->_zoekdb.” WHERE $likecol ORDER BY id DESC”;
        $result = $this->dosql($sql, true) or die(“Mysql error or database connection error at “.__LINE__);
        $count = mysql_num_rows($result);
        if($count > 0 ){
            while($row = mysql_fetch_assoc($result)){
                if(!array_key_exists($row['id'], $this->_searchresult)){        //als de key niet al bestaat (anders dubbele zoekresultaten)
                    $this->_searchresult[$row['id']] = 8;
                    //echo “<br />1 Found: “.$row['phone_model'].” id: “.$row['id'].” score: 10″;
                }    
            }    
        }
        
        $likecol = “”;
        foreach($this->_searchfields as $col){
            $likecol .= “$col LIKE ‘%”.$sterm.”%’ OR “;
        }
        $likecol = substr($likecol,0,-4);
        $sql = “SELECT id,phone_model FROM “.$this->_zoekdb.” WHERE $likecol ORDER BY id DESC”;
        $result = $this->dosql($sql, true) or die(“Mysql error or database connection error at “.__LINE__);
        $count = mysql_num_rows($result);
        if($count > 0 ){
            while($row = mysql_fetch_assoc($result)){
                if(!array_key_exists($row['id'], $this->_searchresult)){        //als de key niet al bestaat (anders dubbele zoekresultaten)
                    $this->_searchresult[$row['id']] = 6;
                    //echo “<br />1 Found: “.$row['phone_model'].” id: “.$row['id'].” score: 10″;
                }    
            }    
        }
        
        
        $likecol = “”;
        foreach($this->_searchfields as $col){
            $likecol .= “$col LIKE ‘%”.$sterm.”%’ OR “;
        }
        $likecol = substr($likecol,0,-4);
        $sql = “SELECT id,phone_model FROM “.$this->_zoekdb.” WHERE $likecol ORDER BY id DESC”;
        $result = $this->dosql($sql, true) or die(“Mysql error or database connection error at “.__LINE__);
        $count = mysql_num_rows($result);
        if($count > 0 ){
            while($row = mysql_fetch_assoc($result)){
                if(!array_key_exists($row['id'], $this->_searchresult)){        //als de key niet al bestaat (anders dubbele zoekresultaten)
                    $this->_searchresult[$row['id']] = 6;
                    //echo “<br />1 Found: “.$row['phone_model'].” id: “.$row['id'].” score: 10″;
                }    
            }    
        }
        
        $likecol = “”;$sterm = substr($sterm,0,-1);
        foreach($this->_searchfields as $col){
            $likecol .= “$col LIKE ‘%”.$sterm.”%’ OR “;
        }
        $likecol = substr($likecol,0,-4);
        $sql = “SELECT id,phone_model FROM “.$this->_zoekdb.” WHERE $likecol ORDER BY id DESC”;
        $result = $this->dosql($sql, true) or die(“Mysql error or database connection error at “.__LINE__);
        $count = mysql_num_rows($result);
        if($count > 0 ){
            while($row = mysql_fetch_assoc($result)){
                if(!array_key_exists($row['id'], $this->_searchresult)){        //als de key niet al bestaat (anders dubbele zoekresultaten)
                    $this->_searchresult[$row['id']] = 5;
                    //echo “<br />1 Found: “.$row['phone_model'].” id: “.$row['id'].” score: 10″;
                }    
            }    
        }
        
        $likecol = “”;$sterm = substr($sterm,1);
        foreach($this->_searchfields as $col){
            $likecol .= “$col LIKE ‘%”.$sterm.”%’ OR “;
        }
        $likecol = substr($likecol,0,-4);
        $sql = “SELECT id,phone_model FROM “.$this->_zoekdb.” WHERE $likecol ORDER BY id DESC”;
        $result = $this->dosql($sql, true) or die(“Mysql error or database connection error at “.__LINE__);
        $count = mysql_num_rows($result);
        if($count > 0 ){
            while($row = mysql_fetch_assoc($result)){
                if(!array_key_exists($row['id'], $this->_searchresult)){        //als de key niet al bestaat (anders dubbele zoekresultaten)
                    $this->_searchresult[$row['id']] = 5;
                    //echo “<br />1 Found: “.$row['phone_model'].” id: “.$row['id'].” score: 10″;
                }    
            }    
        }
        
        //now check database per word (in case of multiple words)
        if(count($this->_zoekterm) > 1){
            foreach($this->_zoekterm as $term){
                $term = $this->FullTermClean($term);
                if(strlen($term) > 2){ //like with term smaller then 3 characters is useless
                    $likecol = “”;
                    foreach($this->_searchfields as $col){
                        $likecol .= “$col LIKE ‘%”.$term.”%’ OR “;
                    }
                    $likecol = substr($likecol,0,-4);
                    $sql = “SELECT id,phone_model FROM “.$this->_zoekdb.” WHERE $likecol ORDER BY id DESC”;
                    $result = $this->dosql($sql, true) or die(“Mysql error or database connection error at “.__LINE__);
                    $count = mysql_num_rows($result);
                    if($count > 0 ){
                        while($row = mysql_fetch_assoc($result)){
                            if(!array_key_exists($row['id'], $this->_searchresult)){        //als de key niet al bestaat (anders dubbele zoekresultaten)
                                $this->_searchresult[$row['id']] = 4;
                                //echo “<br />1 Found: “.$row['phone_model'].” id: “.$row['id'].” score: 4″;
                            }    
                        }    
                    }
                }
            }
        }
        $time = microtime();
        $time = explode(” “, $time);
        $time = $time[1] + $time[0];
        $this->_endtime = $time;
        return $this->_searchresult;        
    }
    
    
    function get_results_fulltext(){                        
        $searchterms = “”;
        if(count($this->_zoekterm) > 1){
            foreach($this->_zoekterm as $term){
                $searchterms .= “+$term “;    //exact matches ‘term1′ AND ‘term2′ etc..
            }
        }else{
            $searchterms = $this->_fullterm;
        }

        $matchcols = “”;
        foreach($this->_searchfields as $col){
            $matchcols .= “$col,”;
        }
        $matchcols = substr($matchcols,0,-1);$likecol = substr($likecol,0,-4);
        
        $searchterm = substr($searchterms, 0, -1);
        $sql = “SELECT id,phone_model, MATCH ($matchcols) AGAINST (‘$searchterm’) as score FROM “.$this->_zoekdb.” WHERE MATCH ($matchcols) AGAINST (‘$searchterm’) >= 0.5 ORDER BY score DESC”;
        $result = $this->dosql($sql, true) or die(“Mysql error or database connection error at “.__LINE__);
        $count = mysql_num_rows($result);
        if($count > 0 ){                                                        //aah wat gevonden.
            while($row = mysql_fetch_assoc($result)){
                if(!array_key_exists($row['id'], $this->_searchresult)){        //als de key niet al bestaat (anders dubbele zoekresultaten)
                    $this->_searchresult[$row['id']] = $row['score'];
                    //echo “<br />1 Found: “.$row['phone_model'].” id: “.$row['id'].” score: “.$row['score'];
                }    
            }    
        }
        if($this->_debug === true){
            echo “<br />Sql1: $sql”;
            echo “<br />Results: “.count($this->_searchresult);
            foreach($this->_searchresult as $id => $score){ echo “<br />$id : $score | “; }
        }
        
        //whole term with spaces (space means OR)
        $sql = “SELECT id, MATCH ($matchcols) AGAINST (‘”.$this->_fullterm.”‘) as score FROM “.$this->_zoekdb.” WHERE MATCH ($matchcols) AGAINST (‘”.$this->_fullterm.”‘) >= 0.5 ORDER BY score DESC”;        
        $result= $this->dosql($sql) or die(“Mysql error or database connection error at “.__LINE__);
        $count = mysql_num_rows($result);
        if($count > 0 ){                                                        //aah wat gevonden.
            while($row = mysql_fetch_assoc($result)){
                if(!array_key_exists($row['id'], $this->_searchresult)){        //als de key niet al bestaat (anders dubbele zoekresultaten)
                    $this->_searchresult[$row['id']] = $row['score'];
                    //echo “<br />2 Found: “.$row['phone_model'].” id: “.$row['id'].” score: “.$row['score'];
                }    
            }    
        }
        if($this->_debug === true){
            echo “<br />Sql2: $sql”;
            echo “<br />Results: “.count($this->_searchresult);
            foreach($this->_searchresult as $id => $score){ echo “<br />$id : $score | “; }
        }        
        
        //remove last letter and incldue wildcard
        $newterm = substr($this->_fullterm, 0, -1).”*”;
        $sql = “SELECT id, MATCH ($matchcols) AGAINST (‘”.$newterm.”‘ IN BOOLEAN MODE) as score FROM “.$this->_zoekdb.” WHERE MATCH ($matchcols) AGAINST (‘”.$newterm.”‘ IN BOOLEAN MODE ) >= 0.5 ORDER BY score DESC”;
        $result= $this->dosql($sql) or die(“Mysql error or database connection error at “.__LINE__);
        //echo “<!– $sql //–>”;
        $count = mysql_num_rows($result);
        if($count > 0 ){                                                        //aah wat gevonden.
            while($row = mysql_fetch_assoc($result)){
                if(!array_key_exists($row['id'], $this->_searchresult)){        //als de key niet al bestaat (anders dubbele zoekresultaten)
                    $this->_searchresult[$row['id']] = $row['score'];
                    //echo “<br />3 Found: “.$row['phone_model'].” id: “.$row['id'].” score: “.$row['score'];
                }    
            }    
        }    
        if($this->_debug === true){
            echo “<br />Sql3: $sql”;
            echo “<br />Results: “.count($this->_searchresult);    
            foreach($this->_searchresult as $id => $score){ echo “<br />$id : $score | “; }
        }
                        
        //remove last 2 letters
        $newterm = substr($this->_fullterm, 0, -2).”*”;
        $sql = “SELECT id, MATCH ($matchcols) AGAINST (‘”.$newterm.”‘ IN BOOLEAN MODE) as score FROM “.$this->_zoekdb.” WHERE MATCH ($matchcols) AGAINST (‘”.$newterm.”‘ IN BOOLEAN MODE ) >= 0.5 ORDER BY score DESC”;
        $result= $this->dosql($sql) or die(“Mysql error or database connection error at “.__LINE__);
        //echo “<!– $sql //–>”;
        $count = mysql_num_rows($result);
        if($count > 0 ){                                                        //aah wat gevonden.
            while($row = mysql_fetch_assoc($result)){
                if(!array_key_exists($row['id'], $this->_searchresult)){        //als de key niet al bestaat (anders dubbele zoekresultaten)
                    $this->_searchresult[$row['id']] = $row['score'];
                    //echo “<br />3 Found: “.$row['phone_model'].” id: “.$row['id'].” score: “.$row['score'];
                }    
            }    
        }
        if($this->_debug === true){
            echo “<br />Sql4: $sql”;
            echo “<br />Results: “.count($this->_searchresult);    
            foreach($this->_searchresult as $id => $score){ echo “<br />$id : $score | “; }
        }        
        //echo “<br /><pre>”;
        //print_r($this->_searchresult);
        //echo “</pre>”;
        
        $time = microtime();
        $time = explode(” “, $time);
        $time = $time[1] + $time[0];
        $this->_endtime = $time;
        return $this->_searchresult;
    }
    
    function get_searchtime(){
        $totaltime = ($this->_endtime – $this->_begintime);
        $totaltime = round($totaltime, 4);                                                //round on 4 digits
        return $totaltime;
    }
    
    private function dosql($sql, $open, $close){
        if($open === true or !$this->_dblink){
            $this->_dblink = @mysql_connect($this->_host, $this->_dbuser, $this->_dbpass);        // Open DB Connection.
            if( !$this->_dblink ){
                exit(“Error: Connecting to database, “.mysql_error());
            }     
            if( ! mysql_select_db($this->_dbname, $this->_dblink) ){  // Select the database.
                exit(“Error: Selecting database, “. mysql_error());
            }
        }        
        $result = @mysql_query($sql,$this->_dblink);
        if(mysql_error() != “”){
            $datetime = date(‘F D \t\h\e jS Y’);
            $data = chr(10).”$datetime – Mysql error: ” . mysql_error() .chr(10).”Query: $sql”.chr(10);
            echo $data;
            //or
            //$this->LogError($data); #Make this function yourself
        }
        if($close === true){
            @mysql_close($dblink);
        }
        return $result;
    }    
}
?>

Sphinx Class (sphinxapi is included in the zip file for download above and also comes with the Sphinx installation):

<?PHP
###################################################
# Sphinx search class v0.1
# by Steven van der Peijl
# http://www.stevenvanderpeijl.com
#
# part of the Mysql2Search class v0.6
# To make this class work with Mysql2Search class set the index names in sphinx.conf to the name of the table you are searching in
#
# Requirements
# Sphinx installed and sphinx.conf configured correctly (e.g. /etc/sphinxsearch/sphinx.conf)
# More info at http://sphinxsearch.com
#
# There are still many many more Sphinx options to set and implement in this class
# sphinxsearch.com/wiki/doku.php?id=php_api_docs
###################################################
require_once(‘sphinxapi.php’); //comes with the sphinx install package in the api directory

class spinx_search{
    var $_sphinxobj;
    var $_searchquery;
    var $_host            = “localhost”;                    //sphinx host
    var $_port            = 9312;                            //sphinx port
    var $_errors        = array();
    var $_searchindex    = “*”;                            //all existing indexes from sphinx.conf!
    var $_errorlogfile    = “../admin/sphinxerr.log”;        //search errors
    var $_emailadmin    = “admin@yourdomain.com”;
    
    function spinx_search($searchquery){
        $this->_sphinxobj = new SphinxClient();
        $this->_sphinxobj->SetServer( $this->_host, $this->_port);
        $this->_sphinxobj->SetConnectTimeout( 1 );
        $this->_sphinxobj->SetArrayResult( true );
        $this->_searchquery = $searchquery;
    }
    
    function SetHost($host){
        $this->_host = $host;
    }
    
    function SetPort($port){
        $this->_port = $port;
    }
    
    # This function is to set importance of a columns for the search (set in the index of the sphinx conf file)
    # the input is an array which looks like array(“<columnname>”=><weightnr> , “<columnname>”=><weightnr>)
    # <weightnr> should be 100 in total (in %) e.g. array(‘title’ => 70, ‘body_text’ => 30)
    #
    function SetColumnWeight($weightarray){
        if(!is_array($weightarray)){
            return false;
        }
        $this->_sphinxobj->SetFieldWeights($weightarray);
    }
    
    #modes info: http://sphinxsearch.com/wiki/doku.php?id=sphinx_docs#matching_modes
    function SetmatchModeExt($mode){
        switch($mode){
            case “all”:
                $smode = “SPH_MATCH_ALL”; //matches all query words ;
                break;
            case “any”:
                $smode = “SPH_MATCH_ANY”; //matches any of the query words;
                break;                
            case “phrase”:
                $smode = “SPH_MATCH_PHRASE”; //matches query as a phrase, requiring perfect match;
                break;                
            case “boolean”:
                $smode = “SPH_MATCH_BOOLEAN”; //matches query as a boolean expression
                break;        
            case “extended”:
                $smode = “SPH_MATCH_EXTENDED”; //matches query as an expression in Sphinx internal query language
                break;    
            case “extended2″:
                $smode = “SPH_MATCH_EXTENDED2″;
                break;
            default:
                $smode = “SPH_MATCH_ANY”;
        }
        $this->_sphinxobj->SetMatchMode($smode);
    }
    
    #SetSearchLimits(0, 5); // Returns the first 5 results. “page 1″
    #SetSearchLimits(5, 5); // Returns the next 5 results. “page 2″
    #SetSearchLimits(50, 25, 60); // Returns the last 10 documents. This configuration limits the search to a maximum of 60 results.
    #SetSearchLimits(0, 25, 60, 5); // Returns the last 5 results of all 60 results. Setting the offset here to greater than 4 will return a blank result set.
    function SetSearchLimits($offset=0, $limit=20, $max=500, $cutoff=0){
        if(!is_numeric($offset) or !is_numeric($limit) or !is_numeric($max) or !is_numeric($cutoff)){
            $offset=0;$limit=20;$max=500;$cutoff=0;
        }
        $this->_sphinxobj->SetLimits($offset, $limit, $max, $cutoff);    
    }
    
    # See sphinx documentation on how to create an index
    # This is done in the ‘sphinx.conf’ file and is obligatory to do!
    # Default is set to ‘*’ which means it will use all indexes set in sphinx.conf
    function SetSearchIndex($index){
        $sindx = “”;
        if(is_array($index)){
            foreach($index as $ind){
                $sindx .= “$ind “;
            }
            
        }else{
            $sindx = $index;
        }
        $this->_searchindex = $sindx;
    }    
    
    function GetSearchResults(){
        $result = array();
        //echo “<br />query: “,$this->_searchquery.” index; “.$this->_searchindex;
        $searchresults = $this->_sphinxobj->Query( $this->_searchquery, $this->_searchindex);
        //echo “Sphinx class:<pre>”;
        //print_r($searchresults);
        //echo “</pre>”;
        if($searchresults == false){
            $result['status'] = ‘error’;
            $result['status_message'] = $this->_sphinxobj->GetLastError();
            $this->LogError($result['status_message']);
        }else{
            if ( $this->_sphinxobj->GetLastWarning() ){
                $result['status'] = ‘warning’;
                $result['status_message'] = $this->_sphinxobj->GetLastWarning();
                $this->LogError($result['status_message']);
            } else{
                $result['status'] = ‘success’;
            }
            $result['result_total'] = $searchresults['total'];
            $result['result_found'] = $searchresults['total_found'];
            $result['searchtime'] = $searchresults['time'];                    
            $result['keys'] = array();
            foreach ($searchresults["matches"] as $key => $docarr ){
                $result['keys'][] = $docarr['id'];    //these are the search results!
            }
        }
        //echo “Sphinx class:<pre>”;
        //print_r($result);
        //echo “</pre>”;
        return $result;    
    }
    
    function GetJsonResults(){
        $results = $this->GetSearchResults();
        return json_encode($results);
        //or
        //echo json_encode($results);    
    }

    private function LogError($error){
        $datetime = date(‘F D \t\h\e jS Y H:i:s’);;
        $this->_errors[] = “$datetime”.chr(10).__FILE__.”: $error”;
    }
    
    #Error report function
    # returns false if no errors are found
    # returns true if errors were set and reported per set option (options are email | logfile | echo)    
    function ReportErrors($option){
        if(count($this->_errors) > 0){
            switch($option){
                case “email”:
                    $this->MailError($this->_errors);    
                    break;
                case “logfile”:
                    $this->WriteErrorFile($this->_errors);    
                    break;
                case “echo”:
                    $this->EchoError($this->_errors);    
                    break;                
                default:
                    $this->MailError($this->_errors);
            }
            return true;
        }else{
            return false;
        }
    }
    
    private function MailError($errors){
        $errs = “”;
        foreach($errors as $key => $error){
            $errs = “Error $key: $error\n”;
        }
        mail($this->_emailadmin,”Sphinx search errors”,”Sphinx search errors on “.$_SERVER['HTTP_HOST'].” \nErrors: $errs\n\n”,”From: “.$this->_emailadmin);
    }
    
    private function WriteErrorFile($errors){
        $errs = “”;
        foreach($errors as $key => $error){
            $errs = “Error $key: $error”.chr(10);
        }
        if (!file_exists($this->_errorlogfile)) { $fp = @fopen($this->_errorlogfile, ‘wb’); }
        if (is_writable($this->_errorlogfile)) {
            $fp = @fopen($this->_errorlogfile, ‘ab’);
            if (is_writable($this->_errorlogfile)) {
            $lock = flock($fp, LOCK_EX);
                if ($lock) {
                    fwrite($fp, $errs);
                    flock ($fp, LOCK_UN);
                }
                fclose($fp);
                clearstatcache();
            }
        }//else{
        //    echo “Error: Writing errorlog to file no write access”;
        //}                
    }    
    
    private function EchoError($errors){
        foreach($errors as $key => $error){
            echo “<br />Error $key: $error”;
        }    
    }        
}
?>