How to query preg_match_all results in foreach loop?

Question

So I am working on a user tagging system like twitter where users can tag each other in comments like so: "blablabla @username blablabla". I am using preg_replace to find and convert "@username" in a text into a clickable link, and preg_match_all when inserting to a database.

$text = preg_replace('/(^|\s)@(\w+)/', '$1<a href="profile.php?poet_id=$2" class="small">@$2</a>', $text);

And

    $sanitized_comment = trim(mysql_real_escape_string(htmlspecialchars($_POST['comment'])));

if (preg_match_all("/(^|\s)@(\w+)/", $sanitized_comment, $matches)) {
        $mentions = array_unique($matches[0]);

        foreach($mentions as $mention) {
            mysql_query("INSERT INTO `comments_mentions` SET `cm_comment_id` = @last_comment_id, `cm_mentioned` = '" . $mention . "', `cm_mentioner` = " . (int)$session_user_id . "");
        }
    }

This works fine.

What I'm trying to do now is to find and store the "user_id" of the users that have been tagged in the text, instead of their "username".

I have tried doing

    $sanitized_comment = trim(mysql_real_escape_string(htmlspecialchars($_POST['comment'])));

if (preg_match_all("/(^|\s)@(\w+)/", $sanitized_comment, $matches)) {
    $mentions = array_unique($matches[0]);

    foreach($mentions as $mention) {
        $user_id_from_username = mysql_fetch_assoc(mysql_query("SELECT `user_id` FROM `users` WHERE lower(`username`) = lower(substr('" . $mention . "', 2))"));
        mysql_query("INSERT INTO `comments_mentions` SET `cm_comment_id` = @last_comment_id, `cm_mentioned` = " . $user_id_from_username . ", `cm_mentioner` = " . (int)$session_user_id . "");
    }
}

But this only inserts one row (the first value) into the database, even if several people were tagged.

How do I properly query the "user_id" of each mentioned users "username", and then store these ids in rows (multiple if several people were tagged) in my database?

Answer 1

Try var_dump($matches) before $mentions = array_unique($matches[0]);. I am not sure, but I would expect the usernames to be stored in $matches[2].

You could also give (\w+) a name to make things easier. The pattern should look something like:

/(^|\s)@(?P<username>\w+)/

When doing preg_match_all you can get the usernames like this:

$mentions = array_unique($matches['username']);

Also what @CharlotteDunois said in the comment. Using mysql is bad. Use PDO or mysqli.