Select from multiple tables

[bschultz]

I'm having a hard time grasping how to select data from several tables.

 

Here's the code

 

<?php 
require_once "config.php";
$gamedate = $_SESSION['date'];
echo "These umpires are not currently scheduled on this date, and have not asked for the day off:<br />";

$umpirelist = Array('umpire 1 name', 'umpire 2 name', 'umpire 3 name');

$dbc = mysql_pconnect($host, $username, $password);  
mysql_select_db($db,$dbc);  

foreach($umpirelist as $data) { 

//now get stuff from a table           
$sql = "SELECT calendar.date, calendar.ump1, calendar.ump2, calendar.ump3, calendar.ump4, calendar.ump5, vacation.date, vacation.umpire FROM umps, calendar, vacation WHERE umps.full_name = $data AND $gamedate = `calendar.date` AND $gamedate = `vacation.date` AND $data NOT IN ('calendar.ump1', 'calendar.ump2', 'calendar.ump3', 'calendar.ump4', 'calendar.ump5') AND $data NOT IN ('vacation.umpire') order by umps.full_name asc"; 

$rs = mysql_query($sql,$dbc);  
$matches = 0; 
while ($row = mysql_fetch_assoc($rs))  {
$matches++; 
echo "$row[$data]<br />";
}
}
?>

 

The table CALENDAR holds the date of the game, who is playing, and what umpires are schedule for that game.  it does have a unique "row_number" column as well, but didn't think I'd need it in this select.

 

The table VACATION has the date, and umpire's name that is on vacation.

 

The table UMPS contains the umpires name (same names as the array in the code) and email addresses and login info.

 

I'm getting this error:

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource on line 152

 

This is line 152

 

while ($row = mysql_fetch_assoc($rs))  {

 

Can someone please point me in the right direction as to where I'm going wrong?

 

Thanks!

[Pikachu2000]

$rs = mysql_query($sql,$dbc) or die( "<br>Query: $sql<br>Caused error: " . mysql_error() );  

[bschultz]

Here's the error:

 

Query: SELECT calendar.date, calendar.ump1, calendar.ump2, calendar.ump3, calendar.ump4, calendar.ump5, vacation.date, vacation.umpire FROM umps, calendar, vacation WHERE umps.full_name = Brian Schultz AND 2011-04-05 = `calendar.date` AND 2011-04-05 = `vacation.date` AND Brian Schultz NOT IN ('calendar.ump1', 'calendar.ump2', 'calendar.ump3', 'calendar.ump4', 'calendar.ump5') AND Brian Schultz NOT IN ('vacation.umpire') order by umps.full_name asc

Caused error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Schultz AND 2011-04-05 = `calendar.date` AND 2011-04-05 = `vacation.date` AND Br' at line 1

[Pikachu2000]

What do you see wrong with the query string? Missing some quotes around string values, perhaps? Correct that, and see if it runs or not.

[bschultz]

I hate quotes!

 

Here's the new select:

 

<?php
$sql = "SELECT calendar.date, calendar.ump1, calendar.ump2, calendar.ump3, calendar.ump4, calendar.ump5, vacation.date, vacation.umpire, umps.full_name FROM umps, calendar, vacation WHERE '$data' = `umps.full_name` AND $gamedate = `calendar.date` AND '$gamedate' = `vacation.date` AND '$data' NOT IN ('calendar.ump1', 'calendar.ump2', 'calendar.ump3', 'calendar.ump4', 'calendar.ump5') AND '$data' NOT IN ('vacation.umpire') order by `umps.full_name` asc"; 
?>

 

And the new error:

 

Query: SELECT calendar.date, calendar.ump1, calendar.ump2, calendar.ump3, calendar.ump4, calendar.ump5, vacation.date, vacation.umpire, umps.full_name FROM umps, calendar, vacation WHERE 'Brian Schultz' = `umps.full_name` AND 2011-04-05 = `calendar.date` AND '2011-04-05' = `vacation.date` AND 'Brian Schultz' NOT IN ('calendar.ump1', 'calendar.ump2', 'calendar.ump3', 'calendar.ump4', 'calendar.ump5') AND 'Brian Schultz' NOT IN ('vacation.umpire') order by `umps.full_name` asc

Caused error: Unknown column 'umps.full_name' in 'where clause'

 

umps.full_name is in the select, though...

[Pikachu2000]

MySQL thinks the entire value between the backticks is a field name in `umps.full_name`.

 

`umps`.`full_name`

OR

umps.full_name

[bschultz]

OK...thanks a bunch.  No error...but not echoing anything either.

 

SELECT calendar.date, calendar.ump1, calendar.ump2, calendar.ump3, calendar.ump4, calendar.ump5, vacation.date, vacation.umpire, umps.full_name FROM umps, calendar, vacation WHERE '2011-04-05' = calendar.date AND '2011-04-05' = vacation.date AND 'Brian Schultz' NOT IN ('calendar.ump1', 'calendar.ump2', 'calendar.ump3', 'calendar.ump4', 'calendar.ump5') AND 'Brian Schultz' NOT IN ('vacation.umpire') order by umps.full_name asc

 

I think I need to re-work the select...

 

 

[Pikachu2000]

Yeah, your NOT IN()s are probably going to need to be replaced with straight comparisons.

AND calendar.ump1 != 'Brian Schultz' AND calendar.ump2 != 'Brian Schultz', etc.

[bschultz]

I can't get this to work.  Nothing echoes no matter what I change. 

 

I'm guessing the select is wrong.

 

Here's what I need:

 

- get the list of umpires (umps.full_name)

- get the list of umpires for the date in question (calendar.ump1, calendar.ump2, calendar.ump3, calendar.ump4, calendar.ump5)

- display those umpires (umps.full_name) that are in the list of umpires, but NOT in the list (calendar.ump1, calendar.ump2, calendar.ump3, calendar.ump4, calendar.ump5) of umpires scheduled.

 

How should I construct this select (or multiple select)?

 

 

[bschultz]

I "might" be getting somewhere on this.  This code: 

 

<?php 
require_once "config.php";
$gamedate = $_SESSION['date'];
echo "These umpires are not currently scheduled on this date ($gamedate), and have not asked for the day off:<br />";

$dbc = mysql_pconnect($host, $username, $password);  
mysql_select_db($db,$dbc);  

$result = mysql_query("SELECT full_name FROM umps");
while ($names = mysql_fetch_array($result, MYSQL_BOTH)) 
{

$result2 = mysql_query("SELECT date, ump1, ump2, ump3, ump4, ump5 FROM calendar WHERE '$gamedate' = `date`");
while ($umpires = mysql_fetch_array($result2, MYSQL_BOTH)) 
{

$available = array_diff($names, $umpires);
$final_array = array_merge($available);

foreach ($final_array as $iamavailable) {
  echo $iamavailable . '<BR />';
}
}}

?>

 

Is displaying every available umpire once for EVERY game that they are NOT scheduled....not just once...TOTAL.

[bschultz]

I got it working...but there has to be a quicker way than this:

 

<?php 
require_once "config.php";
$gamedate = $_SESSION['date'];
echo "These umpires are not currently scheduled on this date ($gamedate), and have not asked for the day off:<br />";

$dbc = mysql_pconnect($host, $username, $password);  
mysql_select_db($db,$dbc);  

$sql = "SELECT full_name FROM umps ORDER BY full_name asc"; 
$rs = mysql_query($sql,$dbc);  
while($row = mysql_fetch_array($rs))
            {
                        $name_array[] = $row['full_name'];
            }

$sql = "SELECT date, ump1 FROM calendar WHERE '$gamedate' = `date`"; 
$rs = mysql_query($sql,$dbc);  
while($row = mysql_fetch_array($rs))
            {
                        $umpire1_array[] = $row['ump1'];
            }

$sql = "SELECT date, ump2 FROM calendar WHERE '$gamedate' = `date`"; 
$rs = mysql_query($sql,$dbc);  
while($row = mysql_fetch_array($rs))
            {
                        $umpire2_array[] = $row['ump2'];
            }

$sql = "SELECT date, ump3 FROM calendar WHERE '$gamedate' = `date`"; 
$rs = mysql_query($sql,$dbc);  
while($row = mysql_fetch_array($rs))
            {
                        $umpire3_array[] = $row['ump3'];
            }

$sql = "SELECT date, ump4 FROM calendar WHERE '$gamedate' = `date`"; 
$rs = mysql_query($sql,$dbc);  
while($row = mysql_fetch_array($rs))
            {
                        $umpire4_array[] = $row['ump4'];
            }

$sql = "SELECT date, ump5 FROM calendar WHERE '$gamedate' = `date`"; 
$rs = mysql_query($sql,$dbc);  
while($row = mysql_fetch_array($rs))
            {
                        $umpire5_array[] = $row['ump5'];
            }

$sql = "SELECT date, umpire FROM vacation WHERE '$gamedate' = `date`"; 
$rs = mysql_query($sql,$dbc);  
while($row = mysql_fetch_array($rs))
            {
                        $vacation_array[] = $row['umpire'];
            }


$available = array_diff($name_array, $umpire1_array, $umpire2_array, $umpire3_array, $umpire4_array, $umpire5_array, $vacation_array);

$final_array = array_merge($available);

foreach ($final_array as $iamavailable) {
  echo $iamavailable . '<BR />';
}

?>

 

Thanks for all the help Pikachu2000!