[SOLVED] Optimizing code for less queries?

[maddenboard]

The way I currently have my code written is running 21 queries

 

I am looking for help on optimizing the code.  First question is would running one query and then using PHP to run through a array be better than running the 21 queries? (faster less server intensive?)

 

Second if it would be how could I rewrite this to be more optimized?

 

    
$mon = array('0' => '', '1'=>'','2'=>'','3'=>'','4'=>'',
                '5'=>'', '6'=>'', '7'=>'', '8'=>'', '9'=>'',
                '10'=>'', '11'=>'', '12'=>'', '13'=>'', '14'=>'', '15'=>'',
                '16'=>'', '17'=>'', '18'=>'', '19'=>'', '20'=>'');
$mon_fields = array('id','firstname','lastname','position','OVR','OYR','YRL','CSAL','AGE');
$defaultend = array('MOR','STA','INJ','TGH');
$what = $mon;
foreach($what as $key1 => $val1) {
      $totalfields = $mon_fields;
     $totalfields = array_merge($totalfields, $defaultend);
      $sql = "SELECT ";
  $i=0;
      foreach($totalfields as $key2) {
          $sql .= "$key2, ";
      }
      $sql = preg_replace("/, $/", " ", $sql);
      $sql .= " FROM $tbl_players 
                WHERE team='".$team_ident."' 
                AND position='$key1'";

      $result = cnt_mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $sql . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());

      while($row = mysql_fetch_row($result)) {
        foreach($row as $field) {
	     echo '<td class="roster">'.$field.'</td>';
	 }
  }

[Fergusfer]

This looks like a case for "WHERE field IN (item1, item2, ..., itemN)".

 

I don't really understand what you're trying to do specifically, but if you need to match a set of 21 possible values of the position column, use this:

 

<?php

// ... 
$positions = implode(',', array_keys($what));
$sql .= "WHERE team='$team_ident' AND position IN ($positions)";

?>

 

I don't understand why you are creating an array of keys will null string values.  This is inefficient (necessitates the call to array_keys() in order to provide valid input to implode()).

[btherl]

A possible replacement for that array would be

 

$mon = range(0, 20);
$positions = implode(',', $mon);
$sql .= "WHERE team='$team_ident' AND position IN ($positions)";

 

If you wanted to use that array in a foreach loop, you would do

 

foreach ($mon as $m) {
  print "Array element is $m\n";
}

[Fergusfer]

Good point.  I didn't notice the array's keys were sequential.

 

Since I assume we're dealing with an INT field in the database, this WHERE clause might be preferable:

 

<?php
$sql .= "WHERE team='$team_ident' AND position BETWEEN 0 AND 20";
?>

 

[maddenboard]

I took a lot of the code out and only showed the basic structure of what I am doing as to not have a bunch of code in there that is not pertinent to my question.

 

But I guess I left out more important info in doing so.

 

1) There are only 21 positions 0-20.

 

2) I have another array with certain fields I print out in a table for each position.

 

I guess I will just post the whole code.

 

$default = array('id','firstname', 'lastname', 'position', 'OVR', 'AGE', 'SPD', 'AWR', 'ACC', 'AGI');
   $defaultend = array('MOR','STA','INJ','TGH');
   $off = array(
      0 => array('THP','THA','CAR'),
      1 => array('STR','CTH','CAR','BTK'),
      2 => array('STR','CTH','CAR','BTK','RBK','PBK'),
      3 => array('JMP','CTH','BTK','CAR','RBK','PBK'),
      4 => array('STR','CTH','BTK','CAR','RBK','PBK'),
      5 => array('STR','RBK','PBK'),
      6 => array('STR','RBK','PBK'),
      7 => array('STR','RBK','PBK'),
      8 => array('STR','RBK','PBK'),
      9 => array('STR','RBK','PBK'),
   );
$d=array('SPD','STR','JMP','CTH','TAK');
   $def = array(
      10 => $d,
      11 => $d,
      12 => $d,
      13 => $d,
      14 => $d,
      15 => $d,
      16 => $d,
      17 => $d,
      18 => $d,
      19 => array('KPW','KAC'),
      20 => array('KPW','KAC'),
   );

   $mon = range(0, 20);

   $mon_fields = array('id','firstname','lastname','position','OVR','OYR','YRL','CSAL','AGE');

   $prog = array(
      0 => array('AWR','SPD','AGI','ACC','JMP','THP','THA'),
      1 => array('AWR','SPD','AGI','ACC','JMP','CAR','BTK','PBK','RBK','STA'),
      2 => array('AWR','SPD','AGI','ACC','JMP','CAR','BTK','PBK','RBK','STA'),
      3 => array('AWR','SPD','AGI','ACC','JMP','CTH','CAR','RBK','STA'),
      4 => array('AWR','SPD','AGI','ACC','JMP','CTH','CAR','PBK','RBK','STA'),
      5 => array('AWR','SPD','AGI','ACC','JMP','STR','PBK','RBK','STA'),
      6 => array('AWR','SPD','AGI','ACC','JMP','STR','PBK','RBK','STA'),
      7 => array('AWR','SPD','AGI','ACC','JMP','STR','PBK','RBK','STA'),
      8 => array('AWR','SPD','AGI','ACC','JMP','STR','PBK','RBK','STA'),
      9 => array('AWR','SPD','AGI','ACC','JMP','STR','PBK','RBK','STA'),
      10 => array('AWR','SPD','AGI','ACC','JMP','TAK','STA'),
      11 => array('AWR','SPD','AGI','ACC','JMP','TAK','STA'),
      12 => array('AWR','SPD','AGI','ACC','JMP','TAK','STA'),
      13 => array('AWR','SPD','AGI','ACC','JMP','TAK','STA'),
      14 => array('AWR','SPD','AGI','ACC','JMP','TAK','STA'),
      15 => array('AWR','SPD','AGI','ACC','JMP','TAK','STA'),
      16 => array('AWR','SPD','AGI','ACC','JMP','TAK','STA'),
      17 => array('AWR','SPD','AGI','ACC','JMP','TAK','STA'),
      18 => array('AWR','SPD','AGI','ACC','JMP','TAK','STA'),
      19 => array('AWR','SPD','KPW','KAC','STA'),
      20 => array('AWR','SPD','KPW','KAC','STA'),
   );


if($_GET['type'] == 'off')
   $what = $off;
if($_GET['type'] == 'def')
   $what = $def;
if($_GET['type'] == 'mon')
   $what = $mon;

if($_GET['type'] == 'off' || $_GET['type'] == 'def' || $_GET['type'] == 'mon'){
   foreach($what as $key1 => $val1) {
      echo "<div class=\"roster\"><table border=1>\n";
      echo "<tr style=\"background-color: ".$team_sql['th_bg_color'].";\">\n";
   if($_GET['type'] == 'off' || $_GET['type'] == 'def') {
      $totalfields = array_merge($default, $val1);
   }
   if($_GET['type'] == 'mon') {
      $totalfields = $mon_fields;
   }
   $totalfields = array_merge($totalfields, $defaultend);
      $sql = "SELECT ";
  $i=0;
      foreach($totalfields as $key2) {
          $sql .= "$key2, ";
	  if($i==0){$i++; continue;}
	  echo "<th style=\"color: ".
                 $team_sql['th_text_color'].
                 "; background: ".$team_sql['th_bg_color'].";\">";
         echo ($abs[$key2]) ? $abs[$key2] : $key2;
         echo "</th>\n";
         
      }
      echo "</tr>\n";
      if(!$order){
	  $order='OVR';
  }
      $sql = preg_replace("/, $/", " ", $sql);
      $sql .= " FROM $tbl_players 
                WHERE team='".$team_ident."' 
                AND position='$key1'
                ORDER BY $order DESC";
      $result = cnt_mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $sql . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());
;
      while($row = mysql_fetch_row($result)) {
         echo "<tr>";
	 $xcount=0;
	 $playerid=$row[0];
         foreach($row as $field) {
		 if($xcount==0){$xcount++; continue;}

            /* If no field is found, set 'n/a' */
            $field = ($field != '') ? $field : 'n/a';
            if($xcount==1 || $xcount==2){
			echo '<td class="roster"><a href=player_profile.php?pid='.$playerid.'>'.$field.'</td>';
	 }
	 elseif($xcount==3){
		echo '<td class="roster">'.$arr_position[$field].'</td>';
	 }
	 elseif($_GET['type'] == mon && $xcount==7){
	 //addition for calculating total salary
         $totsal = ($totsal+($field*10000));
	 $field = $field*10000;
	 $field = number_format($field);
		 echo '<td class"roster">$'.$field.'</td>';
	 }
	 else{
            echo '<td class="roster">'.$field.'</td>';
	 }
	    $xcount++;
         }
         echo "<tr>\n";
      }
   
      echo "</table></div>";
      echo "<p></p>";
   }

[maddenboard]

I'm not sure if bumping is allowed but I would like to get this done today.. so... bump

[maddenboard]

Bump

[per1os]

Bumping is ok, but asking for a specific time is not. If someone knows the answer they will help, but sometimes it takes time for the right person to respond.

 

To answer the query question, yes running 1 query and letting php process is a way better idea because php can handle the code alot quicker than MySQL and than it won't tie up your DB like 21 queries would.

 

What I would do is just grab all the rows from the table that are relevant, if you are going to select all the rows anyways just do it in one shot, but make sure you order by what you needed it ordered by, IE playerid or field ID or first teamname than playername than field etc.

 

That way you can just use the foreach through the array and save your SQL server the payload.

[maddenboard]

OK I think I understand...

 

Thanks