将文本中的@mentions替换为可点击的文本，包括从数据库表中获取的相关id

Question

我希望能够点击每个@....并去他们的特定页面，所以我从数据库中获取id的文本中的每个匹配，以便链接到匹配的页面。我确实从foreach循环中得到一个id数组，但是当我在foreach循环中使用preg_replace时，我得到了为多个值插入的相同id。我被困住了，试了很多不同的变化，但还没有运气。

$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
}

我得到的结果文本是

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

但是我想要达到的是

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

提前感谢!

Answer 1

首先扫描输入文本中的所有@提及，并构建一个不带前导"@"(K表示忘记任何先前匹配的字符——本例中的@)。从生成列表中的dogs表中提取所有行，并将其转换为查找数组，其中dogName作为键，dogId作为值。

$count = preg_match_all('/@Kw+/', $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);
}

生成$mentions的演示:https://3v4l.org/O9v37
PDO参考:https://stackoverflow.com/a/14767651/2943403 &https://phpdelusions.net/pdo/fetch_modes FETCH_KEY_PAIR

然后您可以再次使用/@(w+)/作为preg_replace_callback()的第一个参数来读取@提及的输入文本。在回调的自定义函数范围内，使用isset()快速检查匹配的提及是否作为查找中的键存在——如果存在，则替换，如果不存在，则不更改文本。

$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
);

演示:https://3v4l.org/7Z2Wp

输出:

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.