PX : code

Class QueryGenerator by Ed Williams
Download this code


<?php

/*****************************************************************************
 Class Query v.1.1
******************************************************************************

 Author: edward@planet-three.co.uk

 Purpose:
 
        To add a logical query to the SearchEngine class. The logic can be 
        one of the following : AND , OR, NOT.

 Usage:     
        aquery = new Query("NOT", array("apple", "pear", "bannana"));
        
*****************************************************************************/

class Query {

//PRIVATE:

    
var $m_words;
    var 
$m_logic;

//PUBLIC

    //
    // Constructor  
    
    // logic: <AND | OR | NOT>
    // words: words to be searched for given logic
    
    
function Query($logic$words) {
    
        
$this->m_words $words;
        
$this->m_logic $logic;
    }   
    
    
// print attributes of query
    
    
function p() {
    
        echo 
"$this->m_logic";
        
reset($this->m_words);
        while(list(
$i$word) = each($this->m_words))
            echo 
":$word";
    }
};

/*****************************************************************************
 Class QueryGenerator v.1.2
******************************************************************************
 
 Author: edward@planet-three.co.uk
 
 purpose: 
        
        Generates selection query strings
 usage:     
        query1 = new Query("AND", array("rabbit", "horse", "cow"));
        query2 = new Query("OR",  array("dogs", "cats", "kangaroos"));
        query3 = new Query("NOT", array("sheep"));

        querygen = new QueryGenerator(array(query1, query2, query3));
        query = 
           querygen->GetQueryString("animal_table", "mamals_field", "ID_field");
        
        resultid = mysql_query(query);      
        while(row = mysql_fetch_row(resultid))
            echo "got row id ",row["ID_field"],"<br>";
        
*******************************************************************************/

class QueryGenerator {

    var 
$m_Querys;
    var 
$m_start;
    var 
$m_offset;  
    
    var 
$m_wordmatch;
    var 
$m_casematch;

//PUBLIC:

    //  querys   : array of Query objects
    //  wordmatch: only match whole words
 
    
function QueryGenerator($Querys$wordmatch=false$casematch=false) {
    
        
$this->m_Querys $Querys;
        
$this->m_wordmatch $wordmatch;
        
$this->m_casematch $casematch;
        
        
$this->Restrict(0,0);
    }
    
    
// start  : start searching data at this pos
    // offset : number of records to search after pos
    
    
function Restrict($start$offset) {
    
        
$this->m_start  $start;
        
$this->m_offset $offset;  
    }
    
    function 
GetRestrict(&$start, &$offset) {
    
        
$start $this->m_start;
        
$offset $this->m_offset;
    }
    
    
// table : database table name
    // searchfield: field(s) to search in for resultfield(s)
    // resultfield: field(s) retrieved from search match
    // NOTE: field must be separated by "," eg, "field1,field2"

    // RETURN: function returns resultfield
    
    
function GetQueryString($table$searchfield$resultfield="ID") {
    
        
// begin query string
        
$query_string "SELECT $resultfield FROM $table WHERE ";
    
        
// get the query string     
        
$query_string .= $this->BuildQueryString($table$searchfield);
    
        
// append limit     
        
if ($this->m_start || $this->m_offset) {
            
$query_string .= "LIMIT $this->m_start";    
            if(
$this->m_offset)          
                
$query_string .= ",$this->m_offset";
        }

        return 
$query_string;
    }   

    
// build Query string that will return the number of rows selected
            
    
function GetCountQueryString($table$searchfield) {
    
        
// begin query string
        
$query_string "SELECT count(*) FROM $table WHERE ";
    
        
// get the query string     
        
$query_string .= $this->BuildQueryString($table$searchfield);
                        
        return 
$query_string;
    }       
    
//PRIVATE:

    
    // main function that generates the entire query
    
function BuildQueryString($table$searchfield) {
                        
        
// parse searchfields
        
$searchfields explode(","$searchfield);     
        if(!
is_array($searchfields)) 
            
$searchfields=array($searchfield);
                    
        
// build query for each searchfield
        
while ($searchfield current($searchfields)) {
            
            
// uppercase do case insensitive man
            
if (!$this->m_casematch)
                
$searchfield "UPPER(".$searchfield.")";
                    
            
$firstquery true;
            
$query_string .= "(";
                                
            
// build current query
            
reset($this->m_Querys);                         
            while(
$query current($this->m_Querys)) {                          
                
                
// append logic         
                
if ($firstquery)
                    
$firstquery=false;
                else if (
$query->m_logic == "NOT")
                    
$query_string.="AND";
                else
                    
$query_string.=$query->m_logic;                                 
                        
                
// append where clause
                
$query_string .= 
                
$this->BuildWhereClause($searchfield$query);                                              
                
next($this->m_Querys);
            }
            
$query_string .=")";                                
            if (
next($searchfields))
                
$query_string .= "OR";  
        }                                   
        return 
$query_string;
    }
    
    
// build where clause
    
function BuildWhereClause($searchfield$query) {
                
        
$query_string.="(";                                     
        while(
$word current($query->m_words)) {               
                                    
            if (!
$this->m_casematch)
                
$word strtoupper($word);          
                                    
            if (
$this->m_wordmatch)
                
$search_type "REGEXP '[[:<:]]".$word."[[:>:]]'";
            else
                
$search_type "REGEXP '$word'";
                                                        
            if (
$query->m_logic == "NOT"
                
$query_string.="$searchfield NOT ";
            else
                
$query_string.="$searchfield ";
                
            
$query_string.= $search_type;                   
            if (
next($query->m_words))              
                
$query_string.=" OR ";
        }                                                               
        
$query_string.=")";                                                     
        return 
$query_string;
    }   
};  
?>

Comments or questions?
PX is running PHP 5.2.17
Thanks to Miranda Productions for hosting and bandwidth.
Use of any code from PX is at your own risk.