MySQL WHERE LIKE not working with multiple fields using php and pdo bind

Question

I'm having an issue here with my WHERE LIKE statement. Ideally, I want to be able to search for multiple terms (or just 1 or the other). Right now, for testing purposes, I have this separated in my test form where I choose what type of function I'm running.

Purpose: Please ignore the update function for the moment (I'm sure it's a mess like the rest but I haven't finished up there yet). Still trying to finish the dFind() function. The purpose of this testing is so that I can build a data class that will create a class, insert data into the database, search the database for data and pull it, update that data. So far, every step is a learning curve for me, so please bear with me.

In regards to dFind(): Below, if I just keep the query down to 1 like instance within the dFind() function, it works (the name is the more important of the 2, but I need to search other fields once I get this working). If I add 'OR phone LIKE :phone' to the query, then it doesn't pull the right data (I get everything back). I tested my query in phpmyadmin and it worked fine though, so I'm not sure if it's how I'm treating the query itself or I'm not catching something with php (and I also tried adding ' and escaping it, but that didn't help either).

Do any of you see where I'm going wrong on this? Thanks in advance. Also, any recommendations or direction to achieve the functionality I'm working on is more then welcome. These methods will be incorporated into a small database for setting up, searching for and updating consumers.

INDEX.PHP:

<\?php

    require 'incl/con.php';
    require 'incl/class.php';

?>

<!DOCTYPE html>
<html>
<head><title>Test 1 Million</title>



</head>
<body>
<h3>Pull data using classes</h3>

<form method="POST" action="index.php">

    <table border="0">
        <tr>
            <td>ID (Required for update):</td><td><input type="text" name="id" maxlength="4"></td>
        </tr>
        <tr>
            <td>Name:</td><td><input type="text" name="name"></td>
        </tr>
        <tr>
            <td>phone:</td><td><input type="text" name="phone"></td>
        </tr>
        <tr>
            <td>Insert<input type="radio" name="type" value="insert" checked="checked">Find<input type="radio" name="type" value="find">Update<input type="radio" name="type" value="update"></td><td><input type="submit" value="Submit"></td>
        </tr>
    </table>

</form>

<?

if ($_SERVER['REQUEST_METHOD'] == "POST") {

    $type = $_POST['type'];
    $name = $_POST['name'];
    $phone = $_POST['phone'];
    $id = $_POST['id'];

    $newData = new Data($name, $phone);

    if ($type == 'insert') {

        $newData->dInsert();

    } elseif ($type == 'find') {

        $newData->dFind();

    } elseif ($type == 'update') {
        if ($id != null && $name != null) {
            $newData->dUpdate($id,$name,$phone);
        } else {
            echo 'Please enter, at minimum, the id and name fields.';
            return false;
        }        
    }

} else {
    echo 'Please enter data in both fields and choose the correct option.';
}

?>


</body>
</html>

CON.PHP:

<\?php

# VARs
# set the current timezone (host is MST)
date_default_timezone_set("America/New_York");

#$host = "MY_HOST";
#$db = "MY_DB";
#$user = "MY_UN";
#$pw = "MY_PW";

CLASS.PHP:

<\?php

class Data {

    private $dsn = "DSN STRING";
    private $user = "MY_UN"; // I know this was already declared - was trying it within the class to see how it works, which does ok.
    private $pw = "MY_PW"; // I know this was already declared - was trying it within the class to see how it works, which does ok.
    private $opts = array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION );

    public $name;
    public $phone;

    public function __construct($n,$p) {
        $this->name = $n;
        $this->phone = $p;
    }

    public function dInsert() {                
        try {            
            $DBH = new PDO($this->dsn, $this->user, $this->pw, $this->opts);

            $STH = $DBH->prepare("INSERT INTO directory (name, phone) VALUES (:name, :phone)");

            $STH->bindParam(':name', $this->name);
            $STH->bindParam(':phone', $this->phone);

            $STH->execute();            
        } catch(PDOException $e) {            
            echo "I'm sorry, Dave. I'm afraid I can't do that.<br />";
            echo date("d/m/y : H:i:s", time()) . " - " . $e->getMessage();
            file_put_contents('PDOErrors.txt', date("d/m/y : H:i:s", time()) . " - " . $e->getMessage() . "\n", FILE_APPEND);
            $DBH = null;            
        }        
        $DBH = null;        
    }

    public function dFind() {                
        try {        
            $DBH = new PDO($this->dsn, $this->user, $this->pw, $this->opts);

            # $STH = $DBH->prepare('SELECT id, name, phone FROM directory WHERE name LIKE :name OR phone LIKE :phone');
            # $STH = $DBH->prepare("SELECT * from directory WHERE name LIKE CONCAT('%', :name ,'%') OR phone LIKE CONCAT('%', :phone ,'%')");
            $STH = $DBH->prepare("SELECT * from directory WHERE name LIKE :name OR phone LIKE :phone");

            $STH->bindValue(':name', '%' . $this->name . '%');
            $STH->bindValue(':phone', '%' . $this->phone . '%');

            $STH->execute();

            $STH->setFetchMode(PDO::FETCH_ASSOC);

            while($row = $STH->fetch()) {
                echo $row['id'] . " " . $row['name'] . ": " . $row['phone'] . "<br />";
            }            
        } catch(PDOException $e) {            
            echo "I'm sorry, Dave. I'm afraid I can't do that.<br />";
            echo date("d/m/y : H:i:s", time()) . " - " . $e->getMessage();
            file_put_contents('PDOErrors.txt', date("d/m/y : H:i:s", time()) . " - " . $e->getMessage() . "\n", FILE_APPEND);
            $DBH = null;            
        }        
        $DBH = null;        
    }

    public function dUpdate($id,$name,$phone) {

        $this->name = $name;
        $this->phone = $phone;

        try {        
            $DBH = new PDO($this->dsn, $this->user, $this->pw, $this->opts);

            $STH = $DBH->prepare('UPDATE directory SET name = :name, phone = :phone WHERE id = :id');

            $STH->bindValue(':id', $id);
            $STH->bindValue(':name', '%' . $name . '%');
            $STH->bindValue(':phone', '%' . $phone . '%');

            $STH->execute();

            $STH->setFetchMode(PDO::FETCH_ASSOC);

            while($row = $STH->fetch()) {
                echo $row['id'] . " " . $row['name'] . ": " . $row['phone'] . "<br />";
            }            
        } catch(PDOException $e) {            
            echo "I'm sorry, Dave. I'm afraid I can't do that.<br />";
            echo date("d/m/y : H:i:s", time()) . " - " . $e->getMessage();
            file_put_contents('PDOErrors.txt', date("d/m/y : H:i:s", time()) . " - " . $e->getMessage() . "\n", FILE_APPEND);
            $DBH = null;            
        }        
        $DBH = null;

    }

}

- - - - - - - - - - - - - - - - - - - - - - - - RESOLVED - - - - - - - - - - - - - - - - - - - - - - - -

Using @mzedeler's suggestion (THANKS!) from the below post, which is to change the dFind() query to:

SELECT *
  FROM directory
 WHERE name LIKE :name 
   AND :name_provided = 1
    OR phone LIKE :phone
   AND :phone_provided = 1

Replaced bound data in dFind() with the following and it seems to be working:

    $STH->bindValue(':name', '%' . $this->name . '%');
    $STH->bindValue(':phone', '%' . $this->phone . '%');

    $STH->bindValue(':name_provided', empty($this->name) ? 0 : 1);
    $STH->bindValue(':phone_provided', empty($this->phone) ? 0 : 1);

score 1 · Accepted Answer · answered Jun 11 '13 at 19:06

1

The problem is probably that $this->phone is empty when you run the query.

In that case, the query will be SELECT * FROM [...] OR WHERE phone LIKE '%%' which will always return everything.

Solution: leave out the phone criteria if not provided or (hack alert!) use a value that will never occur in that column.

Another way to do this is changing the query to something like

SELECT *
  FROM directory
 WHERE name LIKE :name 
   AND :name_provided = 1
    OR phone LIKE :phone
   AND :phone_provided = 1

And then bind :phone_provided to 1 if $this->phone is defined, 0 otherwise. Likewise with :name_provided.

answered Jun 11 '13 at 19:06

mzedeler

4,177
4
28
41

Thanks. Would it make more sense to only append the needed search fields to the query when not empty? Maybe something like: if ($_POST['name'] && !$_POST['phone']) { do this querey } elseif ... Would that be safer from injection attacks or more prone to it? – SiLeNCeD Jun 11 '13 at 19:21
1

I'd go with the third approach above. Programatically building SQL statements is tedious and error prone, so using a static one is what I prefer. – mzedeler Jun 11 '13 at 19:24
Thanks again... I'll give it a shot and get back to you. I appreciate the help. – SiLeNCeD Jun 11 '13 at 19:51
I added another section to my post above @mzedeler. I used the query you suggested and added a custom if statement. Seems to be working. Do you agree with the altered solution using the if? – SiLeNCeD Jun 11 '13 at 20:50
1

It looks right, but I'd still try to shorten the code using ``$STH->bindValue(':phone_provided', empty($this->phone) ? 0 : 1);``. – mzedeler Jun 11 '13 at 21:32
Nice... Thanks again.. I never think to use that where I need to. – SiLeNCeD Jun 11 '13 at 21:42

MySQL WHERE LIKE not working with multiple fields using php and pdo bind

1 Answers1

Linked