Replace @mentions in text with clickable text including related ids fetched from a database table

Question

I want to be able to click on each @.... and go to their specific page so I'm getting the id's from the database for each match in the text in order to link to the match's page. I do get an array of id's from the foreach loop but when I am using preg_replace in the foreach loop, I am getting the same id inserted for multiple values. I'm stuck and have tried many different variations but no luck yet.

$text = "I went to the dog park yesterday and saw @dog4 playing with @dog8 and @dog3 drinking water.";

public function getLinks($text) {

    preg_match_all("/@([\w]+)/", $text, $matches);

if ($matches) {
    $result = array_values($matches[1]);
}

$sql = "SELECT dogId FROM dogs WHERE dogName = :dogName";

foreach ($result as $dogName) {
    $stmt = $this->con->prepare($sql); 
    $stmt->execute(array(":dogName" => $dogName));
    $data = $stmt->fetch(PDO::FETCH_ASSOC);
    
    $i = 0;
    if (!empty($data)) {
        
        foreach ($data as $dogId) {
            $i++;
            // It's working here.  I'm getting an array of dogIds
            echo '<pre>'; print_r($data); echo '</pre>';
            Array
            (
                [dogId] => 4
            )
            Array
            (
                [dogId] => 8
            )
            Array
            (
                [dogId] => 3
            )
            if ($i == count($data)) {
                $pattern = "/@([\w]+)/";
                $dogPage = "<span onclick='openPage(\"dogs.php?id=$dogId\")' role='link' tabindex='0'>$0</span>";
                $dogLink = preg_replace($pattern, $dogPage, $text);

                // It's not working here.  I only get the last array value(3) inserted in $dogId for every match.
                echo '<pre>'; print_r($dogPage); echo '</pre>';
                "<span onclick='openPage(\"dogs.php?id=3\")' role='link' tabindex='0'>@dog4</span>"
                "<span onclick='openPage(\"dogs.php?id=3\")' role='link' tabindex='0'>@dog8</span>"
                "<span onclick='openPage(\"dogs.php?id=3\")' role='link' tabindex='0'>@dog3</span>"
            }           
        }                   
    } 
} return $dogLink
}

The resulting text that I'm getting is

I went to the dog park yesterday and saw @dog4(id=3) playing with @dog8(id=3) and @dog3(id=3) drinking water.

But what I'm looking to achieve is

I went to the dog park yesterday and saw @dog4(id=4) playing with @dog8(id=8) and @dog3(id=3) drinking water.

Thank you in advance!

I cannot add https://stackoverflow.com/a/48730001/2943403 to the list because it is not upvoted. When you use `\w` as the lone value of a character class, you don't need to wrap it in `[` and `]`. My answer shows the correct technique to replace via a lookup. — mickmackusa, Mar 05 '21 at 02:43
Once you have all `$matches`, construct a single query using `IN()` and comma-separated dog names in a prepared statement. (https://stackoverflow.com/a/920523/2943403) FetchAll with dognames as keys and ids as values (https://phpdelusions.net/pdo/fetch_modes#FETCH_KEY_PAIR) -- this becomes your lookup array. Then you can `preg_replace_callback()` your input string to append the ids using the lookup. — mickmackusa, Mar 05 '21 at 02:55
I did not hammer this page closed because it was a bad question. If you are not able to implement the advice provided, please ask a new question with your best attempt to implement these techniques and we'll see if we can help you further. I honestly do care if you get to a satisfactory resolution. — mickmackusa, Mar 05 '21 at 04:12
Assuming you can structure your db data into a flat associative array... https://3v4l.org/JFuGS — mickmackusa, Mar 05 '21 at 05:09
Thank you for the links and the suggestions. I'm still learning PHP so I'll try out the solutions listed. — SimplyDave, Mar 05 '21 at 18:41
This is not a bad question and you showed a fair amount of effort. I have upvoted. I wish more people would show this level of effort. — mickmackusa, Mar 05 '21 at 20:17
How are you going with this task @Sim ? Stuck anywhere? If you give me a 3v4l.org demo of your code, I can have a look after work. — mickmackusa, Mar 08 '21 at 22:46
Thanks for checking in @mickmackusa . Still no luck yet. I've been researching and trying preg_replace_callback and creating other functions. I'm still getting the same results as posted. I'll try to post my code on 3v4l.org in the next day or so and will let you know when I have. Really appreciate the offer for that too. This is a good exercise because I'm learning things that I may not have learned if I had just gotten the answer right away. — SimplyDave, Mar 09 '21 at 22:33

score 0 · Accepted Answer · answered Mar 24 '21 at 13:09

First scan your input text for all @mentions and build a flat array of values without the leading "@" (\K means forget any previously matched characters -- the @ in this case). Extract all of the rows from the dogs table which are in the generated list and convert them into a lookup array with dogName as keys and dogId as values.

$count = preg_match_all('/@\K\w+/', $text, $mentions);
if ($count) {
    $in  = str_repeat('?,', $count - 1) . '?';
    $stmt = $db->prepare("SELECT dogName, dogId FROM dogs WHERE dogName IN ($in)");
    $stmt->execute($mentions[0]);
    $lookup = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
}

Demo of generating $mentions: https://3v4l.org/O9v37
PDO references: https://stackoverflow.com/a/14767651/2943403 & https://phpdelusions.net/pdo/fetch_modes#FETCH_KEY_PAIR

Then you can read your input text for @mentions again using /@(\w+)/ as the first parameter of preg_replace_callback(). Within the callback's custom function scope, swiftly check if the matched mention exists as a key in the lookup using isset() -- if so, replace, if not, don't change the text.

$text = "I went to the dog park yesterday and saw @dog4 playing with @dog8 and @dog3 drinking water -- poor @dog33.";

$lookup = [
    'dog4' => 4,
    'dog8' => 8,
    'dog3' => 3,
];

echo preg_replace_callback(
         '/@(\w+)/',
         function ($m) use($lookup) {
             return isset($lookup[$m[1]])
                        ? "<span onclick='openPage(\"dogs.php?id={$lookup[$m[1]]}\")' role='link' tabindex='0'>{$m[0]}</span>"
                        : $m[0];
         },
         $text
     );

Demo: https://3v4l.org/7Z2Wp

Output:

I went to the dog park yesterday and saw <span onclick='openPage("dogs.php?id=4")' role='link' tabindex='0'>@dog4</span> playing with <span onclick='openPage("dogs.php?id=8")' role='link' tabindex='0'>@dog8</span> and <span onclick='openPage("dogs.php?id=3")' role='link' tabindex='0'>@dog3</span> drinking water -- poor @dog33.

Thank you very much, this works perfectly! This solution is really straightforward and to the point. Thank you for the explanations too because they are really helpful. — SimplyDave, Mar 26 '21 at 04:37
I had been trying some of the links that you referred me too, but just couldn't quite figure it out. I always ended up with only one id being returned. Again, really appreciate it @mickmackusa . — SimplyDave, Mar 26 '21 at 04:44

Replace @mentions in text with clickable text including related ids fetched from a database table

1 Answers1