16
Mods & Plugins (Requests & Discussions) / Re: [PRE-MOD] Top Categories by votes, rating and hits
« on: October 29, 2008, 09:59:04 PM »
You are right V@no, I have made it different, maybe later someone can improve it.
Here we go:
STEP #1:
Create a new custom page
http://www.4homepages.de/forum/index.php?topic=4996.0
STEP #2:
On this new page, on content part, add (You must modify "yourdatabasehost","youruser","yourpassword" for your data):
You can see the final result here Hi5 Girls Contest
That's it, let me know if you have some problems.
Here we go:
STEP #1:
Create a new custom page
http://www.4homepages.de/forum/index.php?topic=4996.0
STEP #2:
On this new page, on content part, add (You must modify "yourdatabasehost","youruser","yourpassword" for your data):
Code: [Select]
//Optional styles
<style type="text/css">
<!--
.top {
width:auto;
padding:10px;
float:left;
margin-right:40px;;
}
.top h2{
line-height:150%;
font-size:16px;
}
-->
</style>
<?php
function Connect()
{
if (!($link=mysql_connect("yourdatabasehost","youruser","yourpassword")))
{
echo "Error connecting to database";
exit();
}
if (!mysql_select_db("yourdatabasename",$link))
{
echo "Error selecting database.";
exit();
}
return $link;
}
$link=Connect();
?>
<div class="top"></div>
<div class="top">
<h2>Top 15 - Votes </h2>
<p>
<?php
$sql = "SELECT 4images_categories.cat_id AS CAT_ID, sum( `image_votes` ) AS VOTES, 4images_categories.cat_name AS NAME
FROM `4images_images` , `4images_categories`
WHERE 4images_images.cat_id = 4images_categories.cat_id
AND cat_parent_id =125
GROUP BY 4images_images.cat_id
HAVING sum( `image_votes` ) >100
ORDER BY VOTES DESC
LIMIT 0 , 15 ";
//* AND cat_parent_id =125 --> This line is exclusive for selected subcategories, in this case, all subcategories of category 125
//* HAVING sum( `image_votes` ) > 100 --> Minimal votes quantity
//* LIMIT 0 , 15 "; --> In this case it will display a top 15 list, change this number for your preferred.
$result = mysql_query($sql,$link);
$i=0;
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$i++;
echo "<a href=categories.php?cat_id={$row['CAT_ID']}>#".$i."</a> {$row['VOTES']} "." <a href=categories.php?cat_id={$row['CAT_ID']}>{$row['NAME']}</a><br>";
}
?>
</p>
</div>
<div class="top">
<h2>Top 15 - Visits </h2>
<p>
<?php
$sql = "SELECT 4images_categories.cat_id AS CAT_ID, sum( `image_hits` ) AS HITS, 4images_categories.cat_name AS NAME
FROM `4images_images` , `4images_categories`
WHERE 4images_images.cat_id = 4images_categories.cat_id
AND cat_parent_id =125
GROUP BY 4images_images.cat_id
HAVING sum( `image_votes` ) >100
ORDER BY HITS DESC
LIMIT 0 , 15 ";
$result = mysql_query($sql,$link);
$i=0;
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$i++;
echo "<a href=categories.php?cat_id={$row['CAT_ID']}>#".$i."</a> {$row['VOTES']} "." <a href=categories.php?cat_id={$row['CAT_ID']}>{$row['NAME']}</a><br>";
}
?>
</p>
</div>
<div class="top" style="margin:0px;">
<h2>Top 15 - Rating </h2>
<p>
<?php
$sql = "SELECT 4images_categories.cat_id AS CAT_ID, avg( `image_RATING` ) AS VOTOS, 4images_categories.cat_name AS NAME
FROM `4images_images` , `4images_categories`
WHERE 4images_images.cat_id = 4images_categories.cat_id
AND cat_parent_id =125
GROUP BY 4images_images.cat_id
HAVING sum( `image_votes` ) >100
ORDER BY VOTOS DESC
LIMIT 0 , 15 ";
$result = mysql_query($sql,$link);
$i=0;
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$i++;
echo "<a href=categories.php?cat_id={$row['CAT_ID']}>#".$i."</a> {$row['VOTES']} "." <a href=categories.php?cat_id={$row['CAT_ID']}>{$row['NAME']}</a><br>";
}
mysql_close($link);
?>
</p>
</div>
You can see the final result here Hi5 Girls Contest
That's it, let me know if you have some problems.