PDO Search with Like

[joshmac]

I have this search method that doesn't seem to work, and I am not sure why. I need an extra pair of eyes to see why my search query brings back false results. Thanks in advance.

public function search($data) {
        $person = $data['person'];
        $bind = [ ":person" => "%$person%" ];

        $q = DB::inst()->query( "SELECT personID,fname,lname,uname 
                FROM 
                    person 
                WHERE 
                    (CONCAT(fname,' ',lname) LIKE :person 
                OR 
                    CONCAT(lname,' ',fname) LIKE :person 
                OR 
                    CONCAT(lname,', ',fname) LIKE :person) 
                OR 
                    fname LIKE :person 
                OR 
                    lname LIKE :person 
                OR 
                    uname LIKE :person 
                OR 
                    personID LIKE :person",
                $bind
        );
        error_log(var_export($q,true));

        foreach($q as $r) {
            $array[] = $r;
        }
        return $array;
    }

[mmcdonald]

Would be great to have an error aswell...?

[joshmac]

Ok....here is the error.

[14-Sep-2013 13:29:34 America/New_York] false

[mmcdonald]

Joshmac - that's not a great error at all :)

Can you catch a clearer error with something like:

catch (PDOException $e){
    echo $e->getMessage();
}

after your query?

[joshmac]

That is already implemented in the database class. So the above error is all I get.

public function query($sql, $bind = false) {
        $this->error = '';

        try {
            if($bind !== false) {
                return $this->init($sql, $bind);
            } else {
                $this->result = $this->db->query($sql);
                return $this->result;
            }
        } catch (\PDOException $e) {
            $this->error = $e->getMessage();
            echo $this->error;
        }
    }

[joshmac]

I made an adjustment to the first method that is called in the query method, and the error message is now this which causes and unnecessary error.

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'et_error' doesn't existSQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':)' at line 1SQLSTATE[42S02]: Base table or view not found: 1146 Table 'et_error' doesn't existSQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':)' at line 1SQLSTATE[HY093]: Invalid parameter numberSQLSTATE[42S02]: Base table or view not found: 1146 Table 'et_error' doesn't existSQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':)' at line 1SQLSTATE[42S02]: Base table or view not found: 1146 Table 'et_error' doesn't existSQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':)' at line 1SQLSTATE[42S02]: Base table or view not found: 1146 Table 'et_error' doesn't existSQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':)' at line 1

I have this search method that doesn't seem to work, and I am not sure why. I need an extra pair of eyes to see why my search query brings back false results.

@joshmac

I will buy a new pair of glasses for you just to let you see why your search method didn't work in the first.

Look closely why is there a extra comma on line 13?

Try to remove that and run it again. What error do you have now? It should have a different error depending what is it.

[pritaeas]

Look closely why is there a extra comma on line 13?

That comma is part of a string.

Base table or view not found: 1146 Table 'et_error' doesn't exist

Where is this table used?

Thank you for downvoting everyone. It doesn't really matter if noone can answer Josh question! If anyone has a good explanation why the query is not working please explain if not, everyone on this thread is pointless because NOONE has an answer to Josh question!

Continue downvoting!

[pritaeas]

The problem is that the query he is showing is not the one causing the issue.

[joshmac]

I should have never posted that error message because it has nothing to do with the issue at hand, so please ignore it and instead investigate the query and the first error message I posted. Thanks again for everyone's help.

[pritaeas]

Your query is fine. The problem may be in the query method you are calling. Do other queries work, or is it just this one?

false is not an error message. Are you using PDO? I've tried this query on my machine and it works.

In your own function query() you call init() and then in else branch PDO::query(). PDO::query() returns a PDOstatement object. However if the query fails, it returns FALSE.

I usually use PDO::query() this way:

    $sql = 'select a, b from mytable';
    foreach ($db->query($sql) as $row) {  /*db is PDO con. object*/ 
       print $row['a'] . "\t" . $row['b'] . "\n";
    }

If a SQL statement has parameters (placeholders), I usually work with prepare(), bindParam() and execute().

[joshmac]

@pritaeas yes, I am using PDO and all queries work except this one. This is the only one that returns false, and yes, I know that false is not an error message, but I have all messages printed to an error log and this is the only thing that appears.

@1stDAN, that method in the DB class allows someone to run a straight query if they would like. However, if they are binding parameters ($bind returns true), then the first part will run, otherwise the second part will run instead. If I run the query without binding parameters, it works. However, that would open it up to Cross Site Scripting (XSS). If there is a simpler way to do the query above, by all means please share.

Here is the the init method:

    public function init($sql, $bind="") {
        $this->sql = trim($sql);
        $this->bind = $this->cleanup($bind);
        $this->error = '';

        try {
            $stmt = $this->db->prepare($this->sql);
            if($stmt->execute($this->bind) !== false) {
                if(preg_match("/^(" . implode("|", array("select", "describe", "pragma")) . ") /i", $this->sql))
                    return $stmt->fetchAll(\PDO::FETCH_ASSOC);
                elseif(preg_match("/^(" . implode("|", array("delete", "insert", "update")) . ") /i", $this->sql))
                    return $stmt->rowCount();
            }   
        } catch (\PDOException $e) {
            $this->error = $e->getMessage();
            $this->debug();
            return false;
        }
    }

[pritaeas]

I reconstructed your class (with a few modifications) based on what's posted above. This code works on my development machine. The only real difference is in the query: I've used id instead of PersonID:

<?php
class DB
{
    public $db;
    public $sql;
    public $result;
    public $error;
    public $bind;

    public function __construct()
    {
        $this->db = new PDO('mysql:dbname=mydatabase;host=localhost', 'myuser', 'mypassword');
        $this->db->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
    }

    public function cleanup($bind)
    {
        return $bind;
    }

    public function debug()
    {
    }

    public function init($sql, $bind = "")
    {
        $this->sql = trim($sql);
        $this->bind = $this->cleanup($bind);
        $this->error = '';
        try
        {
            echo "1\n";
            $stmt = $this->db->prepare($this->sql);
            if ($stmt->execute($this->bind) !== false)
            {
                echo "2\n";
                if (preg_match("/^(" . implode("|", array ("select", "describe", "pragma")) . ") /i", $this->sql))
                {
                    echo "3\n";
                    return $stmt->fetchAll(\PDO::FETCH_ASSOC);
                }
                elseif (preg_match("/^(" . implode("|", array ("delete", "insert", "update")) . ") /i", $this->sql))
                {
                    echo "4\n";
                    return $stmt->rowCount();
                }
                else
                {
                    echo "5\n";
                }
            }
            else
            {
                echo "6\n";
            }
        }
        catch (\PDOException $e) {
            $this->error = $e->getMessage();
            echo $this->error;
            echo "7\n";
        }
        echo "8\n";
        return false;
    }

    public function query($sql, $bind = false)
    {
        $this->error = '';
        try
        {
            if($bind !== false)
            {
                return $this->init($sql, $bind);
            }
            else
            {
                $this->result = $this->db->query($sql);
                return $this->result;
            }
        }
        catch (\PDOException $e)
        {
            $this->error = $e->getMessage();
            echo $this->error;
        }
        return false;
    }

    public function search($data) {
        $bind = [":person" => "%$data%"];
        $q = $this->query("SELECT *
                FROM
                    person
                WHERE
                    CONCAT(fname, ' ', lname) LIKE :person
                OR
                    CONCAT(lname, ' ', fname) LIKE :person
                OR
                    CONCAT(lname, ', ', fname) LIKE :person
                OR
                    fname LIKE :person
                OR
                    lname LIKE :person
                OR
                    uname LIKE :person
                OR
                    id LIKE :person",
            $bind
        );

        return $q;
    }
}

$db = new DB();
$result = $db->search('prit');
print_r($result);
?>

[joshmac]

@pritaeas, If that is the exact same code you used above, I think I can see why your code works and mind does not. I also have this attribute set:

$this->db->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);

When I comment out this line, it works. Since you are not setting this in your example above, that means it is set to true and that is why it works. I am not sure I want to set this to true even though you are still protected against XSS. Is there another way without setting it to true? If not, then I might have to just admit defeat and set it to true. Thanks again for your help.

[pritaeas]

This SO thread may help.

[joshmac]

Ok, then off it goes. Thanks.