StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POImplementing a basic Twitter/Facebook @mention system
primarykey
Id
16967116
data
AcceptedAnswerId
16970620
AnswerCount
1
ClosedDate
2013-06-07T22:57:47.493
CommentCount
3
CommunityOwnedDate
CreationDate
2013-06-06T16:15:52.417
FavoriteCount
0
LastActivityDate
2013-06-06T19:37:51.460
LastEditDate
2017-05-23T11:49:59.377
LastEditorUserId
-1
OwnerUserId
2332251
ParentId
0
PostTypeId
1
Score
-2
ViewCount
1146
LastEditorDisplayName
text
Body
I'm trying to create a basic @mention system for a microblogging application that I am creating (it's a project for me to learn PDO). I read a few other <a href="https://stackoverflow.com/a/5182301/2332251">similar</a> <a href="https://stackoverflow.com/a/5456339/2332251">questions</a> and <a href="http://www.codingforums.com/showthread.php?t=266258" rel="nofollow noreferrer">threads</a>,but I still can't understand what everyone is saying. What I have at the moment is a database structure, something like this: <pre><code>**mentions:** mention_id (primary key auto increment) post_id (id from the posts table) user_id (id of the user who is mentioned) unread (either 0 or 1 depending on whether the post has been viewed by the user mentioned) **posts:** post_id (primary key auto increment) user_id (id of the user who posted) post_content (the post contents) stamp (the post time stamp) </code></pre> The way I think it has to work based on previous posts is this - we add the post to the database and then run a function over it to grab the post_id THEN we run a regex statement over it and pull out all the references to @something, then we chop off the @ symbol and run it through a function to check if there is a user by that name. If there is a user by that name we insert their user_id, the post_id and a 1 into the unread column into the mentions table of the database so that later we can check if our currently logged in user has any unread mentions and display those. I understand this method may not scale well, but I'm not looking at holding millions of users I just want a simple solution. So... what I'm looking for is for someone to take a look at what I have so far and let me know how I can make it work and/or suggest a better/more simple approach. The end result I want is for a user to be able to @mention another user and to be able to display it as an 'unread' mention until the mentioned user views their notifications (a list of mentions etc e.g. like Facebook). What I have thus far is this: <pre><code>$post_content = 'Yo @jack, what up? I have a new email, jack@bob.com'; // to be later replaced with $post_content = $_POST['post_content']; // right here would be a function add_post that // inserts the post_content, user_id and time stamp into the database function select_post($post_content){ $sql = "SELECT post_id FROM posts WHERE post_content = $post_content "; $stmt = $GLOBALS['db']->prepare($sql); $stmt->execute(); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); return $rows; } function valid_username($mentionedUser){ $sql = "SELECT username FROM users WHERE username = $mentionedUser "; $stmt = $GLOBALS['db']->prepare($sql); $stmt->execute(); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); return $rows; } function insert_mention($post_id, $mentionedUser){ $sql = "INSERT INTO mentions (post_id, user_id, unread) VALUES (:post_id, :user_id, 1) // 1 means unread "; $stmt = $GLOBALS['db']->prepare($sql); $stmt->bindParam(':user_id', $mentionedUser, PDO::PARAM_STR); $stmt->bindParam(':post_id', $post_id, PDO::PARAM_INT); $stmt->execute(); } add_post($userid, $post_content); $post_id = select_post($post_content); if (preg_match("/\B@[a-zA-Z0-9]+/i", $post_content)) { preg_match_all("/\B@[a-zA-Z0-9]+/i", $post_content, $mentions); $mentions = array_map(function($str){ return substr($str, 1); }, $mentions[0]); foreach($mentions as $mentionedUser){ if(!valid_username($mentionedUser)){ continue; } insert_mention($post_id, $mentionedUser); } </code></pre> Am I even anywhere on the right track? How can I make this work? Please, the more detail in your answer, the better, I'm interested in what syntax you would use not just a general overview.
Tags
<php><mysql><regex><database><pdo>
Title
Implementing a basic Twitter/Facebook @mention system
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USCommunity
UserOwnerUserId
1. USJosh
plurals
PostLinksPostIdRelatedPostId
1. PL
 singulars
 LinkTypeLinkTypeId
 LTLinked
2. PL
 singulars
 LinkTypeLinkTypeId
 LTLinked
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 POImplementing a basic Twitter/Facebook @mention system
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTDownMod
2. VO
 singulars
 PostPostId
 POImplementing a basic Twitter/Facebook @mention system
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
3. VO
 singulars
 PostPostId
 POImplementing a basic Twitter/Facebook @mention system
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTDownMod
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.