StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL WHERE LIKE not working with multiple fields using php and pdo bind
text
Body
copied!<p>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.</p> <p><strong>Purpose</strong>: 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.</p> <p><strong>In regards to dFind()</strong>: 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).</p> <p>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.</p> <p><strong>INDEX.PHP</strong>:</p> <pre><code><\?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> </code></pre> <p><strong>CON.PHP</strong>:</p> <pre><code><\?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"; </code></pre> <p><strong>CLASS.PHP</strong>:</p> <pre><code><\?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; } } </code></pre> <p><strong>- - - - - - - - - - - - - - - - - - - - - - - - RESOLVED - - - - - - - - - - - - - - - - - - - - - - - -</strong></p> <p>Using @mzedeler's suggestion (THANKS!) from the below post, which is to change the dFind() query to:</p> <pre><code>SELECT * FROM directory WHERE name LIKE :name AND :name_provided = 1 OR phone LIKE :phone AND :phone_provided = 1 </code></pre> <p>Replaced bound data in dFind() with the following and it seems to be working:</p> <pre><code> $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); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload