1

Edit : my question is not a duplicate of how to highlight search results since i am trying to work with the query and not the query results. I know i could work with the query result and i even tried, but that create other issues with case insentive and accentuation. So, if there is a way to do it with mysql (which is my question), kind like this Mysql : Select query with like but with PDO prepared statement ... Please, keep this question open !

I have this kind of data in my database "mydb" :

id | name
---------
1  | abcdef
2  | bcdefg
3  | cdefgh
4  | defghi
5  | efghij

I do a PDO prepared query like this one :

$q = "SELECT * FROM `mydb` WHERE name LIKE :search ;";
$res = $cnx->prepare($q);
$res->bindValue(':search', '%'.$_POST['search'].'%');
$res->execute();

In my example, $_POST['search'] = 'defgh', so i get those results :

id | name
---------
3  | cdefgh
4  | defghi

I would like to know if there is a way, with mysql and PDO, to inject html code which encompasses the string searched with "LIKE" statement directly in the result ?

So the results would be :

id | name   | html
----------------
3  | cdefgh | c<mark>defgh</mark>
4  | defghi | <mark>defgh</mark>i
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Aurélien Grimpard
  • 918
  • 10
  • 24
  • also [replace()](https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_replace) if you really need to do it right in the query but I wonder why anyone would ever want to do it this way – Your Common Sense Aug 22 '23 at 15:02
  • 1
    what exactly you don't understand here? How to write `SELECT mydb.*, replace(name, :search, concat('', :search,'')) as name FROM mydb WHERE name LIKE :search`? – Your Common Sense Aug 22 '23 at 15:09
  • @YourCommonSense maybe a better duplicate would be [How do I use a LIKE clause in a PDO prepared statement?](https://stackoverflow.com/questions/13832941/how-do-i-use-a-like-clause-in-a-pdo-prepared-statement) – Ergest Basha Aug 22 '23 at 15:14
  • @ErgestBasha the OP already knows how to use LIKE clause in a PDO prepared statement – Your Common Sense Aug 22 '23 at 15:15
  • Ok, i could achieved it based on your comment @YourCommonSense but i had to do it this way `SELECT mydb.*, replace(name, :search, concat('', :search2,'')) as name FROM mydb WHERE name LIKE :search3` due to `Invalid parameter number`. You might want to add the answer ? – Aurélien Grimpard Aug 22 '23 at 15:21
  • As for the "why" ? I first tried to do it with the query results but i had issue with accented character, uppercase, lowercase ... since mysql handle all this, i find it so much simple and easier ! – Aurélien Grimpard Aug 22 '23 at 15:23
  • Only if you add a sensible title to the question. Right now it explicitly asks how to use LIKE clause in a PDO prepared statement and any replace stuff would be misleading. – Your Common Sense Aug 22 '23 at 15:24
  • Ok, how about now ? – Aurélien Grimpard Aug 22 '23 at 15:26
  • I reopened the question, you can add your answer – Your Common Sense Aug 22 '23 at 15:29
  • 1
    Actually this is your answer. Thanks for your help anyway :) – Aurélien Grimpard Aug 22 '23 at 15:30
  • By the way, replace in mysql is as case insensitive as replace in PHP. I don't see what you gained here – Your Common Sense Aug 22 '23 at 15:34
  • Yes case insensitive is not an issue in PHP, regex or specific php function can work with this. That's quite different for accentuation, when SQL LIKE has no problem with string like `état`/`etat` or `août`/`aout`, this is a pain with PHP, you end up with a huge function. – Aurélien Grimpard Aug 22 '23 at 15:40
  • But you have the same pain with replace() in SQL. What gives? – Your Common Sense Aug 22 '23 at 15:44

0 Answers0