Jump to content

How could I improve this code


Andy11548

Recommended Posts

Ok, i further modified the query a little bit to match the code.

 

This code:

<?php
$id_link=mysql_connect("localhost", "root", "");
mysql_select_db("testing");

$sql="SELECT 
t3.name as Name,
t1.name as SubName, 
t1.description as SubDescr,
t4.topic_name as TopicName, 
t4.id as TopicId,
t4.date as ReplyDate,
t4.time as ReplyTime, 
t5.username as Username,
t5.id as UserID,
(SELECT COUNT(*) FROM forum_topics as t6 WHERE t6.sub_cat_id = t1.id) as Topics,
(SELECT COUNT(*) FROM forum_replys as t7 WHERE t7.sub_cat_id = t1.id) as Replies

FROM 
forum_cats as t3
	LEFT JOIN forum_subs as t1 
		ON t3.name = t1.cat 
	LEFT JOIN forum_topics as t2 
		ON t2.sub_cat_id = t1.id 
	LEFT JOIN forum_replys as t4 
		ON t4.sub_cat_id = t1.id 
	LEFT JOIN users as t5 
		ON t5.username = t4.username 
GROUP BY t1.name 
ORDER BY 
t3.id ASC, t1.name ASC, t4.id DESC";

$lastBoard="";
if ($query=@mysql_query($sql)) {
	if (@mysql_num_rows($query) > 0) {

		?>
		<div id="forumContainer">
		<div id="forumHolder">
		<?php

		while ($req=@mysql_fetch_array($query)) {
			if ($req['Name'] != $lastBoard) {
				$lastBoard=$req['Name'];
				?><div class="forumCats"><?php echo $req['Name'];?></div><?php
			}
			?>
			<div class="forumSubs">
				<div class="subsLeft"><a href="view.php?id=<?php echo $req['TopicId'];?>&currentpage=1">
				<?php echo $req['SubName'];?></a><br />
				<span><?php echo $req['SubDescr'];?></span>
				</div>


				<div class="subsRightH">
					<div class="subsRLeft">
					Posts: <?php echo $req['Replies'];?><br />
					Topics: <?php echo $req['Topics'];?>
					</div>

					<div class="subsRRight">
					<?php
					if ($req['Replies'] > 0) {
						?>
						Last post by: <a href="profile.php?id=<?php echo $req['UserID'];?>"><?php echo $req['Username'];?></a><br />
						in: <a href="topic.php?id=<?php echo $req['TopicId'];?>&currentpage=1"><?php echo $req['TopicName'];?></a><br />
						<?php
                              
						if ($req['ReplyDate'] == date('d/m/Y')) {
							echo "<strong>Today</strong> at {$req['ReplyTime']}";
						}
						else {
							echo "on {$req['ReplyDate']} at {$req['ReplyTime']}";
						}
					}
					else {
						echo "No posts";
					}
					?>
					</div>
				</div>
				<div class="clearfloat"></div>
			</div>
			<?php
		}
		?></div></div><?php
	}
}
else {
	echo mysql_error();
}
?>

 

outputs the exact same thing as your code:

<?php
$id_link=mysql_connect("localhost", "root", "");
mysql_select_db("testing");

$catsQ = mysql_query("SELECT * FROM `forum_cats` ORDER BY `id` ASC");
$catsF = mysql_fetch_assoc($catsQ);
?>

<div id="forumContainer">
   <div id="forumHolder">
      <?php
         $catsQ = mysql_query("SELECT * FROM `forum_cats`");
         $catsF = mysql_fetch_assoc($catsQ);
         
         do
         {
            echo '<div class="forumCats">'.$catsF['name'].'</div>';
            
            $subsQ = mysql_query("SELECT * FROM `forum_subs` WHERE `cat`='".$catsF['name']."'");
            $subsF = mysql_fetch_assoc($subsQ);
            
            do
            {
               $topicQ = mysql_query("SELECT * FROM `forum_topics` WHERE `sub_cat_id`='".$subsF['id']."'");
               $topicR = mysql_num_rows($topicQ);
               
               $replyQ = mysql_query("SELECT * FROM `forum_replys` WHERE `sub_cat_id`='".$subsF['id']."' ORDER BY `id` DESC");
               $replyF = mysql_fetch_assoc($replyQ);
               $replyR = mysql_num_rows($replyQ);

               $uidQ = mysql_query("SELECT * FROM `users` WHERE `username`='".$replyF['username']."'");
               $uidF = mysql_fetch_assoc($uidQ);

               echo '<div class="forumSubs">
                        <div class="subsLeft"><a href="view.php?id='.$subsF['id'].';currentpage=1">'.$subsF['name'].'</a><br /><span>'.$subsF['description'].'</span></div>
                        <div class="subsRightH">
                           <div class="subsRLeft">Posts: '.$replyR.'<br />Topics: '.$topicR.'</div>
                           <div class="subsRRight">';
                           
                           if($replyR > 0)
                           {
                              echo 'Last post by: <a href="profile.php?id='.$uidF['id'].'">'.$replyF['username'].'</a><br />in: <a href="topic.php?id='.$replyF['topic_id'].';currentpage=1">'.$replyF['topic_name'].'</a><br />';
                              if($replyF['date'] == date('d/m/Y'))
                              {
                                 echo '<strong>Today</strong> at '.$replyF['time'];
                              } else {
                                 echo 'on '.$replyF['date'].' at '.$replyF['time'];
                              }
                           } else {
                              echo 'No posts';
                           }
               echo        '</div>
                        </div>
                        <div class="clearfloat"></div>
                     </div>
                     ';
            } while($subsF = mysql_fetch_assoc($subsQ));

         } while($catsF = mysql_fetch_assoc($catsQ));
      ?>
   </div>
</div>

 

However, my code (top) only uses one query.

Link to comment
Share on other sites

Correction (Forgot sub category id / link)

 

<?php
$id_link=mysql_connect("localhost", "root", "");
mysql_select_db("testing");

$sql="SELECT 
t3.name as Name,
t1.name as SubName,
t1.id as SubId,
t1.description as SubDescr,
t4.topic_name as TopicName, 
t4.id as TopicId,
t4.date as ReplyDate,
t4.time as ReplyTime, 
t5.username as Username,
t5.id as UserID,
(SELECT COUNT(*) FROM forum_topics as t6 WHERE t6.sub_cat_id = t1.id) as Topics,
(SELECT COUNT(*) FROM forum_replys as t7 WHERE t7.sub_cat_id = t1.id) as Replies

FROM 
forum_cats as t3
	LEFT JOIN forum_subs as t1 
		ON t3.name = t1.cat 
	LEFT JOIN forum_topics as t2 
		ON t2.sub_cat_id = t1.id 
	LEFT JOIN forum_replys as t4 
		ON t4.sub_cat_id = t1.id 
	LEFT JOIN users as t5 
		ON t5.username = t4.username 
GROUP BY t1.name 
ORDER BY 
t3.id ASC, t1.name ASC, t4.id DESC";

$lastBoard="";
if ($query=@mysql_query($sql)) {
	if (@mysql_num_rows($query) > 0) {

		?>
		<div id="forumContainer">
		<div id="forumHolder">
		<?php

		while ($req=@mysql_fetch_array($query)) {
			if ($req['Name'] != $lastBoard) {
				$lastBoard=$req['Name'];
				?><div class="forumCats"><?php echo $req['Name'];?></div><?php
			}
			?>
			<div class="forumSubs">
				<div class="subsLeft"><a href="view.php?id=<?php echo $req['SubId'];?>&currentpage=1">
				<?php echo $req['SubName'];?></a><br />
				<span><?php echo $req['SubDescr'];?></span>
				</div>


				<div class="subsRightH">
					<div class="subsRLeft">
					Posts: <?php echo $req['Replies'];?><br />
					Topics: <?php echo $req['Topics'];?>
					</div>

					<div class="subsRRight">
					<?php
					if ($req['Replies'] > 0) {
						?>
						Last post by: <a href="profile.php?id=<?php echo $req['UserID'];?>"><?php echo $req['Username'];?></a><br />
						in: <a href="topic.php?id=<?php echo $req['TopicId'];?>&currentpage=1"><?php echo $req['TopicName'];?></a><br />
						<?php
                              
						if ($req['ReplyDate'] == date('d/m/Y')) {
							echo "<strong>Today</strong> at {$req['ReplyTime']}";
						}
						else {
							echo "on {$req['ReplyDate']} at {$req['ReplyTime']}";
						}
					}
					else {
						echo "No posts";
					}
					?>
					</div>
				</div>
				<div class="clearfloat"></div>
			</div>
			<?php
		}
		?></div></div><?php
	}
}
else {
	echo mysql_error();
}
?>

Link to comment
Share on other sites

@ is error suppression. Basically if I did $query="SELECT * FROM BLAH WTF THIS IS NOT A SQL QUERY"; mysql_query($sql); and your server has warnings enabled, you could have errors pop up and reveal things you might not want to reveal. I just formed a habit over time of error suppression so that end users don't see them.

 

http://php.net/manual/en/language.operators.errorcontrol.php

Link to comment
Share on other sites

@thorpe, force of habit. I don't like errors / warnings in production :/

 

Even without the error suppression, there was no code in your example that would cause an error. Using error suppression would just slow down the process.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.