How to properly use Wildcard with CONCAT

Question

(Spoiler: The Title has nothing to do with what is wrong with the code.)

I'm creating a live-search system just to show the user possible event types already listed on my website. During my speculations I may have an error with Wildcard binding which I'm unable to see.

I tried using different types of "WHERE LIKE" statements, and most of them didn't work at all. Such as I tried using placeholder query (question mark) and that did not work at all. If I ran this query manually on my database I will get results which I'm expecting.

This is how my code looks, the variable $q is obtained using $_GET method.

$query = $pdo->prepare('SELECT DISTINCT EventCategory FROM Events
             WHERE EventCategory LIKE CONCAT(\'%\',:q,\'%\')');
$query->bindParam(":q", $q);
$query->execute();
$row = $query->fetch(PDO::FETCH_ASSOC);
while ($row = $query->fetchObject()) {
    echo "<div>  $row->EventCategory </div>";
}

The expected results would be: If the $q is equal to n, Meeting and Nightlife is returned. When $q is equal to ni, then Nightlife is only returned.

The search is NOT CASE SENSITIVE, N and n is treated equally.

The SHOW CREATE TABLE Events query returned the following:

  CREATE TABLE `Events` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `Name` varchar(100) NOT NULL,
 `Image` varchar(600) NOT NULL,
 `Date` date NOT NULL,
 `Description` varchar(1200) NOT NULL,
 `SpacesAvailable` int(11) NOT NULL,
 `EventCategory` varchar(50) NOT NULL,
 `Trending` varchar(30) DEFAULT NULL,
 `TrendingID` int(255) NOT NULL,
 `Sale` int(255) NOT NULL,
 PRIMARY KEY (`ID`)
)DEFAULT CHARSET=latin1

Images to show the operation of the website: https://i.stack.imgur.com/8nCkD.jpg Please if you are viewing the images the view from bottom to top. Thanks

You're maybe getting extra quotes where you don't want them. Try `WHERE EventCategory LIKE :q` and `bindParam(':q', '%' . $q . '%');` — Alex Howansky, Jan 04 '19 at 17:57
`$q = $_GET['something'];` is how you assign `$q`? That `concat` usage looks correct. — user3783243, Jan 04 '19 at 18:02
We'll be more help to you if you [edit] your question to show the results of `SHOW CREATE TABLE Events`. — O. Jones, Jan 04 '19 at 18:06
@AlexHowansky thanks for reply! Although, bindParam returns fatal error, to be precise : Cannot pass parameter 2 by reference .My $q is assigned using XMLhttp request — Adrian, Jan 04 '19 at 19:43
Thanks for that fast response! The error is fixed, but the issue still remains. I'm in process of uploading graphical evidence :P — Adrian, Jan 04 '19 at 19:46
I was going to add that, just saw it that family fun is not returned even when you give it an F. It's returned when "a" is provided, seems like it's skipping first letter in this case — Adrian, Jan 04 '19 at 19:58
But `n` is the last letter.. You searched for `n` in the image. If the issue is collation/character set related we should have the same results in the DB. Can you show use what happens when you execute `SELECT DISTINCT EventCategory FROM Events WHERE EventCategory LIKE CONCAT('%', 'n','%')` in the DB interface? — user3783243, Jan 04 '19 at 20:21
Try storing your query into a variable, then dumping the variable, to convince yourself the query is correct. What happens if you type the equivalent query into phpmyadmin or some other sql client program? — O. Jones, Jan 04 '19 at 20:25
@user3783243 https://imgur.com/a/lxJOi8t O. Jones, everything works when it's typed up manually, surprisingly — Adrian, Jan 04 '19 at 21:17
Okay, so we're narrowing it down. What if you define the `n` statically in the `bind`? `$query->bindParam(":q", 'n');` Also you can't tag 2 users, notifications are only sent to one user. @O.Jones — user3783243, Jan 04 '19 at 21:29
Uncaught Error: Cannot pass parameter 2 by reference when using bindParam instead of bindValue provides me correct output, Meeting and Nightlife. So this one works perfectly @user3783243 — Adrian, Jan 04 '19 at 21:35
It still doesn;t work sadly, although thanks ;( This is weirdest thing I've encountered — Adrian, Jan 04 '19 at 21:53

O. Jones · Answer 1 · 2019-01-04T18:09:54.910

1

I suspect the default collation in your EventCategory column is case-sensitive. That's why Ni and ni don't match in Nightlife.

Try this query instead.

'SELECT DISTINCT EventCategory FROM Events WHERE EventCategory COLLATE utf8_general_ci LIKE CONCAT(\'%\',:q,\'%\')'

Or, if your column's character set is not unicode but rather iso8859-1, try this:

'SELECT DISTINCT EventCategory FROM Events WHERE EventCategory COLLATE latin1_general_ci LIKE CONCAT(\'%\',:q,\'%\')'

This explains how to look up the available character sets and collations on MySQL.

How to change collation of database, table, column? explains how to alter the default collation of a table or a column. It's generally a good idea because collations are baked into indexes.

edited Jan 04 '19 at 18:09

answered Jan 04 '19 at 18:04

O. Jones

103,626
17
118
172

Sadly, it doesn't work at all. Although, my unicode is iso8859-1, as first option doesn't work ;P Please check my question again as I've updated it as you wanted. Thank you. – Adrian Jan 04 '19 at 19:39
The default for `latin1` is `latin1_swedish_ci`, so it is "Case Insensitive". Hence adding `COLLATE...` won't be a solution. (See `SHOW COLLATION LIKE 'latin1%';` to get the Default.) – Rick James Jan 04 '19 at 23:48

score 0 · Answer 2 · answered Jan 04 '19 at 18:24

0

In complement to the comprehensive answer by O.Jones, another, simpler solution would be to just perform a case-insensitive search, like :

'SELECT DISTINCT EventCategory 
 FROM Events 
 WHERE UPPER(EventCategory) LIKE CONCAT(\'%\',UPPER(:q),\'%\')'

answered Jan 04 '19 at 18:24

GMB

216,147
25
84
135

Thank you so much for your reply! But sadly, the above solution doesn't work. No change at all. Same end-results. Although thanks for your time! :D – Adrian Jan 04 '19 at 19:37
No, use the column's collation. – Rick James Jan 04 '19 at 23:48

score 0 · Accepted Answer · answered Jan 04 '19 at 23:51

0

The problem is not in LIKE, but in PHP and PDO. Stare at the 3 conflicting uses of $row in your code:

$row = $query->fetch(PDO::FETCH_ASSOC);
while ($row = $query->fetchObject()) {
echo "<div>  $row->EventCategory </div>"; }

Then review the documentation and examples. (Sorry, I'm not going to feed you the answer; you need to study to understand it.)

answered Jan 04 '19 at 23:51

Rick James

135,179
13
127
222

Oh dear Rick my saviour! I actually went to take a look at the docs, and I "think" that I've found the mistake. I was basically executing twice instead of once, my solution was to declare fetchObject in while loop, and remove the top one, as fetchObject already does what "fetch" does... so my mistake was "repetition". Thank you! – Adrian Jan 05 '19 at 17:28

How to properly use Wildcard with CONCAT

3 Answers3