I am trying to change the sorting method of my tag cloud (http://www.addfaith.net/menu5.php) so that it sorts so the most tagged item is at the top. I can get it to sort this way in phpMyAdmin and it gives me this sql:
SELECT COUNT( * ) AS `Rows` , `ID_TAG`
FROM `smf_tags_log`
GROUP BY `ID_TAG`
ORDER BY `Rows` DESC
LIMIT 0 , 30
Right now my php is using this sql query:
$query = "SELECT t.tag AS tag, l.ID_TAG, COUNT(l.ID_TAG) AS quantity
FROM smf_tags as t, smf_tags_log as l WHERE t.ID_TAG = l.ID_TAG
GROUP BY l.ID_TAG
ORDER BY l.ID_TAG DESC LIMIT 20";
When I changed it to:
$query = "SELECT COUNT( * ) AS `Rows` , `ID_TAG`
FROM `smf_tags_log`
GROUP BY `ID_TAG`
ORDER BY `Rows` DESC
LIMIT 0 , 30";
...I got nothing, it didn't spit out any data at all. Any help?
**EDIT**
Here is the code for context:
SELECT COUNT( * ) AS `Rows` , `ID_TAG`
FROM `smf_tags_log`
GROUP BY `ID_TAG`
ORDER BY `Rows` DESC
LIMIT 0 , 30
Right now my php is using this sql query:
$query = "SELECT t.tag AS tag, l.ID_TAG, COUNT(l.ID_TAG) AS quantity
FROM smf_tags as t, smf_tags_log as l WHERE t.ID_TAG = l.ID_TAG
GROUP BY l.ID_TAG
ORDER BY l.ID_TAG DESC LIMIT 20";
When I changed it to:
$query = "SELECT COUNT( * ) AS `Rows` , `ID_TAG`
FROM `smf_tags_log`
GROUP BY `ID_TAG`
ORDER BY `Rows` DESC
LIMIT 0 , 30";
...I got nothing, it didn't spit out any data at all. Any help?
**EDIT**
Here is the code for context:
PHP:
<html>
<head><title></title>
<link rel="stylesheet" type="text/css" href="style.css" />
</head>
<body bgcolor="fafad2">
<center>
<font size="+4" color="rgb(128,0,0)"><b>+</b></font> <font size="+4" color="rgb(128,0,0)">Faith</font><br>
</center>
<br><br>
<ul class="links">
<li><a href="main.php" id="front" title="Front Page" target="main"><span>Front Page</span></a></li>
<li><a href="http://forum.addfaith.net" id="forum" title="Forum - Discuss religious matters, ask questions, get answers" target="main"><span>Forum</span></a></li>
<li><a href="http://wiki.addfaith.net/index.php?n=WorshipWiki.WorshipWiki" id="wiki" title="Worship Wiki - Find a new place of worship, or add your own if it isn't there yet!" target="main"><span>Worship Wiki</span></a></li>
<li><a href="http://www.addfaith.net/about.html" id="about" title="About AddFaith" target="main"><span>About AddFaith</span></a></li>
</ul>
<br><br><br>
What's being talked about on AddFaith:<br><br>
<?php
$dbhost = "****"; #Your database host
$dbname = "****"; #Type your database name
$dblogin = "****"; #Type your database username
$dbpass = "****"; #Type your database password
//$db_prefix = "smf_";
$connection = mysql_connect("$dbhost", "$dblogin", "$dbpass");
mysql_select_db ("$dbname");
global $db_prefix, $scripturl, $context;
//Tag cloud from [url]http://www.prism-perfect.net/archive/php-tag-cloud-tutorial/[/url]
$query = "SELECT COUNT( * ) AS `Rows` , `ID_TAG`
FROM `smf_tags_log`
GROUP BY `ID_TAG`
ORDER BY `Rows` DESC
LIMIT 0 , 30";
$result = mysql_query($query);
//$result = db_query($query, __FILE__, __LINE__);
// here we loop through the results and put them into a simple array:
// $tag['thing1'] = 12;
// $tag['thing2'] = 25;
// etc. so we can use all the nifty array functions
// to calculate the font-size of each tag
$tags = array();
$tags2 = array();
while ($row = mysql_fetch_array($result))
{
$tags[$row['tag']] = $row['quantity'];
$tags2[$row['tag']] = $row['ID_TAG'];
}
if(count($tags2) > 0)
{
// change these font sizes if you will
$max_size = 150; // max font size in %
$min_size = 90; // min font size in %
// get the largest and smallest array values
$max_qty = max(array_values($tags));
$min_qty = min(array_values($tags));
// find the range of values
$spread = $max_qty - $min_qty;
if (0 == $spread)
{ // we don't want to divide by zero
$spread = 1;
}
// determine the font-size increment
// this is the increase per tag quantity (times used)
$step = ($max_size - $min_size)/($spread);
// loop through our tag array
$context['poptags'] = '';
$row_count = 0;
foreach ($tags as $key => $value)
{
$row_count++;
// calculate CSS font-size
// find the $value in excess of $min_qty
// multiply by the font-size increment ($size)
// and add the $min_size set above
$size = $min_size + (($value - $min_qty) * $step);
// uncomment if you want sizes in whole %:
// $size = ceil($size);
// you'll need to put the link destination in place of the #
// (assuming your tag links to some sort of details page)
$context['poptags'] .= '<a href="' . 'http://forum.addfaith.net/index.php' . '?action=tags;id=' . $tags2[$key] . '" style="font-size: '.$size.'%"'.'target="main"';
// perhaps adjust this title attribute for the things that are tagged
$context['poptags'] .= ' title="'.$value.' things tagged with '.$key.'"';
$context['poptags'] .= '>'.$key.'</a> ';
if ($row_count > 5)
{
$context['poptags'] .= '<br />';
$row_count =0;
}
// notice the space at the end of the link
}
}
echo $context['poptags'];
?>
</body>
</html>
