php 使用 LIKE 的 PDO 搜索数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18877098/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PDO search database using LIKE
提问by ShadowZzz
I am trying to make a small search function to look at database using this code:
我正在尝试使用以下代码制作一个小型搜索功能来查看数据库：
$searchQ = 'Li';
$query = $connDB->prepare('SELECT * FROM topic WHERE topic_name LIKE '."':keywords'");
$query->bindValue('keywords', '%' . $searchQ . '%');
$query->execute();
if (!$query->rowCount() == 0) {
while ($results = $query->fetch()) {
echo $results['topic_name'] . "<br />\n";
}
} else {
echo 'Nothing found';
}
This return all of the items in database, not just the ones that are alike,
这将返回数据库中的所有项目，而不仅仅是相似的项目，
I then ran this SQL query:
然后我运行了这个 SQL 查询：
SELECT * FROM topic WHERE topic_name LIKE '%Li%';
and this ran as expected and returned the required result.
这按预期运行并返回所需的结果。
What am I missing?
我错过了什么？
回答by showdev
Remove the quotes from the placeholder and add a colon before your bind reference:
从占位符中删除引号并在绑定引用之前添加一个冒号：
$query = $connDB->prepare('SELECT * FROM topic WHERE topic_name LIKE :keywords');
$query->bindValue(':keywords', '%' . $searchQ . '%');
Here's my text example:
这是我的文本示例：
SQL
SQL
CREATE TABLE IF NOT EXISTS `items` (
`id` mediumint(9) NOT NULL auto_increment,
`name` varchar(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
INSERT INTO `items` (`id`, `name`) VALUES
(1, 'apple'),
(2, 'orange'),
(3, 'grape'),
(4, 'carrot'),
(5, 'brick');
PHP
PHP
$keyword='ap';
$sql="SELECT * FROM `items` WHERE `name` LIKE :keyword;";
$q=$dbh->prepare($sql);
$q->bindValue(':keyword','%'.$keyword.'%');
$q->execute();
while ($r=$q->fetch(PDO::FETCH_ASSOC)) {
echo"<pre>".print_r($r,true)."</pre>";
}
Output
输出
Array
(
[id] => 1
[name] => apple
)
Array
(
[id] => 3
[name] => grape
)
回答by Moeed Farooqui
You need to pass a single variable to bind_paramnot a concatenatedstring, because parameter twomust be a reference not a value.
您需要将单个变量传递给而 bind_param不是concatenated字符串，因为参数two必须是引用而不是值。
$keyword_to_search = '%' . $searchQ . '%';
$query->bindValue('keywords', $keyword_to_search);
回答by Mihai
You can only bind data literals. Try
您只能绑定数据文字。尝试
$searchQ = '%Li%'; OR $searchQ = '%'.Li.'%';
$query = $connDB->prepare('SELECT * FROM topic WHERE topic_name LIKE :keywords');
$query->bindValue('keywords', $searchQ );
回答by Mahdani Ismail
showdev thanks you tutorial
showdev 谢谢你的教程
SQL
SQL
CREATE TABLE IF NOT EXISTS 'news'
(`id` int(9) NOT NULL auto_increment,
`title` text NOT NULL,
PRIMARY KEY (`id`)
)
ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
insert into `items` (`id`, `title`) VALUES
(1, 'news title'),
(2, 'news title 2');
PHP
PHP
$connection = new PDO('mysql:host=localhost;dbname=YOUR_DATABASE_NAME','root','');
if (isset($_GET['store'])) {
$store = $_GET['store'];
if(!empty($store)){//condition you the remove karakter
$q=$connection->prepare("SELECT title FROM tbl_nes WHERE title LIKE :store");
$q->bindValue(':store','%'.$store.'%');
$q->execute();
while ($r = $q->fetch(PDO::FETCH_OBJ)) {
echo $r->title,"<br>";
}
}
}
OUTPUT
输出
1.news title
2.news title 2 ....
