maddenboard Posted April 5, 2007 Share Posted April 5, 2007 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>'; } } Quote Link to comment Share on other sites More sharing options...
Fergusfer Posted April 5, 2007 Share Posted April 5, 2007 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()). Quote Link to comment Share on other sites More sharing options...
btherl Posted April 5, 2007 Share Posted April 5, 2007 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"; } Quote Link to comment Share on other sites More sharing options...
Fergusfer Posted April 5, 2007 Share Posted April 5, 2007 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"; ?> Quote Link to comment Share on other sites More sharing options...
maddenboard Posted April 5, 2007 Author Share Posted April 5, 2007 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>"; } Quote Link to comment Share on other sites More sharing options...
maddenboard Posted April 5, 2007 Author Share Posted April 5, 2007 I'm not sure if bumping is allowed but I would like to get this done today.. so... bump Quote Link to comment Share on other sites More sharing options...
maddenboard Posted April 5, 2007 Author Share Posted April 5, 2007 Bump Quote Link to comment Share on other sites More sharing options...
per1os Posted April 5, 2007 Share Posted April 5, 2007 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. Quote Link to comment Share on other sites More sharing options...
maddenboard Posted April 5, 2007 Author Share Posted April 5, 2007 OK I think I understand... Thanks Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.