根据锚标记的存在加入查询？

Question

For the sake of ease, I simplified the script below.

TABLE: users

id   |  username     | location

12   |  best_chef    |  SF, CA
15   |  muffin_girl  | Miami, FL

TABLE: list

post_id   |  userid  |  post

  95      |   15     |  check out my new recipe!
  96      |   12     |  I love <a href="profile.php?id=$12">best_chef</a> 's recipes 

Here's the SQL query i'm using to display posts:

$table = query("SELECT id, post, post_id, username FROM list, users WHERE list.userid = users.id");

HTML:

<div class="update">
   <div class="user"><?php echo $usermame ?></div>  //"muffin_girl"
   <div class="post"><?php echo $post ?></div>     // "i love best_chef's recipes"       
</div>

What I am trying to do is to add a popup box that will display the location of the person tagged in a post WHEN the anchor of the post is hovered. Something like that:

 <div class="popup"><?php echo location ?></div

Question:

How can I join the anchor tag of the posts with the location of the users? Plus, not all posts contain an anchor tag.

Would this be feasible? i have a feeling i'm overthinking this.

EDIT

Desired output/pseudocode:

$sql = "SELECT post, username, location FROM list, users WHERE post like '%{TODO:anchor text}%' = users.username LIMIT 1";

Answer 1

I think I get what you're asking, basically if someone posts:

I love <a href="profile.php?id=$12">best_chef</a> 's recipes

then you want to intercept that <a href...>...</a>, extract the id ($12), and add a hidden DIV that will pop-up on hover over the anchor.

I'm sure that you could do this directly in mySQL with a horrible query involving regular expressions, etc -- but I really wouldn't. Chances are it's going to break a lot of the time.

One approach would be to have a second tag table (matching tagged_user_id to post_id) then when a user posts you parse the post and update that table as well as the posts table went saving the post. I'd also replace the anchor tag with something easy to find later.

However, to answer your question as it is, you'll need to take the string and go looking for the anchor tags, so after getting the post from the database:

if (preg_match_all('/<a href="profile\.php.id=\$([^"]*)">[^<]*<\/a>/', $str, $matches)) { 
    print_r($matches);
    // $matches[1] is an array of the ids mentioned, so you can 
    // query the database again to get the information 
}

This uses a regular expression that matches the whole link, but only if it's formatted exactly as your example. You may need to cope with extra whitespace, different quoting, etc, etc depending on how that link is generated.

for

$str = "I love <a href=\"profile.php?id=$12\">best_chef</a> 's recipes. ";

this gives the output:

Array
(
    [0] => Array
    (
        [0] => href="profile.php?id=$12
    )

    [1] => Array
    (
        [0] => 12
    )

)

Now you can query the database again and insert the divs before rendering the post.

You can see it'd be easier to pre-parse the post and extract that information, which may be easy depending on how you implement inserting tags.

If the anchors are embedded in the post you could store the start/end char position of each anchor in the tags table to make it easy to replace later.