How could I improve this code

teynon · July 11, 2011

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.

teynon · July 11, 2011

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();
}
?>

MargateSteve · July 12, 2011

Sorry to hijack the thread but something in here was new to me.

I noticed the use of @mysql_num_rows in teynon's code. I always just use mysql_num_rows without the @ in front of it.

Does the @ do anything different to the execution?

Steve

teynon · July 12, 2011

@ 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

KevinM1 · July 12, 2011

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

Wouldn't the solution be to fix the errors rather than squelch them?

teynon · July 12, 2011

The errors are fixed. But you're missing the point. @ is basically me being paranoid. Is there a downside to this other than the fact that the user doesn't see the error message?

trq · July 12, 2011

@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.

Andy11548 · July 12, 2011

Thank you teynon, that works great, I'll revise the code and keep it into account for my further coding.

Thank you all for you help.

~Andy.

Sign In

How could I improve this code

Recommended Posts

teynon

Link to comment

Share on other sites

teynon

Link to comment

Share on other sites

MargateSteve

Link to comment

Share on other sites

teynon

Link to comment

Share on other sites

KevinM1

Link to comment

Share on other sites

teynon

Link to comment

Share on other sites

trq

Link to comment

Share on other sites

Andy11548

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information