MySQL WHERE LIKE不使用多个字段使用php和pdo绑定 [英] MySQL WHERE LIKE not working with multiple fields using php and pdo bind
问题描述
我有一个问题在这里与我的WHERE LIKE语句。理想情况下，我想要能够搜索多个术语（或只是1或其他）。现在，为了测试的目的，我在测试表单中选择了哪种类型的函数。
目的 ：
请忽略更新函数（我确定它是一个混乱像休息，但我还没有完成那里）。仍然尝试完成dFind（）函数。这个测试的目的是，我可以建立一个数据类，将创建一个类，将数据插入数据库，搜索数据库的数据并拉它，更新该数据。
$ b下面，如果我只是保持查询下来为1像dFind（）函数中的实例，它工作（名称是更重要的2，但我需要搜索其他字段一旦我得到这个工作）。如果我添加或手机LIKE：电话到查询，那么它不拉正确的数据（我得到的一切回来）。我在phpmyadmin测试我的查询，并且它工作正常，但我不知道是否如何处理查询本身或我不捕获的东西与php（我也试图添加和逃避它，但是没有帮助。）
你们有没有看到我在哪里出错了？提前致谢。此外，任何建议或方向实现我正在工作的功能是更欢迎。
INDEX.PHP ：
$这些方法将被合并到一个小型数据库中，用于设置，搜索和更新消费者。 b$ b
< \？php
require'incl / con.php';
require'incl / class.php';
？>
<！DOCTYPE html>
< html>
< head>< title>测试1百万< / title>
< / head>
< body>
< h3>使用类< / h3>拉取数据
< form method =POSTaction =index.php>
< table border =0>
< tr>
< td> ID（需要更新）：< / td>< td>< input type =textname =idmaxlength =4>< / td&
< / tr>
< tr>
< td>名称：< / td>< td>< input type =textname =name>< / td>
< / tr>
< tr>
< td>电话：< / td>< td>< input type =textname =phone>< / td>
< / tr>
< tr>
< td> Insert< input type =radioname =typevalue =insertchecked =checked> Find< input type =radioname =typevalue =find >更新< input type =radioname =typevalue =update>< / td>< td>< input type =submitvalue =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'请至少输入id和name字段。
return false;
}
}
} else {
echo'请在两个字段中输入数据并选择正确的选项。
}
？>
< / body>
< / html>
CON.PHP ：
< \？php
＃VARs
＃设置当前时区（主机是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; //我知道这已经被声明 - 在类中尝试看它是如何工作，这是确定。
private $ pw =MY_PW; //我知道这已经被声明 - 在类中尝试看它是如何工作，这是确定。
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目录（名称，电话）VALUES（：name，：phone））;
$ STH-> bindParam（'：name'，$ this-> name）;
$ STH-> bindParam（'：phone'，$ this-> phone）;
$ STH-> execute（）;
} catch（PDOException $ e）{
echo对不起，Dave，恐怕我不能这样做。< 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（）。\\\
，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对不起，Dave，恐怕我不能这样做。< 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（）。\\\
，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目录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对不起，Dave，恐怕我不能这样做。< 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（）。\\\
，FILE_APPEND）
$ DBH = null;
}
$ DBH = null;
}
}
- - - - - - - - - - - - - - - - - - - - - - - - 分解 - - - - - - - / strong>
使用@ mzedeler的建议（THANKS！）从下面的帖子，这是将dFind（）查询更改为：
SELECT *
FROM目录
WHERE名称LIKE：name
AND：name_provided = 1
或手机LIKE： phone
AND：phone_provided = 1
使用以下内容替换dFind它似乎工作：
$ STH-> bindValue（'：name'，'％'。$ this-> ;名称 。 '％'）;
$ STH-> bindValue（'：phone'，'％'。$ this-> phone。'％'）;
$ STH-> bindValue（'：name_provided'，empty（$ this-> name）？0：1）;
$ STH-> bindValue（'：phone_provided'，empty（$ this-> phone）？0：1）;
问题可能是
在这种情况下，查询将是 SELECT * FROM [...] OR WHERE手机LIKE'%%'，它将始终返回所有内容。
解决方案：
另一种方法是更改此列中的值，如果没有提供或 hack alert！ 然后，如果 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: CON.PHP: CLASS.PHP: - - - - - - - - - - - - - - - - - - - - - - - - RESOLVED - - - - - - - - - - - - - - - - - - - - - - - - Using @mzedeler's suggestion (THANKS!) from the below post, which is to change the dFind() query to: Replaced bound data in dFind() with the following and it seems to be working:
The problem is probably that In that case, the query will be 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 And then bind 这篇关于MySQL WHERE LIKE不使用多个字段使用php和pdo绑定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
SELECT *
FROM目录
WHERE名称LIKE：name
AND：name_provided = 1
或手机LIKE：电话
AND：phone_provided = 1
$ this-> phone 被定义，则将：phone_provided 绑定为1，否则为0。同样：name_provided 。<\?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>
<\?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";
<\?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;
}
}
SELECT *
FROM directory
WHERE name LIKE :name
AND :name_provided = 1
OR phone LIKE :phone
AND :phone_provided = 1
$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);
$this->phone is empty when you run the query.SELECT * FROM [...] OR WHERE phone LIKE '%%' which will always return everything.SELECT *
FROM directory
WHERE name LIKE :name
AND :name_provided = 1
OR phone LIKE :phone
AND :phone_provided = 1
:phone_provided to 1 if $this->phone is defined, 0 otherwise. Likewise with :name_provided.
