Two same queries, cut down to one?

agentvip · 12-09-2007, 02:18 AM

Hello,

Basically, i have the same query displayed twice on one page.

Code:

	$res = "SELECT COUNT(*) as count, table_traffic.* FROM table_traffic, table_traffic2ip WHERE table_traffic.traffic_id=table_traffic2ip.traffic_id AND table_traffic.traffic_status='1' GROUP BY table_traffic.traffic_id ORDER BY count DESC LIMIT 20";

	$list_traffic = mysql_query($res) or die(mysql_error());

		while($row = mysql_fetch_array($list_traffic)){					
			echo "<a href='".$row['traffic_url']."' class='popular_tags' target='_blank'>".$row['traffic_title']."</a>";
		}

But yes, the site is for http://www.por(**)naroo.com (remove the (**) obviously if you want to visit.. which is NSFW btw)

However, i have the query on the sidebar on the right, and the same query at the bottom... and if you go on the site, and refresh.. there are 80~ requests for the homepage.. so.. just trying to cut it down, even just a little :P

Wildhoney · 12-09-2007, 02:42 AM

Before I start, welcome to the community! I've just had a client showing me lots of NSFW websites for a project he wants me to do, I come onto TalkPHP and there we go - more of the filth!

How about you place them into an array and that way you don't need to run the query again, just loop through the array.

php Code:

$res = "SELECT COUNT(*) as count, table_traffic.* FROM table_traffic, table_traffic2ip WHERE table_traffic.traffic_id=table_traffic2ip.traffic_id AND table_traffic.traffic_status='1' GROUP BY table_traffic.traffic_id ORDER BY count DESC LIMIT 20";
$list_traffic = mysql_query($res) or die(mysql_error());
$sites = array();

while($row = mysql_fetch_array($list_traffic))
{
    $sites[$row['traffic_title']] = $row['traffic_url'];
}

foreach($sites as $title => $url)
{
    echo "<a href='".$url."' class='popular_tags' target='_blank'>".$title."</a>";
}

Geert · 12-09-2007, 03:56 PM

Alternatively, you could use the mysql_data_seek function.

PHP Code:


			
$res = "SELECT COUNT(*) as count, table_traffic.* FROM table_traffic, table_traffic2ip WHERE table_traffic.traffic_id=table_traffic2ip.traffic_id AND table_traffic.traffic_status='1' GROUP BY table_traffic.traffic_id ORDER BY count DESC LIMIT 20";



$list_traffic = mysql_query($res) or die(mysql_error());



// Looping once

while($row = mysql_fetch_array($list_traffic)){                    

    echo "<a href='".$row['traffic_url']."' class='popular_tags' target='_blank'>".$row['traffic_title']."</a>";

}



// Resetting internal pointer

mysql_data_seek($list_traffic, 0);



// And looping again!

while($row = mysql_fetch_array($list_traffic)){                    

    echo "<a href='".$row['traffic_url']."' class='popular_tags' target='_blank'>".$row['traffic_title']."</a>";

}