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’)
