PDO search database using LIKE

Question

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:

SELECT * FROM topic WHERE topic_name LIKE '%Li%';

and this ran as expected and returned the required result.

What am I missing?

Not an answer, but worth mentioning that `LIKE` queries with a leading `%` are *extremely* inefficient. The DB has to read the entire table to get the results. If you have (or expect to have) a large number of records in your table, you need to find an alternative way doing this query. — Spudley, Sep 18 '13 at 16:24
there are various options depending on your needs. Easy option: simply use tagging; ie a separate table with keywords. Then there's a `FULLTEXT` index and a `MATCHES` query; flawed, but workable. More complex: a DB search tool like Sphinx: blisteringly fast, but can be a pain to configure. — Spudley, Sep 18 '13 at 16:31
there's other options too, and frankly if it's a small table (<1000 records), then even `LIKE` can be "good enough". It's just something to be aware of. — Spudley, Sep 18 '13 at 16:32
For other options for full text search see my presentation [Full Text Search Throwdown](http://www.slideshare.net/billkarwin/practical-full-text-search-with-my-sql). — Bill Karwin, Sep 18 '13 at 18:20

showdev · Accepted Answer · 2013-09-18T16:35:40.683

18

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

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

$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
)

edited Sep 18 '13 at 16:35

answered Sep 18 '13 at 16:07

showdev

28,454
37
55
73

This still returns all of the content from the DB – ShadowZzz Sep 18 '13 at 16:09
Possible to provide the contents of your db table? – showdev Sep 18 '13 at 16:11
right now its place holders like: a red apple, a purple grape, a orange orange, a yellow banana. When i run it, it return all items. – ShadowZzz Sep 18 '13 at 16:13
1

Make suer that `$searchQ` has an actual value. If you're getting everything back, it's likely that the variable is empty or so generic that everything matches the search criteria. – Robbert Sep 18 '13 at 16:20
Yes, what is the output when you `var_dump($searchQ)`? – showdev Sep 18 '13 at 16:21
Unfortunately, I don't know of a PHP/MySQL/PDO sandbox to show an example. But I've added my example to my answer. – showdev Sep 18 '13 at 16:36
OK I used the code form that example and modified it to my context and it works, I have no clue, but i will mark yours as correct as it was the one that helped. Thank You! – ShadowZzz Sep 18 '13 at 16:49
Is it secure against SQL injections? – Pascal Goldbach Jul 02 '17 at 13:31
@PascalGoldbach That depends. You might find this post informative: [Are PDO prepared statements sufficient to prevent SQL injection?](https://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection) – showdev Jul 05 '17 at 19:37

score 0 · Answer 2 · answered Sep 18 '13 at 16:14

0

You need to pass a single variable to bind_param not a concatenated string, because parameter two must be a reference not a value.

$keyword_to_search = '%' . $searchQ . '%';
$query->bindValue('keywords', $keyword_to_search);

answered Sep 18 '13 at 16:14

Moeed Farooqui

3,604
1
18
23

But the OP is using `bindValue`, not `bindParam`. A concatenated string works for me... is there any documentation that it shouldn't? – showdev Sep 18 '13 at 16:17
Ok I did that, but still no luck, It still returns all of the contents of the DB – ShadowZzz Sep 18 '13 at 16:17

Mihai · Answer 3 · 2013-09-18T16:48:41.330

-1

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 );

edited Sep 18 '13 at 16:48

answered Sep 18 '13 at 16:41

Mihai

26,325
7
66
81

score -1 · Answer 4 · edited Jun 20 '20 at 09:12

showdev thanks you tutorial

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

$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 ....

PDO search database using LIKE

4 Answers4

SQL

PHP

Output

showdev thanks you tutorial

Linked