Help - My code is NOT efficient (I think?)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Chalks
Forum Contributor
Posts: 447
Joined: Thu Jul 12, 2007 7:55 am
Location: Indiana

Help - My code is NOT efficient (I think?)

Post by Chalks »

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 :banghead:

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);
  }
}
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:

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 post

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[#])
Post Reply