I was just about to ask a similar question about efficiency (which is the best way?), so I’ll offer you my solution that avoids making a query inside your while loop, and maybe other people can comment on whether it is an efficient solution or not?
I have this function:
function getAgents () {
$result = @mysql_query("SELECT * FROM agents");
if (!$result) {
exit('<p>Error performing query: ' .
mysql_error() . '</p>');
}
$agents = array();
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
foreach ($row as $key => $value)
{
$formArray[$key] = $value;
}
$agentID = $row['agentID'];
$agents[$agentID] = $formArray;
}
return $agents;
}
I call it at the top of the page, and then close off:
$agents = getAgents();
mysql_close();
I then have my query and while loop as per usual:
$result = @mysql_query("SELECT * FROM residential LIMIT $limitValue, $limit");
if (!$result) {
exit('<p>Error performing query: ' .
mysql_error() . '</p>');
}
while ($row = mysql_fetch_array($result)) {
Within the loop, I set a variable to indicate which agent, and then access data from my array that the function getAgents() made:
$agent = $row['contact'];
//$row['contact'] would be equivalent to your $host_id
echo $agents[$agent]['name'] . " - " . $agents[$agent]['phone'];
} //end while
Of course I also have lots of other stuff in my while loop to display onscreen, from my query to the “residential” table.
So for you, you’d make your “reputation” query in a function, like I have done for my getAgents(), then access it in your while loop.
Only, you should propbably wait for other people to comment on the efficiency of my code. This solution works well for me for this site, as there are only a very few agents, so the array made by getAgents() isn’t very large. The question I was going to ask was whether that’s an appropriate solution for a larger table with more rows, which it seems like yours would be, or would need the potential to be - I just don’t know how well my code would scale up in terms of efficiency.
Please let me know if there’s anything in that which isn’t clear, and hopefully someone can tell you whether it’s a good solution or if there’s a better one!
Thanks for the correction.