Page 1 of 1

PHP search - broken?

Posted: Tue Jan 20, 2009 6:18 am
by faulknerm
Hi Guys,
I'm currently working on a website project which is to incorporate a search facility - to search a mySQL database.

I'm using the script below;


Search.php

Code: Select all

<?php
include 'searchclass.php';
$search = new DBsearch(false, 'justncou_companies', 'localhost', 'justncou_marc', 'password');
 
$search->table = 'UKC';
$search->perPage = 10;
 
if($search->Submitted()){
    if($search->Prime(Array(
        'Name', 'Contact'
    )))
        echo "The following errors occured: <ul><li>".join('</li><li>', $search->errors).'</li></ul>';
    else
        if( ! $search->Search())
            echo 'There was an error search for results.'.mysql_error();
};
 
$search->ShowForm();
 
if($search->Submitted() && ! count($search->errors)){
    $search->Pagination();
    while(FALSE !== ($row = $search->GetRow())){
        echo "<h3><b>{$row['id']}</b> - {$row['title']}</h3>
<p>{$row['message']}</p>";
    }
    $search->Pagination();
}
With searchclass.php

Code: Select all

<?php
 
/*
       Generic search script.
*/
 
if(__FILE__ == $_SERVER['SCRIPT_FILENAME'])
    die(highlight_file(__FILE__, true));
 
 
class DBsearch{
////////////////////////////////////// Most important is indexing - Don't worry about it
    static private $curIndex = 0;
////////////////////////////////////// We need variables.
    protected $connection;
    protected $db;
    protected $errors;
    private $numResults;
    private $formPrefix;
    private $termsRaw;
    private $termsArray;
    protected $query;
    protected $perPage = 1;
    protected $results = null;
    protected $table = 'test';
/////////////////////////////////////// Then magic functions (that call them selves as needed)
    public function __construct($connection, $db, $host='localhost', $username='root', $password='password'){
        $this->formPrefix = 'searchForm-'.++self::$curIndex;
        $this->query = $this->numResults = $this->errors = $this->results = $this->termsRaw = $this->termsArray = null;
        $this->GetSearchTerms();
        if($connection === false)
            $this->connection = mysql_connect($host, $username, $password)
                or self::Error('unable to make database connection');
        else
            $this->connection = $connection;
        $this->db = $db;
    }
    public function __set($name, $value){
        if($name == 'perPage' && is_int($value))
            $this->perPage = $value;
        if($name == 'table' && is_string($value))
            $this->table = $value;
    }
    public function __get($name){
        if(isset($this->$name))
            return $this->$name;
        return null;
    }
///////////////////////////////////// Public functions (for explicit calling)
    public function Search($order=false){
        if( ! is_array($this->errors))
            $this->Prime();
        if( ! empty($this->errors))
            return false;
        $sql = "SELECT *{$this->query}";
        if($order)
            $sql .= ' ORDER BY '.$order;
        $page = $this->GetPage()-1;
        $sql .= ' LIMIT '.($page*$this->perPage) . ', '.($page*$this->perPage+$this->perPage);
        $this->results = $this->Query($sql);
        return ($this->results !== FALSE);
    }
    public function GetRow(){
        if(is_null($this->results))
            return false;
        return mysql_fetch_assoc($this->results);
    }
    public function Prime($fields){
        if( ! is_array($fields))
            $fields = Array($fields);
        $this->errors = array();
        if(count($this->termsArray) == 0){
            $this->errors[] = 'No search terms specified.';
            return false;
        }
        $this->query = " FROM {$this->table} WHERE 0";
        foreach($this->termsArray as $term){// build a query
            $this->query .= "\nOR `".join("` LIKE '%".mysql_real_escape_string($term)."}%' OR `", $fields)
                        ."` LIKE '%{$term}%'";
        }
        if( ! ($this->GetNumResults()))
            $this->errors[] = 'No results found.';
        if($this->GetPage() > ceil($this->GetNumResults() / $this->perPage))
            $this->errors[] = 'Current Page higher than max page.';
        return ! empty($this->errors);
    }
    public function GetSearchTerms(){
        $this->termsRaw = filter_input(INPUT_GET, $this->__get('formPrefix'). '-q', FILTER_UNSAFE_RAW);
        $this->termsArray = preg_split('/\b/',
            str_replace('%', '\%', $this->termsRaw));// break on all word boundries, including tabs, new lines etc.
        foreach($this->termsArray as $k=>$v){
            $this->termsArray[$k] = trim($v);
            if(empty($this->termsArray[$k]))
                unset($this->termsArray[$k]);
        }
        array_unique($this->termsArray);
        return count($this->termsArray);
    }
    public function ShowForm(){
        echo <<<HTML
<form action='?' method='get' name='{$this->formPrefix}' id='{$this->formPrefix}'><fieldset>
    <input name='{$this->formPrefix}-q' id='{$this->formPrefix}-q' type="text"  value='{$this->termsRaw}'>
    <input name='{$this->formPrefix}-submit' id='{$this->formPrefix}-submit' type="submit" value="Search">
</fieldset></form>
HTML;
    }
    public function Submitted(){
        return (filter_input(INPUT_GET, $this->formPrefix. '-submit', FILTER_UNSAFE_RAW) == 'Search');
    }
    public function GetNumResults(){
        if(is_null($this->query))
            return false;
        if(is_null($this->numResults))
            return $this->numResults = (int) mysql_result($this->Query('SELECT Count(1) as `rows`'.$this->query), 0, 'rows');
        return $this->numResults;
    }
    public function GetPage(){
        $page = filter_input(INPUT_GET, $this->formPrefix. '-page', FILTER_VALIDATE_INT);
        return ($page < 1)?1:$page;
    }
    public function Pagination($items=3){
        $qs = preg_replace('/^&/', '',
            preg_replace("/&{$this->formPrefix}\\-page=[^&]&/", '&', '&'.$_SERVER['QUERY_STRING'].'&'))
            .$this->formPrefix.'-page=';
        $max = ceil($this->GetNumResults() / $this->perPage);
        $cur = $this->GetPage();
        $pages = Array();
        for($i = 1; $i <= $items; $i++)
            $pages[] = $i;
        for($i = $cur-$items; $i < $cur+$items; $i++)
            $pages[] = $i;
        for($i = $max-$items; $i < $max; $i++)
            $pages[] = $i;
        $pages = array_unique($pages);
        foreach($pages as $page){
            if($page < 1 || $page > $max)
                continue;
            if($page == $cur)
                echo "<b>{$page}</b>";
            else
                echo "<a href='?{$qs}{$page}'>{$page}</a>";
        }
    }
////////////////////////////////// And functions we can extend if need be
    protected function Error($error){
        die($error);
    }
////////////////////////////////// Finaly the one function I want private, I can't imagine much reason to open it up
    private function Query($query){
        $oldDb = mysql_result(mysql_query("SELECT DATABASE()", $this->connection),0);
        $rs = mysql_db_query($this->db, $query);
        for($i = 0; $i < $items; $i++){
            if($i == $cur)
                echo "<span>{$i}</span>";
            else
                echo "<a href={$qs}{$i}>{$i}</a>";
        }
        mysql_select_db($oldDb);
        return $rs;
    }
}
But I'm having problems with the results it returns. If I search on a term which would appear under the "name" field then it returns no results
The following errors occured:

* No results found.
* Current Page higher than max page.
If I search on a term which should appear under "contact" it returns the following -
1
-

-

1
Instead of the details I'm expecting.

Any idea's?

Cheers,

Marc

Re: PHP search - broken?

Posted: Tue Jan 20, 2009 10:36 am
by pickle
Best thing to do is output the query just before its run - see if its what you expect.

Re: PHP search - broken?

Posted: Tue Jan 20, 2009 11:05 am
by faulknerm
pickle wrote:Best thing to do is output the query just before its run - see if its what you expect.
Thanks.

Can I be really thick now, and say that I haven't got a clue how to do that? Would I "echo" somewhere in one of the php files?

Also, I wouldn't have a clue what the query should look like...

Sorry, I should have said in my post that I'm a complete php n00b. HTML I can manage, but php throws me completely.


Cheers,

Marc

Re: PHP search - broken?

Posted: Tue Jan 20, 2009 11:44 am
by pickle
Line 60 of your searchclass.php file. echo $sql.