Tag Cloud using 2 queries (one nested), and a foreach loop: Is there a better way? 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-&gt;prepare('SELECT publicationTagId, tagName FROM publicationTags WHERE EXISTS (SELECT publicationTags_publicationTagId FROM publications_have_publicationTags WHERE publicationTags_publicationTagId = publicationTagId)ORDER BY tagName');
  $stmt-&gt;execute();
} catch(PDOException $e) {
    echo "&lt;p&gt;Oops!&lt;/p&gt;";
}     
$tags = $stmt-&gt;fetchAll();

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

    }
  } catch(PDOException $e) {
    echo "&lt;p&gt;Oops!&lt;/p&gt;";
  }
}

//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 "&lt;ul class='tagCloudList'&gt;";

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

//reset link
echo "&lt;li&gt;&lt;a href='publications.php'&gt;View All&lt;/a&gt;&lt;/li&gt;";

//close the list
echo "&lt;/ul&gt;";
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