Help - My code is NOT efficient (I think?)
Posted: Mon Jan 21, 2008 1:50 pm
I think the key to fixing my code is going to be using joins of some sort. However, every time I've tried to wrap my head around what an innerjoin, left join, or what have you... I've ended up
Here's my code:
As you can see, I have 7 queries. I have a feeling that it is far more than I actually need, but I don't know how to fix it. The above code DOES work, but I think that if I was dealing with a few hundred hits a day, and a larger forum, it would get bogged down rapidly. Here are what my tables look like:
Any help getting this optimized a bit would be greatly appreciated. If it's a matter of using joins (which I suspect), a description of how joins work would be great. You already know how I feel about them.
Thanks!
edit: changed the code quoted to be more clear (used array['name'] instead of array[#])
Here's my code:
Code: Select all
$sql = "SELECT posts, type FROM threads WHERE subcat=$n AND type='announcement'";
buildThread($sql);
// there's a similar statement for building sticky threads, and normal/locked threads.
function buildThread($sql)
{
$result = mysql_query($sql) or die(mysql_error());
while($data=mysql_fetch_array($result))
{
$threadID = $data['posts'];
$type = $data['type'];
$sql = "SELECT COUNT(threads) FROM posts WHERE threads='$threadID'";
$data111 = mysql_fetch_array(mysql_query($sql));
$replies = $data111['COUNT(threads)'] - 1;
$sql = "SELECT * FROM posts WHERE threads='" . $data['posts'] . "' ORDER BY dated ASC LIMIT 1";
$result = mysql_query($sql) or die(mysql_error());
$data = mysql_fetch_array($result);
$linkTo = $data['threads'];
$dated = $data['dated'];
$title = $data['texts'];
$posterID = $data['poster'];
$sql = "SELECT name FROM user WHERE id='$posterID'";
$data = mysql_fetch_array(mysql_query($sql));
$poster = $data['name'];
$sql = "SELECT title FROM texts WHERE id='$title'";
$data = mysql_fetch_array(mysql_query($sql));
$title = $data['title'];
// Get data for last post
$sql = "SELECT * FROM posts WHERE threads='$threadID' ORDER BY dated DESC LIMIT 1";
$result = mysql_query($sql) or die(mysql_error());
$data = mysql_fetch_array($result);
$datedLast = $data['dated'];
$posterLastID = $data['poster'];
$sql = "SELECT name FROM user WHERE id='$posterLastID'";
$data = mysql_fetch_array(mysql_query($sql));
$posterLast = $data['name'];
writeThread($type, $linkTo, $title, $poster, $posterID, $dated, $replies, '12', $posterLast, $posterLastID, $datedLast);
}
}Code: Select all
subcats fields:
id // auto incrementing
cat // the category the subcategory belongs to (i.e. General Discussion belongs to the category 'General')
name // Name of the subcategory (i.e. 'General Discussion')
description
place // What order to display the subcategories in
perms // permissions
threads fields:
id // auto incrementing
type // 'Announcement', 'Sticky', etc.
subcat // the subcategory the thread belongs to
posts //the initial post id # in the thread
perms // I decided to put the permissions everywhere... maybe not needed?
posts fields:
id // auto incrementing
threads // thread id # that this post belongs to
poster // poster of this particular post
texts // text id # for this post
dated // date posted
perms // again, not sure if I need this in all three tables
texts fields:
id // auto incrementing
title // the title.
content // the text belonging to a postAny help getting this optimized a bit would be greatly appreciated. If it's a matter of using joins (which I suspect), a description of how joins work would be great. You already know how I feel about them.
Thanks!
edit: changed the code quoted to be more clear (used array['name'] instead of array[#])