2014-05-28

Implementation of "Post count" of HashTag with Mysql stored proc

Recently, I am making a feature similar to Instagram HashTag search in my app, which is searching the hashtag with post count with the given keyword.

To do this, I try to keep the post count in the Tag record and get it when do the search. However, to complete this, I need to handle the way to increase the count when a new post is created.

To do so, I had chosen using stored proc to do this task.


What’s and Why stored procedure ? 


Combine several SQL in a named procedure. It can make the application code more simple.

Before using procedure:
We need to use two SQLs to handle
UPDATE table SET count = count + 1 WHERE tag = ‘myTag’
SELECT count FROM table WHERE tag = ‘myTag’

After using procedure:
Just simply use the following SQL
CALL increase_post_count(‘myTag’)

Stored procedure of “Increase Post count” 


Define the procedure 

DELIMITER //
CREATE PROCEDURE inc_post_count
(
   IN inputTag VARCHAR(100)
)
BEGIN
    UPDATE tbl_hashtag SET postCount = postCount + 1 WHERE tag = inputTag ;
    SELECT postCount FROM tbl_hashtag WHERE tag = inputTag ;
END //
DELIMITER ;

Note: The above statement is working mysql client but not in phpMyAdmin

Using the procedure
CALL inc_post_count(‘MyTag’)