1
\$\begingroup\$

I have a database that, among other things, stores publications and publication tags. There is a many-to-many relationship between publications and publication tags. Simply put, I query the database for all the publication tags and the corresponding id. While looping through that query, I have a nested query to count the number of times each tag is used. Once I have all the information, I then loop through all results to display the tags. That seems inefficient, but I can't think of a better way. Thoughts?

//get the tag id and tag names
try {
  $stmt = $pdo->prepare('SELECT publicationTagId, tagName FROM publicationTags WHERE EXISTS (SELECT publicationTags_publicationTagId FROM publications_have_publicationTags WHERE publicationTags_publicationTagId = publicationTagId)ORDER BY tagName');
  $stmt->execute();
} catch(PDOException $e) {
    echo "<p>Oops!</p>";
}     
$tags = $stmt->fetchAll();

$tagCount = array();
//iterate through the tags and get usage counts
foreach($tags as $tag) {
  try {
    $stmt = $pdo->prepare('SELECT count(publicationTags_publicationTagId) FROM publications_have_publicationTags WHERE publicationTags_publicationTagId = :pubId');
    $stmt->execute(array(':pubId'=>$tag[0]));
    while($tagCounter = $stmt->fetch()) {
      $tagCount[] = $tagCounter[0]; 

    }
  } catch(PDOException $e) {
    echo "<p>Oops!</p>";
  }
}

//get the min and max usages
$minUse = min($tagCount);
$maxUse = max($tagCount);

//max and min font size
$maxPercent = 150;
$minPercent = 100;

//prevent divide by zero
$divisor = ($maxUse == $minUse) ? 1 : $maxUse - $minUse;

//multiplier
$multiplier = ($maxPercent - $minPercent) / $divisor;

//set up list
echo "<ul class='tagCloudList'>";

$counter = 0;
//loop through tags
foreach($tags as $tag) { 
    $size = $minPercent + ($tagCount[$counter][0]-$minUse)*$multiplier;
    echo "<li><a href='?pubTag=$tag[1]' style='font-size:$size%'>$tag[1]</a></li>";
    $counter++;
}

//reset link
echo "<li><a href='publications.php'>View All</a></li>";

//close the list
echo "</ul>";
\$\endgroup\$

1 Answer 1

3
\$\begingroup\$

Note: Your table and especially column names are quite verbose. I would remove the table name from each column as a first start and be more consistent with the names. I'll use better names below. You'll have to map it out yourself.

You can get the used tags with their counts in a single query with this statement:

select
    tag_id, 
    tag_name, 
    count(publication_id)
from
    tag join publication_tag using (tag_id) 
group by
    tag_id
\$\endgroup\$
2
  • \$\begingroup\$ I agree on the table names. It's my punishment for being lazy and using mysql workbench. It's a little late in the game to rework the database, but I'll consolidate the query. If it checks out I'll accept. Until then, +1...thanks for the feedback! \$\endgroup\$ May 3, 2014 at 3:38
  • \$\begingroup\$ @MatthewJohnson I agree with you on MySQL WorkBench. I took the time to fix all the names as I built the model, but it's a shame it can't be configured with naming styles. \$\endgroup\$ May 3, 2014 at 4:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.