Combining Several Query's into one (or two...or three)

[bschultz]

I have a page that schedules officials for sporting events.  It schedules based on the priority of the official (whose turn is it to work) and who has NOT asked for the day off...and who has not listed the home team as a "bad school" (can't get to the town before gametime because of "regular" work schedules.

 

Here are all of the queries...

 

<?php
//  this gets the info for the game (sport, date, home team, and game id...results are put into variables to be used later
$sql = "SELECT sport,home,day,game_id FROM games WHERE `game_id` = '$_GET[game]'"; 

//  get all officials already working today ($day_of_game is one of the variables returned in the above query)...results are put into an array
$sql = "SELECT day,game_id FROM games WHERE `day` = '$day_of_game'"; 

//  get all officials that can work at this level...where the home team is not a "bad school" (official can't get to the school by gametime)...and the official has not requested the day off.  $values is an implode of the previous query results...these query results are put into a separate array
$sql = "SELECT ump_id FROM ump_names WHERE `$what_sport` = '1' AND ump_id NOT IN (SELECT ump_id FROM bad_school WHERE `sport` = '$what_sport' AND `school` = '$home_team') AND ump_id NOT IN (SELECT ump_id FROM days_off WHERE `day` = '$day_of_game') AND ump_id NOT IN (SELECT ump_id FROM scheduled_umps WHERE `game_id` IN ($values) )"; 

// get the row numbers of the games that are still scheduled with a TBA official
$sql = "SELECT row FROM scheduled_umps WHERE `game_id` = '$_GET[game]' AND `ump_id` = '0'";           

// get the priorty of each offical that the previous query returns.  The Highest priority will be returned first...results into a separate array
$sql = "SELECT ump_id FROM ump_priority WHERE `ump_id` IN ($values) ORDER BY priority DESC"; 

// now, get all officials priorites...in case we have to schedule an official who usually doesn't work at this level...put results into a separate array
$sql = "SELECT ump_id FROM ump_priority WHERE `association_id` = '$_SESSION[association_id]' ORDER BY priority DESC"; 

//  get all officials names from the priority check two querys ago...and put the results into an html form drop box
$sqlx = "SELECT ump_id, first_name, last_name FROM ump_names WHERE `ump_id` IN ($values2) ORDER BY find_in_set(ump_id, '$values2')";

////  this is the list of ALL officials...no matter what level they can work...for the same drop box...but located at the bottom of the list
$sqlf = "SELECT * FROM ump_names WHERE `association_id` = '$_SESSION[association_id]' AND `is_active`= '1' AND `ump_id` NOT IN ($values2) ORDER BY find_in_set(ump_id, '$values3')";

?>

 

Here's all of the code...

 

<?php
session_start();

if( !isset( $_SESSION['is_admin']) ){ 

echo "You don't have access to this page, or you're not logged in...<meta http-equiv=Refresh content=0;url='/secure/index.php'>";
} 
echo "<LINK Rel='stylesheet' Href='calendar.css' Type='text/css'>";


//include "../config.php";
putenv("TZ=US/Central");

$_SESSION['game_id_number'] = $_GET['game'];

//connect to database
$dbc = mysql_pconnect($host, $username, $password);  
mysql_select_db($db,$dbc);  

//how many umps do we need to find?  if none, exit
$sql3 = "SELECT ump_id FROM scheduled_umps WHERE `game_id` = '$_GET[game]' AND `ump_id` = '0'";           
//echo $sql3;
$rs3 = mysql_query($sql3,$dbc);  
$num_rows = mysql_num_rows($rs3);

if ($num_rows == '0') { echo "This game alread has the required number of officials."; exit; }  



//get info of game
$sql = "SELECT sport,home,day,game_id FROM games WHERE `game_id` = '$_GET[game]'"; 
//echo "$sql<br />";
$rs = mysql_query($sql,$dbc);  
while($row = mysql_fetch_array($rs))
            {
              $what_sport = $row['sport'];
	      $home_team = $row['home'];
		  $day_of_game = $row['day'];
		  $weekday = date('w', strtotime($day_of_game));
		  $game_id = $row['game_id'];
$_SESSION['game_id'] = $game_id;
		  }
		  
//get all officials working today
$sql = "SELECT day,game_id FROM games WHERE `day` = '$day_of_game'"; 
//echo "$sql<br />";
$rs = mysql_query($sql,$dbc);  
while($row = mysql_fetch_assoc($rs))
            {
                        $array_of_date[] = $row['game_id'];
		  }			  
		  
$values = implode(', ', $array_of_date);			  
		  
$sql = "SELECT ump_id FROM ump_names WHERE `$what_sport` = '1' AND ump_id NOT IN (SELECT ump_id FROM bad_school WHERE `sport` = '$what_sport' AND `school` = '$home_team') AND ump_id NOT IN (SELECT ump_id FROM days_off WHERE `day` = '$day_of_game') AND ump_id NOT IN (SELECT ump_id FROM scheduled_umps WHERE `game_id` IN ($values) )"; 
//echo "$sql<br />";
$rs = mysql_query($sql,$dbc);  
while($row = mysql_fetch_assoc($rs))
            {		
                        $available_umps_array[] = $row['ump_id'];
		  }			
$values = implode(', ', $available_umps_array);	
//echo "available array - $values<br />";
if (empty($values)) { $values = '0'; }


$sql3 = "SELECT row FROM scheduled_umps WHERE `game_id` = '$_GET[game]' AND `ump_id` = '0'";           
//echo $sql3;
$rs3 = mysql_query($sql3,$dbc);  
while($row = mysql_fetch_array($rs3)) 
            {		
                        $row_number_array[] = $row['row'];
		  }	
		  
		  


////// how many umps need to be scheduled yet
$sql3 = "SELECT ump_id FROM scheduled_umps WHERE `game_id` = '$_GET[game]' AND `ump_id` = '0'";           
//echo $sql3;
$rs3 = mysql_query($sql3,$dbc);  
$num_rows = mysql_num_rows($rs3);

echo "You still need to schedule $num_rows "; if ($num_rows == '1') { echo "official"; } else { echo "officials"; } 
echo " for this game.  Here are the officials that are NOT already working today, NOT on vacation, and CAN work at this level...and CAN work these teams.";
echo "<br /><br /><b>Officials that can work this game.  The officials in the list are prioritized from top to bottom (ie: the official listed first, has the highest priority to work this game) - - - NAMES IN RED DO NOT USUALLY WORK AT THIS LEVEL, OR ALREADY HAVE A GAME TODAY.  Under normal situations, you wouldn't select the names in red, but you can if you need to.</b><br />-------------------------------------------------------------<br />"; 


////////////get priorities
$sql = "SELECT ump_id FROM ump_priority WHERE `ump_id` IN ($values) ORDER BY priority DESC"; 
//echo "priorities select = $sql<br />";
$rs = mysql_query($sql,$dbc);  
while($row = mysql_fetch_assoc($rs))
            {
                        $priority_array[] = $row['ump_id'];
            }

//print_r ($priority_array);
$values2 = implode(',', $priority_array);





///get priorities of all officials
$sql = "SELECT ump_id FROM ump_priority WHERE `association_id` = '$_SESSION[association_id]' ORDER BY priority DESC"; 
//echo "priorities select = $sql<br />";
$rs = mysql_query($sql,$dbc);  
while($row = mysql_fetch_assoc($rs))
            {
                        $priority_array2[] = $row['ump_id'];
            }

//print_r ($priority_array);
$values3 = implode(',', $priority_array2);




echo "<br /><br /><form id='form1$i' name='form1$i' method='post' action='edit_officials_1_game.php'>";

$i = 1;
while ($i <= $num_rows) {

$sqlx = "SELECT ump_id, first_name, last_name FROM ump_names WHERE `ump_id` IN ($values2) ORDER BY find_in_set(ump_id, '$values2')";
//echo "get names select = $sqlx<br />";
$rsx = mysql_query($sqlx,$dbc);  

echo "Official Slot # $i - <label><select name='officials$i' id='officials$i'>";
while($rowx = mysql_fetch_array($rsx)) {
$full_name = "$rowx[first_name]" . " $rowx[last_name]"; 


echo "<option value='$rowx[ump_id]'>$full_name</option>";
}

//echo "<option value=''>--------------------</option>";




////  this is the list of ALL officials...no matter what level they can work
$sqlf = "SELECT * FROM ump_names WHERE `association_id` = '$_SESSION[association_id]' AND `is_active`= '1' AND `ump_id` NOT IN ($values2) ORDER BY find_in_set(ump_id, '$values3')";
//echo "get names select = $sqlx<br />";
$rsf = mysql_query($sqlf,$dbc);  
while($rowf = mysql_fetch_array($rsf)) 
{
$full_namef = "$rowf[first_name]" . " $rowf[last_name]"; 
echo "<option class='maroon' value='$rowf[ump_id]'>$full_namef</option>";

}
////  end all officials



echo "</select></label><br /><br />"; 


$q = ($i - 1);
echo "<label><input name='game_number$i' type='hidden' id='game_number$i' value='$row_number_array[$q]' /></label>";
$i++;

}
echo "<br /><br />Send Email? <label><input type='checkbox' name='send_email' id='send_email' value='1' /></label><br /><br />";  


echo "<label><input type='submit' name='button' id='button' value='Submit' /></label></form>";

$number_of_officials = $num_rows;

$_SESSION['number_of_officials'] = $number_of_officials;


//}
//echo "<br />-------------------------------------------------------------<br />If you need to schedule an official that DOES NOT ussually work this level, you can do so here.<br /><br />";




?>		

 

 

This code works...kind of.  If the $priority_array query...

 

<?php
////////////get priorities
$sql = "SELECT ump_id FROM ump_priority WHERE `ump_id` IN ($values) ORDER BY priority DESC"; 
//echo "priorities select = $sql<br />";
$rs = mysql_query($sql,$dbc);  
while($row = mysql_fetch_assoc($rs))
            {
                        $priority_array[] = $row['ump_id'];
            }

//print_r ($priority_array);
$values2 = implode(',', $priority_array);
?>

 

Comes up with no matches (meaning nobody is available to work this game that NORMALLY works this level or this school)...I get an error (IMPLODE PASSING INVALID ARGUMENTS) ...and the query to return ALL officials names (the next query in the drop box) doesn't return anything.

 

I can't help but think that there's a better way of doing this...I just don't know how.

 

Anyone care to help shed some light on this for me?

[phpSensei]

If each query is being handle differently then you will have to do this the hard way, however you are simply collecting data from each query and passing them on to a variable array container, then the WHILE loop is plausible method.

 

However you are using WAY too many tables for such a simple task, you should change the mysql structure and how data is being thrown around. Try Joining some tables...

 

For your script, you can prevent the error with a simple if statement