PHP string highlight from mySql proximity search

Question

I would like to highlight the part of text that this query found:

Exemple 1:

SELECT * FROM myTable WHERE columnA regexp 'work.{0,10}john.{0,10}smith'

Highlight: "I have been working for John Joe Smith for the last 5 years"

Exemple 2:

SELECT * FROM myTable WHERE columnA regexp 'jo.{0,10}ba.{0,10}tur'

Highlight: "I have been working for Joseph Balsora Turgeon for the last 5 years"

I need to find the start position for highlighting in the string, I can figure out the end no problem.

I don't have a clue even after some research.

Thanks, Andy

Answer 1

Just apply the same regular expression in php. E.g.:

<?php 
  $str = "I have been working for John Joe Smith for the last 5 years";
  // Assuming you get the $str from the database

  $highlighted = preg_replace('/work.{0,10}john.{0,10}smith/iu', '<b>\0</b>', $str);

  print $highlighted;
  // I have been <b>working for John Joe Smith</b> for the last 5 years

Note, you need to enclose the regular expression in some pair of symbols (like /.../ in the example above). In the end there are modifiers. i allows to perform case-insensitive match, u allows to work with Unicode strings/

UPD

also probably you can perform the same replacement directly in MySQL:

select REGEXP_REPLACE('I have been working for John Joe Smith for the last 5 years','work.{0,10}john.{0,10}smith','<b>\\0</b>') ...