php - How can I find the most frequently mentioned hashtags in a MySQL column? -

php - How can I find the most frequently mentioned hashtags in a MySQL column? -

my database has table messages 3 fields: 'messageid',messagetext (varchar), dateposted (datetime)

i want store bunch of messages in field messagetext along respective date of posting in field dateposted. lot of these messages have hashtags in them.

then, using php , mysql want find out hashtags top 5 mentioned hashtags in messages posted in past week.

how can this? i'd appreciate help. many in advance.

do not take me wrongly, you've set a world of hurt. best way proceed follow lonesomeday's advice , parse hashtags @ insert time. reduces processing time making more deterministic (the workload "spread" between inserts).

if want proceed anyway, need tackle several problems.

1) recognize tags.

2) multiple-select tags. if have message saying "#mysql splitting #cool", want two rows 1 message, 1 saying 'mysql', other 'cool'.

3) selecting appropriate messages

4) performances

you can approach in @ to the lowest degree 2 ways. can utilize stored function , find here on so (actually this site) - have modify though.

this syntax first occurrence of #hashtag in value plus text next it:

select substring(value, length(substring_index(value, '#', 1))+1);

you need decide where, each #hashtag, #stops. (and #parenthesized). @ point need regexp, or search sequence of @ to the lowest degree 1 alphanumeric character - in regexp parlance, [a-za-z0-9]+ - specifying possible characters or using loop, i.e. "#" ok, "#t" ok, "#ta" ok, "#tag" ok, "#tag," not , hashtag '#tag' (or 'tag').

another more promising approach utilize user defined function capture hashtags; can utilize preg_capture.

you have merge both approaches: modifying stored function's setup , inner loop read

declare cur1 cursor select messages.messagetext messages messages.messagetext '%#%'; declare go on handler not found set done = 1; drop temporary table if exists table2; create temporary table table2( hashtag` varchar(255) not null

) engine=memory;

... set occurrence = (select length(msgtext) - length(replace(msgtext, '#, '')) +1); set i=1; while <= occurrence insert table2 values select preg_capture('/#([a-z0-9]+)/i', messagetext, occurrence)); set = + 1; end while; ...

this homecoming list of message-ids , hashtags. need group them by hashtag, count them , order count desc, adding limit 5 5 popular.

php mysql hashtag