PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!
Moderator: General Moderators
s.dot
Tranquility In Moderation
Posts: 5001 Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana
Post
by s.dot » Mon Jul 25, 2005 12:50 am
Someone please explain to me why this takes an insane amount of time to load. I've sectioned off the code and this is the part that takes about 15 seconds to load.
Code: Select all
<? $sql_text = ("SELECT id, topicname, threadstatus, author FROM forumtopicmain WHERE forumid = '$forumid' AND type != 'sticky' ORDER BY lastreply DESC");
$query = mysql_query($sql_text) ." LIMIT 0, 25";
// Show the non-stickied topics (most of them)
while($array = mysql_fetch_assoc($query))
{
$replies = mysql_num_rows(mysql_query("SELECT id FROM forumentries WHERE topicid = '".$array['id']."'")) - 1;
$lastpostarray = mysql_fetch_assoc(mysql_query("SELECT time, author FROM forumentries WHERE topicid = '".$array['id']."' ORDER BY time2 DESC LIMIT 1"));
// Show the number of pages for each individual topic
$query2 = mysql_query("SELECT id FROM forumentries WHERE topicid = '".$array['id']."'");
$numberofpages = (int)(((mysql_num_rows($query2) - 1)/25) + 2); ?>
<tr>
<td bgcolor="#E9EBFC" class="main" width="5%" align="center" style="border-left: solid 1px #000000; border-bottom: solid 1px #000000;">
<? if($array['threadstatus'] == "open"){ echo "<img src=\"images/thread_open.gif\" alt=\"Thread Open\">"; } ELSE { echo "<img src=\"images/thread_locked.gif\" alt=\"Thread Locked\">"; } ?>
</td>
<td bgcolor="#E9EBFC" class="main" width="45%" align="left" style="border-left: solid 1px #000000; border-bottom: solid 1px #000000;"><a href="showthread.php?threadid=<? echo $array['id']; ?>&page=1"><? echo stripslashes($array['topicname']); ?></a>
<? if($numberofpages > 2)
{
echo "<BR><font size=1>Page: </font>";
for ($j=1;$j<$numberofpages;$j++)
{
echo "<a href=\"showthread.php?threadid=".$array['id']."&page=$j\">$j</a> ";
}
} ?>
</td>
<td bgcolor="#E9EBFC" class="main" width="10%" align="center" style="border-left: solid 1px #000000; border-bottom: solid 1px #000000;"><B><? echo $replies; ?></B>
</td>
<td bgcolor="#E9EBFC" class="main" width="15%" align="center" style="border-left: solid 1px #000000; border-bottom: solid 1px #000000;"><a href="showme.php?u=<? echo $array['author']; ?>"><? echo $array['author']; ?></a>
</td>
<td bgcolor="#E9EBFC" class="main" width="25%" align="left" style="border-left: solid 1px #000000; border-bottom: solid 1px #000000; border-right: solid 1px #000000;"><? echo $lastpostarray['time']; ?><BR>by <a href="showme.php?u=<? echo $lastpostarray['author']; ?>"><? echo $lastpostarray['author']; ?></a>
</td>
</tr>
<? } ?>
nyy2000
Forum Newbie
Posts: 15 Joined: Tue Jul 12, 2005 12:40 am
Post
by nyy2000 » Mon Jul 25, 2005 1:34 am
How about this?
Code: Select all
$sql_text = ("SELECT id, topicname, threadstatus, author FROM forumtopicmain WHERE forumid = '$forumid' AND type != 'sticky' ORDER BY lastreply DESC LIMIT 0, 25");
s.dot
Tranquility In Moderation
Posts: 5001 Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana
Post
by s.dot » Mon Jul 25, 2005 1:53 am
I think the problem is inside the loop.
timvw
DevNet Master
Posts: 4897 Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium
Post
by timvw » Mon Jul 25, 2005 8:09 am
You could consider INNER JOIN on the tables... This way you end up with 1 query instead of multiple queries...
pickle
Briney Mod
Posts: 6445 Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:
Post
by pickle » Mon Jul 25, 2005 9:48 am
These could be 1 query, assuming you want that order for all the queries.
Code: Select all
$replies = mysql_num_rows(mysql_query("SELECT id FROM forumentries WHERE topicid = '".$array['id']."'")) - 1;
$lastpostarray = mysql_fetch_assoc(mysql_query("SELECT time, author FROM forumentries WHERE topicid = '".$array['id']."' ORDER BY time2 DESC LIMIT 1"));
$query2 = mysql_query("SELECT id FROM forumentries WHERE topicid = '".$array['id']."'");
just make your query:
Code: Select all
SELECT
time,
author,
id
FROM
forumentries
WHERE
tipicid = '$array[id]'
ORDER BY
time2 DESC
Then call mysql_num_rows for your row count, and just take the first row for your last post.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
onion2k
Jedi Mod
Posts: 5263 Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com
Post
by onion2k » Mon Jul 25, 2005 10:01 am
scrotaye wrote: I think the problem is inside the loop.
nyy2000 is spot on. Your "limit 0,25" is not being added to the sql, so it's currently getting everything from the database.
s.dot
Tranquility In Moderation
Posts: 5001 Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana
Post
by s.dot » Mon Jul 25, 2005 12:58 pm
timvw wrote: You could consider INNER JOIN on the tables... This way you end up with 1 query instead of multiple queries...
Could you please point me to, or show me an example of INNER JOIN, I've never used it before.
timvw
DevNet Master
Posts: 4897 Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium
Post
by timvw » Mon Jul 25, 2005 2:17 pm
Here is an example..
Code: Select all
SELECT *
FROM forumtopicmain AS ftm
INNER JOIN forumentries AS fe ON ftm.id=fe.topicid
nielsene
DevNet Resident
Posts: 1834 Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA
Post
by nielsene » Mon Jul 25, 2005 2:25 pm
Of course you can abbreviate "INNER JOIN" as "JOIN" -- INNER is assumed unless you have LEFT,RIGHT,FULL,CROSS, etc
This is also why I would dislike naming the primary key of each table "id".
If forumtopicsmain primary key had been topicid (so forumentries and forumtopicmain used the same name you could use any of (assuming MySQL supports this?)
Code: Select all
SELECT * FROM forumtopicmain NATURAL JOIN forumentries
SELECT * FROM forumtopicmain JOIN forumentries USING (topicid)
JOIN ... USING (column) says to do the "=" join on the matching column names. NATURAL JOIN is identical to JOIN .. USING (ALL shared columns)
timvw
DevNet Master
Posts: 4897 Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium
Post
by timvw » Mon Jul 25, 2005 3:49 pm
Once again, can't agree more
(And i've experienced that MySQL supports USING)
nielsene
DevNet Resident
Posts: 1834 Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA
Post
by nielsene » Mon Jul 25, 2005 3:55 pm
timvw wrote: Once again, can't agree more
(And i've experienced that MySQL supports USING)
Cool, I think MySQL didn't support it last time I checked, so I'm glad they do now.