Small script uses to much CPU seconds

[7steij17]

I'm new here, Hopefully this is the right thread...

My host made me move to a dedicated server and I discovered that the only thing using so much CPU was this script:

does anyone know how I can optimize this to use less server resources, I want it to look the same but just use less resources, this code gets the job done but it uses so much CPU seconds.

Any help would be grateful, I don't know how to combine the two parts into one so it displays the same. Thanks.

[bradgrafelman]

Well, first of all, just run one query to grab the top 4 games played, store those into an array, and then loop through the array to display them in the format you need.

Second of all, never use 'SELECT *' - only select the columns that you actually need to use.

Third, why do you run the "SELECT * FROM games" query twice just to count the number of played? Not only that, but here is a much better way to do it:

$query = 'SELECT SUM(played) FROM games';
$exec = mysql_query($query);
$totalplayed = mysql_result($exec, 0);

EDIT:

7steij17 wrote:

Any help would be grateful, I don't know how to combine the two parts into one so it displays the same.

All you'd do is loop through the MySQL result set (using "LIMIT 0,4" to grab all 4 at once) and store the results into an array.

Then, replace your current while() loop with a [man]for/man loop to loop through the array twice. Output the HTML in between, then replace the second while() loop with another for() loop similar to above, continuing off where you stopped before (e.g. the last two entries in the array you created).

[7steij17]

All you'd do is loop through the MySQL result set (using "LIMIT 0,4" to grab all 4 at once) and store the results into an array.

Then, replace your current while() loop with a for() loop to loop through the array twice. Output the HTML in between, then replace the second while() loop with another for() loop similar to above, continuing off where you stopped before (e.g. the last two entries in the array you created).

                    <TR>
                      <TD>
                        <TABLE cellSpacing=0 cellPadding=0 width="100%" 
border=0>
                          <TBODY>
                          <TR>
                            <TD>
                              <TABLE cellSpacing=0 cellPadding=0 
                              width="100%" border=0>
                                <TBODY>
                                <TR>
                                <TD> 


                            </TD></TR></TBODY></TABLE></TD></TR>

                      <TR>
                        <TD>

<tr><td><table width="100%"  border="0" cellpadding="0" cellspacing="0" class="categHead"> 
<tr><td>&nbsp; Top Flash Games </td> 
</tr></table></td></tr><tr><td><table width="100%"  border="0" cellspacing="6" cellpadding="0"> 
 <tr>
<?
$sql2 = "SELECT * FROM games Where category!='Classic Nintendo' AND category!='Video' AND category!='Other' AND category!='GBC' ORDER BY played DESC LIMIT 0,2";
$result2 = mysql_query($sql2); 
while($row2 = mysql_fetch_array($result2)) {
?>

   <td width="50%" valign="top"><table width="100%" border="0" cellpadding="0" cellspacing="3" class="game"> 
  <tbody> 
    <tr align="left" valign="top"> 
      <td align="center" width="33%"><span class="normalText">
	  <a href="play-flash-<? echo $row2["gameid"]; ?>.html">	  

	  <img src="http://files.atarcade.com/img/<? echo $row2["image"]; ?>" alt="<? echo $row2["name"]; ?>" border="0" height="60" width="60"></a></span></td> 
      <td class="normalText"> <b>
	  <a href="play-flash-<? echo $row2["gameid"]; ?>.html">        <? echo $row2["name"]; ?> 
        </a></b><br> 
        <?php 

$max_length = 55;
if (strlen($row2['desc'])>$max_length)
    echo substr($row2['desc'], 0, strrpos(substr($row2['desc'], 0, $max_length), ' ')).'.';
else
    echo $row2["desc"];

?>  

</td> 
    </tr> 
  </tbody> 

</table>
</td> 
                        <?
}					
?>
<?
$sql2 = "SELECT SUM(played) AS played FROM games";
$result2 = mysql_query($sql2);
$row2 = mysql_fetch_array($result2);
$totalplayed = $row2["played"];
?>
<tr>
<?
$sql2 = "SELECT * FROM games Where category!='Classic Nintendo' AND category!='Video' AND category!='Other' AND category!='GBC' ORDER BY played DESC LIMIT 2,2";
$result2 = mysql_query($sql2); 
while($row2 = mysql_fetch_array($result2)) {
?>
   <td width="50%" valign="top"><table width="100%" border="0" cellpadding="0" cellspacing="3" class="game"> 
  <tbody> 
    <tr align="left" valign="top"> 
      <td align="center" width="33%"><span class="normalText">
	  <a href="play-flash-<? echo $row2["gameid"]; ?>.html">	  

	  <img src="http://files.atarcade.com/img/<? echo $row2["image"]; ?>" alt="<? echo $row2["name"]; ?>" border="0" height="60" width="60"></a></span></td> 
      <td class="normalText"> <b>
	  <a href="play-flash-<? echo $row2["gameid"]; ?>.html">        <? echo $row2["name"]; ?> 
        </a></b><br> 
        <?php 

$max_length = 55;
if (strlen($row2['desc'])>$max_length)
    echo substr($row2['desc'], 0, strrpos(substr($row2['desc'], 0, $max_length), ' ')).'.';
else
    echo $row2["desc"];

?>  

        <br> 
    </tr> 
  </tbody> 

</table>
</td> 
                        <?
}					
?>
<?
$sql2 = "SELECT SUM(played) AS played FROM games";
$result2 = mysql_query($sql2);
$row2 = mysql_fetch_array($result2);
$totalplayed = $row2["played"];
?>





</tr>

</table></td></tr><tr> <td><table width="100%"  border="0" cellpadding="0" cellspacing="0" class="categFoot"> 
<tr><td><div align="right"> 

<a href="top-100-games.html" class="blackLink">View Top 100 Flash Games ...</a> 

</div></td></tr></table></td></tr></table></td></tr></table>

[7steij17]

bradgrafelman wrote:

Well, first of all, just run one query to grab the top 4 games played, store those into an array, and then loop through the array to display them in the format you need.

Second of all, never use 'SELECT *' - only select the columns that you actually need to use.

Third, why do you run the "SELECT * FROM games" query twice just to count the number of played? Not only that, but here is a much better way to do it:

$query = 'SELECT SUM(played) FROM games';
$exec = mysql_query($query);
$totalplayed = mysql_result($exec, 0);

EDIT: All you'd do is loop through the MySQL result set (using "LIMIT 0,4" to grab all 4 at once) and store the results into an array.

Then, replace your current while() loop with a [man]for/man loop to loop through the array twice. Output the HTML in between, then replace the second while() loop with another for() loop similar to above, continuing off where you stopped before (e.g. the last two entries in the array you created).

How would I loop through the mysql, i know that I would do limit 0,4 but how would it look html wise, this is my current code after I made changes, thanks!

                    <TR>
                      <TD>
                        <TABLE cellSpacing=0 cellPadding=0 width="100%" 
border=0>
                          <TBODY>
                          <TR>
                            <TD>
                              <TABLE cellSpacing=0 cellPadding=0 
                              width="100%" border=0>
                                <TBODY>
                                <TR>
                                <TD> 


                            </TD></TR></TBODY></TABLE></TD></TR>

                      <TR>
                        <TD>

<tr><td><table width="100%"  border="0" cellpadding="0" cellspacing="0" class="categHead"> 
<tr><td>&nbsp; Top Flash Games </td> 
</tr></table></td></tr><tr><td><table width="100%"  border="0" cellspacing="6" cellpadding="0"> 
 <tr>
<?
$sql2 = "SELECT * FROM games Where category!='Classic Nintendo' AND category!='Video' AND category!='Other' AND category!='GBC' ORDER BY played DESC LIMIT 0,2";
$result2 = mysql_query($sql2); 
while($row2 = mysql_fetch_array($result2)) {
?>

   <td width="50%" valign="top"><table width="100%" border="0" cellpadding="0" cellspacing="3" class="game"> 
  <tbody> 
    <tr align="left" valign="top"> 
      <td align="center" width="33%"><span class="normalText">
	  <a href="play-flash-<? echo $row2["gameid"]; ?>.html">	  

	  <img src="http://files.atarcade.com/img/<? echo $row2["image"]; ?>" alt="<? echo $row2["name"]; ?>" border="0" height="60" width="60"></a></span></td> 
      <td class="normalText"> <b>
	  <a href="play-flash-<? echo $row2["gameid"]; ?>.html">        <? echo $row2["name"]; ?> 
        </a></b><br> 
        <?php 

$max_length = 55;
if (strlen($row2['desc'])>$max_length)
    echo substr($row2['desc'], 0, strrpos(substr($row2['desc'], 0, $max_length), ' ')).'.';
else
    echo $row2["desc"];

?>  

</td> 
    </tr> 
  </tbody> 

</table>
</td> 
                        <?
}					
?>
<?
$sql2 = "SELECT SUM(played) AS played FROM games";
$result2 = mysql_query($sql2);
$row2 = mysql_fetch_array($result2);
$totalplayed = $row2["played"];
?>
<tr>
<?
$sql2 = "SELECT * FROM games Where category!='Classic Nintendo' AND category!='Video' AND category!='Other' AND category!='GBC' ORDER BY played DESC LIMIT 2,2";
$result2 = mysql_query($sql2); 
while($row2 = mysql_fetch_array($result2)) {
?>
   <td width="50%" valign="top"><table width="100%" border="0" cellpadding="0" cellspacing="3" class="game"> 
  <tbody> 
    <tr align="left" valign="top"> 
      <td align="center" width="33%"><span class="normalText">
	  <a href="play-flash-<? echo $row2["gameid"]; ?>.html">	  

	  <img src="http://files.atarcade.com/img/<? echo $row2["image"]; ?>" alt="<? echo $row2["name"]; ?>" border="0" height="60" width="60"></a></span></td> 
      <td class="normalText"> <b>
	  <a href="play-flash-<? echo $row2["gameid"]; ?>.html">        <? echo $row2["name"]; ?> 
        </a></b><br> 
        <?php 

$max_length = 55;
if (strlen($row2['desc'])>$max_length)
    echo substr($row2['desc'], 0, strrpos(substr($row2['desc'], 0, $max_length), ' ')).'.';
else
    echo $row2["desc"];

?>  

        <br> 
    </tr> 
  </tbody> 

</table>
</td> 
                        <?
}					
?>
<?
$sql2 = "SELECT SUM(played) AS played FROM games";
$result2 = mysql_query($sql2);
$row2 = mysql_fetch_array($result2);
$totalplayed = $row2["played"];
?>





</tr>

</table></td></tr><tr> <td><table width="100%"  border="0" cellpadding="0" cellspacing="0" class="categFoot"> 
<tr><td><div align="right"> 

<a href="top-100-games.html" class="blackLink">View Top 100 Flash Games ...</a> 

</div></td></tr></table></td></tr></table></td></tr></table>

[bradgrafelman]

Basically, you're just going through all of the results at once and storing them in an array:

$sql = "SELECT * FROM games Where category!='Classic Nintendo' AND category!='Video' AND category!='Other' AND category!='GBC' ORDER BY played DESC LIMIT 0,4";
$result = mysql_query($sql);

$data = array();
while($row = mysql_fetch_array($result))
    $data[] = $row;

Then when you wanted to grab two, you could use a [man]for/man loop:

for($i=0; $i<=2; $i++) {
?>blah blah... html.... id: <?php echo $data[$i]['gameid']; ?> etc. etc.
<?php
}?>
more html...
blah....

and now for the second set of two: <?php
for(; $i<=4; $i++) {
?>blah blah... again, id: <?php echo $data[$i]['gameid']; ?> etc.
<?php
}

Once again, however, I don't understand why you have two queries to get the number played... just do the query once...