FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Decreasing num. Queries





Jamatu
I'm currenty using this chunk of code to output the news from a database onto the index page of my website:
Code:
<?
$query = "SELECT * FROM news ORDER BY news_id DESC LIMIT 0,5";
$result=mysql_query($query) or die ( mysql_error( ) );
$numqueries++;

while($row = mysql_fetch_row($result))
{
   $news_id       = $row[0]; //use row names for simplicity
   $news_time     = $row[1];
   $news_title    = $row[2];
   $news_text     = nl2br($row[3]);
   $news_time     = date('D, M j Y @ H:i:s T', $news_time);
   $news_posterid = $row[4];
   $query         = "SELECT username FROM users WHERE id = '$news_posterid'";
   $poster        = mysql_query("$query");
   $numqueries++;
   $par           =     mysql_fetch_array($poster);
?>
         <span class="title"><? echo "$news_title"; ?></span><br />
         <span class="tiny">Posted by <a href="./members.php?uid=<? echo "$news_posterid"; ?>"><? echo "$par[username]"; ?></a> on <? echo "$news_time"; ?></span>
         <div class="greymo">
         <table>
            <tr>
               <td><p><? echo "$news_text"; ?></p></td>
            </tr>
         </table>
         </div><br />
<?
}
?>

Code:
"SELECT username FROM users WHERE id = '$news_posterid'"
As you can see the above query is getting repeated for every news article that is shown. Is there anyway to get the posters name without having to do this?
mathiaus
I had the exact same problem with my news which I was getting from the forum. I needed topic information and then the first posts contents. Not wanting to overload the server (dont know if it would) I decided to leave it doing all the queries but write it to a new file rather than output it. I then included this file onto my index page. At the same time I saved it to my rss feed so it saves queries there as well and everything loads faster for the users!

I had previously tried using a WHERE id=aray with the array being an array of ids I needed though needless to say this didnt work Rolling Eyes
woja
Assuming that MySQL does INNER JOINs properly, you can sort the problem out with a single SQL statement:
Code:
SELECT news.id, news.time, news.title, news.test, users.username
   FROM news
      INNER JOIN users ON (users.id = news.posterid)
   ORDER BY news.id DESC
   LIMIT 0, 5;

(I've just guessed at the column names, by the way).
Also, if I may make a suggestion:
Your original query did "SELECT *" which means you get the fields back in the order they're defined in the database. If the database definition changes (e.g., someone inserts a field), your code (the $row[] stuff) will all need changing. Use names wherever possible OR always specify the full field list in the query (as I have above).
Jamatu
Cheers woja Very Happy

Whilst I was waiting for someone to reply to this threat I tried to figure out a way to do it and came up with a way that worked. It's as follows:
Code:
$query = "SELECT news.*, users.username
FROM news, users
WHERE news.news_posterid = users.id
ORDER BY news_id DESC
LIMIT 0,5";

Which is the most efficient method? The way I came up with or using INNER JOIN?
woja
Jamatu, having done a couple of experiments and looked at the SQL syntax, I find that witing:
Code:
SELECT a.f1, b.f2 FROM a, b WHERE (a.id = b.id);

is identical to:
Code:
SELECT a.f1, b.f2 FROM a INNER JOIN b ON (a.id = b.id);

Not sure that it is for more complex expressions and the first version doesn't let you filter the join. I would always use INNER JOIN because it makes the meaning plain.
Related topics
Windows XP Tricks & Tips!!!!
MySQL SUBselect queries and Nested queries
Database/PHP/Cron Jobs [Resolved]
An Example to mySQL class
What is useful way in this php+mysql problem?
JavaScript Queries
How To : Secure Your PHP Website
Anonymous logins to FTP
Combining Update Queries
How to track the queries data from the google search box?
1,000,000 MySQL queries!
PHP&MySql - Is there any server side caching of queries?
Servers related Queries to the Staff
Fetch array from mysql
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.