PDO Search same keyword in multiple columns

Question

considering SQL injections and some other features, I'm shifting from mysqli to PDO. But first of all, I want to convert all functions that I already had in my mysqli to PDO, so that I can move on to prepared statements and further security options.

What I currently have is a keyword search in one column. What I want to do is searching for the same keyword in multiple columns of the same table using PDO.

Here is the code I currently have:

$keywordfromform = $_GET["keyword"];
                $keyword = "%$keywordfromform%";
                $sql = 'SELECT * FROM table
                        WHERE title LIKE ?';
                $stmt = $pdo->prepare($sql);
                $stmt->execute([$keyword]);
                $entries = $stmt->fetchAll();

In mysqli, I just used WHERE CONCAT_WS(' ', title, author, text, year), but with PDO it's not working. I considered using WHERE title LIKE ? OR author LIKE ? OR text LIKE ? OR year LIKE ?, but this doesn't work either. I suspect that I need to use some sort of $stmt->bind_param(). However, there are mainly mysqli oriented questions and answers online and I don't get it running.

I would appreciate your help!

The given code contains only one condition - why not add the other ones? — Nico Haase, Dec 28 '20 at 10:43
Neither mysqli nor PDO change your SQL. They are only means to execute it. What does it mean that `WHERE CONCAT_WS(title, author, text, year)` doesn't work with PDO? — Dharman, Dec 28 '20 at 14:16
I had a typo there. In mysqli I used `$sql = "SELECT * FROM table WHERE CONCAT_WS(' ', title, author, text, year) LIKE '%" . $keywordfromform . "%';` — Fish, Dec 28 '20 at 15:00
`SELECT * FROM table WHERE CONCAT_WS(' ', title, author, text, year) LIKE ?` should still work both in mysqli and in PDO. Then you only need to bind the value once. — Dharman, Dec 28 '20 at 15:01
You can use Union. Union performance is better than OR. It's can help you: https://stackoverflow.com/questions/9730203/union-select-in-pdo-not-working — Amin Maleki, Dec 28 '20 at 15:04

score 1 · Accepted Answer · edited Dec 28 '20 at 15:03

1

I solved my own problem like this:

$keywordfromform = $_GET["keyword"];
$keyword = "%$keywordfromform%";
$sql = 'SELECT * FROM table
WHERE author LIKE ? OR title ? OR text LIKE ?';
$stmt = $pdo->prepare($sql);
$stmt->execute(array($keyword, $keyword, $keyword));
$entries = $stmt->fetchAll();

Explanation: I think the problem was, that for each ? I needed to bind a $keyword. And I did know how to combine them. Then I looked it up on php.net and realised that I may just need to add array().

edited Dec 28 '20 at 15:03

Dharman

30,962
25
85
135

answered Dec 28 '20 at 14:21

Fish

43
8

If you are trying to learn PHP and PDO here is good tutorial. It is much better than the manual. https://phpdelusions.net/pdo – Dharman Dec 28 '20 at 14:52

azibom · Answer 2 · 2020-12-28T14:41:30.043

It should work for you:

<?php
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$stmt = $conn->prepare("SELECT * FROM testDB  WHERE (firstCol LIKE :text) OR (secondCol LIKE :text) OR (thirdCol LIKE :text)");
$likeField = "%test%";
$stmt->bindParam(':text', $likeField);
$stmt->execute();

// set the resulting array to associative
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);

PDO Search same keyword in multiple columns

2 Answers2