PHP Table With Comma [delete this]

Question

Here i get mos popular hashtags (mos used hashtags) but problem is with comma my query show this:

Popular hashtags: 
#q7 (2)
#q9 (2)
#q7 , #q0 (1)
#q0 (1)

I want this:

Popular hashtags: 
#q7 (3 times )
#q9 (2 times)
#q0 (2 times)

my code:

$query_result = mysqli_query($Connection, "select hashtag, count(hashtag) as cnt
from tricks
group by hashtag
order by cnt desc limit 5");

$results = array();
while($row = mysqli_fetch_assoc($query_result)) {
   $results[] = "{$row['hashtag']} ({$row['cnt']})";
}
echo "Popular hashtags: </br>", implode("</br>", $results);

show this: Popular hashtags: #q7 (2) #q9 (2) #q7 , #q0 (1) #q0 (1) but is incorrect — Simple Name, Aug 03 '16 at 07:41
ideally you would store the tags in separate records rather than comma-separated in the same string; it's much easier to work with if they're separate. — Simba, Aug 03 '16 at 08:47
Simba understates the case. It's not just more difficult when a relational database holds more than value in an attribute, it's WRONG — symcbean, Aug 03 '16 at 09:31

Depzor · Accepted Answer · 2016-08-03T09:13:43.260

1

Php solution:

$query_result = mysqli_query($Connection, 
                  "SELECT hashtag FROM tricks");             

$popularHashtags = array();

while($row = mysqli_fetch_assoc($query_result)) {

  $hashtags = explode(',', $row['hashtag']);

  foreach($hashtags as $hashtag) {
      $hashtag = trim($hashtag);

      if (!empty($hashtag)) {

        if (!isset($popularHashtags[$hashtag])) {
          $popularHashtags[$hashtag] = 0;
        }

        $popularHashtags[$hashtag]++;
      } 
  }
}


arsort($popularHashtags);

// for showing popular hashtags

echo "Popular hashtags: <br />";

foreach($popularHashtags as $hashtag => $total) {
  echo $hashtag . " ( " . $total . " ) <br />";
}

edited Aug 03 '16 at 09:13

answered Aug 03 '16 at 08:41

Depzor

1,126
1
12
21

thanks so much but now show me this error: ( ! ) Parse error: syntax error, unexpected '$row' – Simple Name Aug 03 '16 at 08:54
I have made some changes. Could you check again? @ShkurtaAbazi – Depzor Aug 03 '16 at 08:58
now work but not show correct :( see image: http://i.imgur.com/62IjVTw.png – Simple Name Aug 03 '16 at 09:03
You have to add !empty check, see my changes @ShkurtaAbazi. – Depzor Aug 03 '16 at 09:08
now work correct thank you so much but how to remove array i want to echo only hashtag name and used times thanks ??? http://i.imgur.com/pQLuFaB.png – Simple Name Aug 03 '16 at 09:10
np :) @ShkurtaAbazi – Depzor Aug 03 '16 at 09:16

score 0 · Answer 2 · edited May 23 '17 at 12:34

If you know the number of hastags contained in the field, you can use the SUBSTRING_INDEX function, or you can see this answer MySQL split to split your field.

Something like this (I have not a MySQL instance):

SELECT hastag as T1 FROM tricks WHERE hastag NOT LIKE '%,%' -- Take the Hastag without separator in it
UNION
SELECT SUBSTRING_INDEX(hashtag, ',', 1) as T1 FROM tricks WHERE hastag LIKE '%,%' -- Take the first Hastag (before separator)
UNION
SELECT SUBSTRING_INDEX(hashtag, ',', 2) as T1 FROM tricks WHERE hastag LIKE '%,%' -- Take the second Hastag (before separator)

score 0 · Answer 3 · edited May 23 '17 at 10:33

Try this out

select count(*) as total, hashtag FROM (
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(hashtag, ',', n.n), ',', -1) hashtag
  FROM table CROSS JOIN 
(
   SELECT a.N + b.N * 10 + 1 n
     FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ORDER BY n
) n WHERE n.n <= 1 + (LENGTH(hashtag) - LENGTH(REPLACE(hashtag, ',', ''))) ORDER BY hashtag ) alias GROUP by hashtag

Took the help from this answer. Hope this helps you

PHP Table With Comma [delete this]

3 Answers3