Jump to content

Select from multiple tables


bschultz
 Share

Recommended Posts

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!

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

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...

 

 

Link to comment
Share on other sites

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

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.