optimizing code to generate categories list faster..
Posted: Mon Feb 15, 2010 3:43 pm
Hello!
I would truly appreciate if someone could help me optimizing this code. It's basically a photo gallery site where the photos are categorized by subject. I want to display the list of categories and the amount of pictures that each category has. I have over 150 categories with over 1000 photos.
It's working so far but it takes like 20 sec to get the results, any idea why and how it could be solved ?
Cheers!
B.
I would truly appreciate if someone could help me optimizing this code. It's basically a photo gallery site where the photos are categorized by subject. I want to display the list of categories and the amount of pictures that each category has. I have over 150 categories with over 1000 photos.
It's working so far but it takes like 20 sec to get the results, any idea why and how it could be solved ?
Cheers!
B.
Code: Select all
<?
$sql2 = mysql_query("SELECT a.title,a.id,a.active,count(b.id) as 'amount', b.gallery_id FROM photo_galleries a LEFT JOIN photo_package b on a.id = b.gallery_id where a.active = '1' group by title order by amount");
$number = mysql_num_rows($sql2);
echo "<div style=\"margin-bottom:20px;\">There are <b> ".$number." </b> Categories</div>";
$data = array();
if ($order_by == "alphabet") {
while ($row = mysql_fetch_array($sql2))
{
$firstletter = strtoupper(substr($row['title'],0,1));
if ($firstletter == "Ö") {
$firstletter = "OE";
}
if ($firstletter == "Ü") {
$firstletter = "UE";
}
if ($firstletter == "Ä") {
$firstletter = "AE";
}
$count = mysql_fetch_array(mysql_query("Select count(*) from `photo_package` where `gallery_id` = ".$row['id']." or other_galleries like '%".$row['id']."%'"));
$data[$firstletter][] = array(0 => $row['title'], 1 => $row['id'], 2 => $count[0]);
}
setlocale (LC_ALL, 'de_DE');
if ($sort_order == "ascending") {
ksort($data, SORT_LOCALE_STRING);
}
else {
krsort($data, SORT_LOCALE_STRING);
}
$number = round(count($data) / 3);
?>
<div>
<table style="border:none; width:100%;">
<tr>
<td valign="top">
<?
$counter = 1;
foreach ($data as $key => $val){
$a = $key;
if ($a == "OE") {
$a = "Ö";
}
if ($a == "AE") {
$a = "Ä";
}
if ($a == "UE") {
$a = "Ü";
}
echo "<div class=\"key_1\">".$a."</div><div class=\"key_2\">";
foreach ($val as $key2 => $val2) {
echo "<a href=\"gallery.php?gid=".$val2[1]."\">".$val2[0]."</a><span style=\"color:#666666;\"> (".$val2[2].")</span><br />";
}
echo "</div>";
if ($counter > $number){
?>
</td>
<td valign="top">
<?
$counter = 0;
}
$counter = $counter + 1;
}
}
else {
$sql2 = mysql_query("SELECT * FROM photo_galleries where active = '1'");
while ($row = mysql_fetch_array($sql2))
{
$sql3 = mysql_query("Select * from photo_package where gallery_id = '".$row[id]."' or other_galleries like '%".$row[id]."%'");
$amount = mysql_num_rows($sql3);
$data[] = array(0 => $amount, 1 => $row['title'], 2 => $row['id']);
}
if ($sort_order == "descending") {
rsort($data);
} else {
sort($data);
}
$number = round(count($data) / 3);
?>
<table style="border:none; width:100%;">
<tr>
<td valign="top">
<?
foreach ($data as $key => $val){
echo "<div class=\"key_2\" style=\"margin:0px;\"><a href=\"gallery.php?gid=".$val[2]."\">".$val[1]."</a><span style=\"color:#666666;\"> (".$val[0].")</span></div>";
if ($counter > $number){
?>
</td>
<td valign="top">
<?
$counter = 0;
}
$counter = $counter + 1;
}
}
?>