Code Optimization

[BulldogGC]

Wondering if any of you see something I missed that could potentially improve the efficiency of this little snippet of code.

 $currentdate = date("m/d/y");
 $currenttime = date("H:i:s");
		$sql = "SELECT subject,genreid,content,authorid,timecreated,hotitem,comments
				FROM news					
			    WHERE datecreated = '$currentdate' 
				 AND timecreated < '$currenttime'
				ORDER BY timecreated DESC";
		$execute_current = mysql_query($sql);									
			$num = mysql_num_rows($execute_current);
			$i = 0;
				while($i < $num) {
					$subject = mysql_result($execute_current,$i,subject); 
					$content = mysql_result($execute_current,$i,content);
					$hotitem = mysql_result($execute_current,$i,hotitem);
					$comments = mysql_result($execute_current,$i,comments);
					$timecreated = mysql_result($execute_current,$i,timecreated);
						$timecreated = date("h:i A",strtotime($timecreated));
					// fetch author code
					$authorid = mysql_result($execute_current,$i,authorid);
						$execute_author = mysql_query("SELECT realname FROM admins WHERE id = $authorid");
						$realname = mysql_result($execute_author,realname);
					// fetch genre code
					$genreid = mysql_result($execute_current,$i,genreid);
						$execute_genre = mysql_query("SELECT name FROM genres WHERE id = $genreid");
						$genrename = mysql_result($execute_genre,name);
							if($hotitem == 1) { $hotitem_tag = "<font size='1' color='#FF0000'><strong>(HOT ITEM)</strong></font>"; } else { $hotitem_tag = ""; }
							echo "<font class='primary_header'>$subject</font> $hotitem_tag<br>
									<font class='small'>[ <a href='[e-mail]'>$realname</a> | $timecreated | $genrename";
										if($comments == 1) { echo " | COMMMENT COUNT"; }
							echo " ]</font><br>
										$content
											<p></p>";
								$i++;
				}

Any suggestions welcome, thanks.

[zingbats]

umm there are programs around that do it

http://www.zend.com/store/products/zend-optimizer.php

is a good one

[BulldogGC]

I'm not really looking for server software.
Just actual, physical, code optimization suggestions.

[Weedpacket]

Coupla suggestions or so:

1. Use a for() loop instead of simulating one with a while()

2. Use mysql_fetch_array() instead of a swarm of mysql_result()s

3. Use string catenation instead of variable interpolation (i.e., "thing".$var instead of "thing$var"

4. Big blocks of text that don't need PHP to massage them are output faster if they're outside <?php ?> tags - i.e., ?>lotsanonphptext<?php is faster than echo "lotsanonphptext";

There's probably some way of taking those queries out of the loop and running them once each; something like selecting all the authors, genres you'll likely be looking at (the query will involve some sort of join ... ah....

select admins.realname from admins, news where admins.id = news.authorid and datecreated= ...etc.

is a first approximation. Store all of those names in an array which you can look over when you want to. Or am I right in guessing there's only one realname per authorid and one name per genreid? that probable makes it simpler; no need for three queries, just fetch them all out at the same time with just one

SELECT n.subject, n.genreid, n.content, ..., a.realname, g.name from news n, admins a, genres g where n.datecreated= .... and a.id=n.authorid and g.id=n.genreid order by timecreated desc

[BulldogGC]

EDIT: Worked on the code some more.
EDIT #2: More little changes.

<?
 $currentdate = date("m/d/y");
 $currenttime = date("H:i:s");
		$sql = "SELECT subject,genreid,content,authorid,timecreated,hotitem,comments
				FROM news					
			    WHERE datecreated = '$currentdate' 
				 AND timecreated < '$currenttime'
				ORDER BY timecreated DESC";
		$execute_current = mysql_query($sql);									
			while($row = mysql_fetch_array($execute_current)) {
				// hot item code				
					if($row['hotitem'] == 1) { $hotitem_tag = "<font size='1' color='#FF0000'><strong>(HOT ITEM)</strong></font>"; } else { $hotitem_tag = ""; }
				// timecreated code
					$timecreated = date("h:i A",strtotime($row['timecreated']));
				// genre & author code
					$genreid = $row['genreid'];
					$authorid = $row['authorid'];			
						$execute_ag = mysql_query("SELECT a.realname,g.name,g.icon FROM admins a, genres g WHERE a.id = $authorid AND g.id = $genreid");
							while($row_genre = mysql_fetch_array($execute_ag)) {
								$icon = $row_genre['icon'];
									if ($icon !== "") { $icontag = "<img src='$direct_path/images/icons/$icon'>"; } else { $icontag = ""; }												

?>									<font class="primary_header"><? echo $row['subject']; ?></font> <? echo "$hotitem_tag" ?><br>
										<font class="small">[ <a href="[e-mail]"><? echo $row_genre['realname']; ?></a> | <? echo "$timecreated" ?> | <? echo $row_genre['name']; if($row[comments] == 1) { echo " | COMMMENT COUNT"; } ?> ]</font><br>
											<? echo "$icontag"; ?>
											<? echo $row[content]; ?>
												<p></p>
<? } } ?>

Anymore suggestions?
Thanks for the advice weed, always appreciated.

[Weedpacket]

Save a call to date() by having the first line

list($currentdate, $currenttime) = explode(' ',date('m/d/y H:i:s'));

? I think explode() is cheaper than date() - haven't tested to find out...