CODEWITHSUNDEEP
phpmysql
Scarface
Scarface

Reputation: 3913

How do you count and format user tags

I have user entered tags that go under the table field tags. 

$sql="SELECT tags from table";
$stmt16 = $conn->prepare($sql);
$result=$stmt16->execute();
while($row = $stmt16->fetch(PDO::FETCH_ASSOC)){ 
echo $tags=$row['tags'];
}

This produces

cool,yes,okcool,yes,ok

because there are 2 entries with the same 3 tags under that field. I want to be able to count how many duplicates there are of each tag such that the result should be cool(2) yes(2) ok(2). Anyone know the proper way to approach this? Should I fetch the results as an array or explode them?

Upvotes: 1

Views: 115

Answers (3)

user743234
user743234

Reputation:

Simply with this query:

SELECT tag, COUNT(*)
FROM table
GROUP BY tag
HAVING COUNT(*) >1;

Upvotes: 1

Spudley
Spudley

Reputation: 168745

With your current data structure, the answer I'd give is yes: as you said, explode them, and put them into an array.

$tags = array();
while($row = $stmt16->fetch(PDO::FETCH_ASSOC)){ 
    foreach(explode(',',$row['tags']) as $tag) {
        if(isset($tags[$tag]) {$tags[$tag]++;} else {$tags[$tag]=1;}
    }
}

//now you can see how many times each tag occurred...
foreach($tags as $tag=>$count) {
   print "Tag {$tag} occurred {$count} times<br />";
}

However, if you can change the database structure, you'll find it a lot easier to manage your tags if you only have one per DB record. It will make it a lot easier to query them that way.

Upvotes: 3

DKSan
DKSan

Reputation: 4197

I would alter the statement to

SELECT COUNT(*) as cnt, tags 
FROM table
GROUP BY tags

and there you are.

Edit: Don't forget to alter your echo to echo $row['tags'] ."(". $row['count'] .")";

Upvotes: 2

Related Questions