Get most used #hashtags with SQL.

+1 Steven the awesome · June 4, 2015

/images/forum/upload/2015-06-04/e8ba2764314b9197a559edb488cf17a0.pngTable name: timeline_messages

So I want to get with a query the top 4 most used #hashtags within the message. Anyone know some good query for that?

Post a Reply


Oldest  Newest  Rating
0 saer siis · June 18, 2015
Do you store all hashtags in some place , if yes then it would be very easy to filter the top 4. if not then follow the following steps
First of all create a table with three columns 1.)Hashtag 2.)Count 3.)Id =>Primary Key and then follow these steps

1.)Before you insert the message in the `timeline_messages` mysql table ,detect the hash tags and insert them into a seperate table .
2.) before inserting the hashtags check if that hashtag already exists . if yes update the  count column by adding 1 .
 ELSE simply insert the new hashtag
3.) when you have the HASH tags table ready , Go For the following query
SELECT * FROM hashtags ORDER BY count LIMIT 4

If you dont know how to detect hashtags .. Follow the following PHP Code

$string_array=explode(" ",$string);//hash mentions

        $q=str_replace("@"," ",$string_array[$i]);
         $z=str_replace(" ","!",$q);
//Check if hashtag by SELECT query where the hashtag would br $t and then implement your further code

     return $temp_res;



Hope this helps!!
  • 1

SQL & Databases


Everything SQL and Databases related in here!

Bucky Roberts Administrator