Subquery is failing

[bschultz]

I'm trying to write a subquery (my first)...and I can't get it to work right.

 

<?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'>";
} 

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



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


//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'];

		  }
		  
//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, sport, school FROM bad_school WHERE `sport` = '$what_sport' AND `school` = '$home_team') AND ump_id NOT IN (SELECT ump_id, day, dow FROM days_off WHERE `day` = '$day_of_game') AND ump_id NOT IN (SELECT ump_id, game_id FROM scheduled_umps WHERE `game_id` = '$values') "; 
echo "$sql<br />";
$rs = mysql_query($sql,$dbc);  

if ($rs){ 

echo "This worked";
  } 
  else{ 
    echo mysql_errno().": ".mysql_error()."<BR>"; 
  } 

while($row = mysql_fetch_assoc($rs))
            {		
                        $available_umps_array[] = $row['ump_id'];
		  }			
$values = implode(', ', $available_umps_array);	
echo "$values<br />";
?>		

 

Here is the error

 

SELECT ump_id FROM ump_names WHERE `bb_baberuth` = '1' AND ump_id NOT IN (SELECT ump_id, sport, school FROM bad_school WHERE `sport` = 'bb_baberuth') AND ump_id NOT IN (SELECT ump_id, sport, school FROM bad_school WHERE `school` = 'Bemidji Lions') AND ump_id NOT IN (SELECT ump_id, day, dow FROM days_off WHERE `day` = '2011-06-06') AND ump_id NOT IN (SELECT ump_id, game_id FROM scheduled_umps WHERE `game_id` = '2, 3')

 

1241: Operand should contain 1 column(s)

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/briansch/public_html/bemidjiumps.com/secure/pages/test.php on line 56

 

Warning: implode() [function.implode]: Invalid arguments passed in /home/briansch/public_html/bemidjiumps.com/secure/pages/test.php on line 60

 

What is 1241: Operand should contain 1 column(s)...and how do I fix it.

[jcbones]

If you are using a subquery inside of the IN() function, you can ONLY call ONE column, you are selecting it to return 2 and 3 columns in each.  In each one of those subqueries, you should be only selecting the 'ump_id', since that is the column you are checking against.

[bschultz]

Cool...thanks!

[DavidAM]

In the context you are using, a subquery can return only ONE column. You are returning multiple columns in each of the subqueries. Also the last subquery needs to use IN instead of equals (=). The query should be something like this:

 

$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') ) "; 

 

jcbones beat me to it. But he did not mention the other problem in the last subquery.

[bschultz]

Thanks again for the help...

 

The next part of this code is to take that list of id's...sort them by priority, and spit out a drop box.

 

<?php
$sql = "SELECT ump_id, priority 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_array($rs))
            {
                        $priority_array[] = $row['ump_id'];
            }

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



$sqlx = "SELECT ump_id, first_name, last_name FROM ump_names WHERE `ump_id` IN ('$values2')";
echo "get names select = $sqlx<br />";
$rsx = mysql_query($sqlx,$dbc);  
echo "<br /><br /><form id='form1' name='form1' method='post' action='edit_officials_1_game.php'>";
while($rowx = mysql_fetch_array($rsx)) {
$full_name = "$rowx[first_name]" . " $rowx[last_name]"; 
echo "<label><select name='officials' id='officials'>";
echo "<option value='$rowx[ump_id]'>$full_name</option>";

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

?>

 

This returns...

 

priorities select = SELECT ump_id, priority FROM ump_priority WHERE `ump_id` IN ('1, 2, 4, 5, 6, 10, 14, 15, 18') ORDER BY priority DESC

 

get names select = SELECT ump_id, first_name, last_name FROM ump_names WHERE `ump_id` IN ('1')

 

Two problems...the highest priority is id #4...so why isn't 4 showing up first...and why is it only returning 1 row...instead of the 9 that it should?

[jcbones]

ORDER BY doesn't reflect on the query going TO the database, it reflects on the results COMING from the database.

 

BTW, remove the single quotes from around the $values, as you are passing them as integers,  Those single quotes are saying that you want to pass ALL of those numbers as 1 string parameter.

[bschultz]

Thanks for the help with the extra quotes.  That got me all the results that it should have. 

 

So, how can I order the final drop box by the priority...which is in a prior query?

[jcbones]

What is your results now? Before, you were only getting 1 result, so the order by was non existent.

[bschultz]

I stumbled across find_in_set...which still isn't working.

 

<?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);



$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 "<br /><br /><form id='form1' name='form1' method='post' action='edit_officials_1_game.php'>";
echo "<label><select name='officials' id='officials'>";
while($rowx = mysql_fetch_array($rsx)) {
$full_name = "$rowx[first_name]" . " $rowx[last_name]"; 

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

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

 

priorities select = SELECT ump_id FROM ump_priority WHERE `ump_id` IN (1, 2, 4, 5, 6, 10, 14, 15, 18) ORDER BY priority DESC

 

Array ( [0] => 4 [1] => 1 [2] => 2 [3] => 5 [4] => 6 [5] => 10 [6] => 14 [7] => 15 [8] => 18 )

 

get names select = SELECT ump_id, first_name, last_name FROM ump_names WHERE `ump_id` IN (4, 1, 2, 5, 6, 10, 14, 15, 18) ORDER BY find_in_set(ump_id, '4, 1, 2, 5, 6, 10, 14, 15, 18')

 

The order of the array after priorities select = is correct...but when the get_names_select results come back, they are ordered by ump_id...1, 2, 4, 5, 6, 10, 14, 15, 18.

[jcbones]

Is there any reason you cannot join the two queries together?

 

$sql = "SELECT 
name.ump_id,
name.first_name,
name.last_name
FROM
ump_names AS name
JOIN
ump_priority AS up
ON
name.ump_id = up.ump_id
WHERE
up.ump_id
IN ( 
$values 
)
ORDER BY 
up.priority 
DESC
";

[bschultz]

Is join faster?  I did find the problem...

 

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

 

should be

 

$values2 = implode(',', $priority_array);  //remove space after comma

 

I'll redo the code if it will run better.

 

 

[jcbones]

Join is a lot faster, with less overhead, and with less problems to crop up in the future.