CODEWITHSUNDEEP
phpmysqltwittertrending
user2326760
user2326760

Reputation: 11

Search query like twitter trending topics

I need MYSQL search query to get trending topics from my table, Below is the explanation What I need

+----+---------+-----------------------------+
| ID | ID_user | text                        | 
+----+---------+-----------------------------+
| 1  | bruno   | michael jackson is dead     |
| 2  | thomasi | michael j. moonwalk is dead |
| 3  | userts  | michael jackson lives       |
+----+---------+-----------------------------+

i want query the words most repeated on the table, limit top 10, the result may be this:

+-------+------------+
| count | word       |
+-------+------------+
| 3     | michael    |
| 2     | dead       |
| 2     | jackson    |
| 1     | j.         |
| 1     | lives      |
| 1     | moonwalk   |
+-------+------------+

But I want search only words that repeat more of 10 times, in this case noone word is appear, but if criteria for repetead words is 2, it will display only 'michael' and 'dead', but ignore 'is' because I dont want words with less 2 chars of lenght, and the words that a phrase, then I need apear this:

+-------+-----------------+
| count | word            |
+-------+-----------------+
| 2     | michael jackson |
| 2     | dead            |
+-------+-----------------+

Upvotes: 1

Views: 1248

Answers (1)

Ochi
Ochi

Reputation: 1478

CREATE TEMPORARY TABLE counters (id INT);
-- insert into counters as much as you like (values here means "number of repeats"
INSERT INTO counters VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9),(10),
                           (11),(12),(13),(14),(15),(16),(17),(18),(19),(20),
                           (21),(22),(23),(24),(25),(26),(27),(28),(29),(30);

  SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(texts.text,' ',counters.id),' ',-1) AS word,
         COUNT(counters.id) AS counter
    FROM texts
         INNER JOIN counters ON (LENGTH(text)>0 AND SUBSTRING_INDEX(SUBSTRING_INDEX(text,' ',counters.id),' ',-1) <> SUBSTRING_INDEX(SUBSTRING_INDEX(text,' ',counters.id-1),' ', -1))
   WHERE length(SUBSTRING_INDEX(SUBSTRING_INDEX(texts.text,' ',counters.id),' ',-1)) > 2
GROUP BY word
  HAVING COUNT(counters.id) > 1
ORDER BY counter desc;

but it's not very efficient and shouldn't be done like that

edit:

  SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(texts.text,' ',counters.id),' ',-1) AS word,
         COUNT(counters.id) AS counter
    FROM texts
         INNER JOIN counters ON (LENGTH(text)>0 AND SUBSTRING_INDEX(SUBSTRING_INDEX(text,' ',counters.id),' ',-1) <> SUBSTRING_INDEX(SUBSTRING_INDEX(text,' ',counters.id-1),' ', -1))
   -- exclude words list
   WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(texts.text,' ',counters.id),' ',-1) NOT IN ('is', 'of', 'this', 'to')
GROUP BY word
  HAVING COUNT(counters.id) > 1
ORDER BY counter desc;

Upvotes: 1

Related Questions