Stuck on a WHERE statement pulling date from 2 tables

[peanutgallery83]

Okay, here's the situation. I have a table where a schedule of events (games) are stored "schedule". I have another table "availability" where data is stored for users to indicate availability to work.

In the following code I am displaying the schedule in a table with drop down menus for the 3 referee positions that need to be assigned. Arrays are creating the schedule and drop downs.

The problem is, I need it so that it only displays the names of individuals who said they are available on the date of that game.

Example: If the date is Jan 20, 2005 in the schedule (2005-01-20 as stored), then it needs to look in the "availability" table and display any names where the column "date" = the date on the schedule.

Normally this would be a simple WHERE statement (SELECT f_name, l_name FROM $table_name WHERE date = 'date'), but I don't know how to link these since the results are from 2 different tables!

Please help!

Code follows for the related section of my code:

// get referee select
function getRefereeSelect($referees,$selname,$selected,$id,$name) {
  $retVal = "<select name=\"$selname\" onChange=\"checksel($id,'$name')\">\n";
  $selected = (is_numeric($selected) && isset($referees[$selected])) ? $selected : 0;
  foreach ($referees as $key => $referee) {
    $sel = ($key == $selected) ? " selected" : "";
    $retVal .= "<option value=\"$key\"$sel>$referee</option>\n";
  }
  $retVal .= "</select>\n";
  return $retVal;
}

// get referee array
function getReferees($conn) {
  $retVal[0] = "";

// [B]THIS IS THE SECTION WHERE I NEED THE HELP, it needs to draw the 'date' from the schedule data in the code below it [/B]

 $sql = "SELECT user_id,f_name,l_name FROM availability WHERE date = 'date' ORDER BY l_name";
  $res = @mysql_query($sql,$conn) or die("SQL error (referees): ".mysql_error());
  while ($row = mysql_fetch_assoc($res)) {
    $retVal[$row['user_id']] = $row['f_name']." ".$row['l_name'];    

  }

  return $retVal;
}

// get schedule data
function getSchedule($conn,$scheduledate) {
  $matches = array();
  $retVal = array();
  if (preg_match("§(\d{4})-(\d{2})§",$scheduledate,$matches)) {
    $sql =<<<EOSQL
  SELECT id, `date`, `time`, trim(concat(`date`, ' ' ,`time`)) as datetime, 
         location, home, visitor, age, referee, ar1, ar2 
    FROM schedule 
   WHERE `gamecode`='$scheduledate'
ORDER BY datetime
EOSQL;

$res = @mysql_query($sql,$conn) or die("SQL error (schedule): ".mysql_error());
while ($row = mysql_fetch_assoc($res)) {
  $retVal[$row['id']] = $row;
}
  }

  return $retVal;
}

[mtmosier]

What you need to do is to familiarize yourself with the mysql join syntax.

[peanutgallery83]

okay, i looked that over, but seeing as how I'm new to this I have no idea what most of it meant! 🙂

the two tables i'm referencing are "availability" and "schedule"

From that read, it looks like I have to create an alias for those tables (t1 and t2 or something)?

Does that go at the start of the code?

Confused.

My full code is here:

<?PHP
// global variables
$db_name = "refphoto_testdb";
$db_user = "refphoto";
$db_pass = "08291983";
$table_name = "schedule";

//functions

// get referee select
function getRefereeSelect($referees,$selname,$selected,$id,$name) {
  $retVal = "<select name=\"$selname\" onChange=\"checksel($id,'$name')\">\n";
  $selected = (is_numeric($selected) && isset($referees[$selected])) ? $selected : 0;
  foreach ($referees as $key => $referee) {
    $sel = ($key == $selected) ? " selected" : "";
    $retVal .= "<option value=\"$key\"$sel>$referee</option>\n";
  }
  $retVal .= "</select>\n";
  return $retVal;
}

// get referee array
function getReferees($conn) {
  $retVal[0] = "";

  $sql = "SELECT id,f_name,l_name FROM reflist ORDER BY l_name";
  $res = @mysql_query($sql,$conn) or die("SQL error (referees): ".mysql_error());
  while ($row = mysql_fetch_assoc($res)) {
    $retVal[$row['id']] = $row['l_name'].", ".$row['f_name'];    

  }

  return $retVal;
}

// get schedule data
function getSchedule($conn,$scheduledate) {
  $matches = array();
  $retVal = array();
  if (preg_match("§(\d{4})-(\d{2})§",$scheduledate,$matches)) {
    $sql =<<<EOSQL
  SELECT id, `date`, `time`, trim(concat(`date`, ' ' ,`time`)) as datetime, 
         location, home, visitor, age, referee, ar1, ar2 
    FROM schedule 
   WHERE `gamecode`='$scheduledate'
ORDER BY datetime
EOSQL;

$res = @mysql_query($sql,$conn) or die("SQL error (schedule): ".mysql_error());
while ($row = mysql_fetch_assoc($res)) {
  $retVal[$row['id']] = $row;
}
  }

  return $retVal;
}

//connect to database and select table
$connection = @mysql_connect("localhost","refphoto","08291983") or die(mysql_error());
$db = @mysql_select_db($db_name,$connection) or die(mysql_error());

$thedate = isset($_POST['thedate']) ? $_POST['thedate'] : "";
$arrSchedule = array();
$display_block = "";
$arrReferees = getReferees($connection);
$confirm = "";

if (isset($_POST['seldate']) || isset($_POST['save'])) {
  // change date
  $arrSchedule = getSchedule($connection,$_POST['thedate']);
} 

if (isset($_POST['save'])) {
  // save data
  // form validation still missing
  $arrSched = $_POST['rowdata'];
  foreach($arrSched as $id => $data) {
    $arrSchedule[$id]['referee'] = $data['referee'];
    $arrSchedule[$id]['ar1'] = $data['ar1'];
    $arrSchedule[$id]['ar2'] = $data['ar2'];
    $sql = "UPDATE schedule SET referee='{$data['referee']}',ar1='{$data['ar1']}',ar2='{$data['ar2']}' WHERE id='$id'";
    @mysql_query($sql,$connection) or die("SQL error: ".mysql_error());
  }
}

if (!empty($arrSchedule)) {
  $display_block =<<<EOHTML
<table border=1 cellpadding=1 cellspacing=0 bordercolor=000000 bgcolor="#CC0000">
  <tr>
    <td valign=top width=85> <font color="#FFFFFF" size="2" face="Verdana, Arial, Helvetica, sans-serif"><strong>Date:</strong>
      </font></td>
    <td valign=top width=75> <font color="#FFFFFF" size="2" face="Verdana, Arial, Helvetica, sans-serif"><strong>Time:
      </strong> </font></td>
    <td valign=top width=150> <font color="#FFFFFF" size="2" face="Verdana, Arial, Helvetica, sans-serif"><strong>Location:</strong>
      </font></td>
    <td valign=top width=150> <font color="#FFFFFF" size="2" face="Verdana, Arial, Helvetica, sans-serif"><strong>Home:</strong>
      </font></td>
    <td valign=top width=150> <font color="#FFFFFF" size="2" face="Verdana, Arial, Helvetica, sans-serif"><strong>Visitor:</strong></font></td>
    <td valign=top width=150> <font color="#FFFFFF" size="2" face="Verdana, Arial, Helvetica, sans-serif"><strong>Age:</strong>
      </font></td>
    <td valign=top width=150> <font color="#FFFFFF" size="2" face="Verdana, Arial, Helvetica, sans-serif"><strong>Referee:</strong></font></td>
    <td valign=top width=150> <font color="#FFFFFF" size="2" face="Verdana, Arial, Helvetica, sans-serif"><strong>AR1:</strong>
      </font></td>
    <td valign=top width=150> <font color="#FFFFFF" size="2" face="Verdana, Arial, Helvetica, sans-serif"><strong>AR2:</strong>
      </font></td>
  </tr>
EOHTML;

  foreach($arrSchedule as $id => $data) {
    $selReferee = isset($arrSchedule[$id]['referee']) ? $arrSchedule[$id]['referee'] : 0;
    $selAr1     = isset($arrSchedule[$id]['ar1']) ? $arrSchedule[$id]['ar1'] : 0;
    $selAr2     = isset($arrSchedule[$id]['ar2']) ? $arrSchedule[$id]['ar2'] : 0;;
    $refSelect = getRefereeSelect($arrReferees,"rowdata[$id][referee]",$selReferee,$id,"referee");
    $ar1Select = getRefereeSelect($arrReferees,"rowdata[$id][ar1]",$selAr1,$id,"ar1");
    $ar2Select = getRefereeSelect($arrReferees,"rowdata[$id][ar2]",$selAr2,$id,"ar2");

$display_block .=<<<EOHTML
  <tr>
    <td valign=top width=85 bgcolor="#ffffff">
      {$data['date']}
    </td>
    <td valign=top width=75 bgcolor="#ffffff">
      {$data['time']}
    </td>
    <td valign=top width=150 bgcolor="#ffffff">
      {$data['location']}
    </td>
    <td valign=top width=150 bgcolor="#ffffff">
      {$data['home']}
    </td>
    <td valign=top width=150 bgcolor="#ffffff">
      {$data['visitor']}
    </td>
    <td valign=top width=150 bgcolor="#ffffff">
      {$data['age']}
    </td>
    <td valign=top width=150 bgcolor="#ffffff">
      {$refSelect}
    </td>
    <td valign=top width=150 bgcolor="#ffffff">
      {$ar1Select}
    </td>
    <td valign=top width=150 bgcolor="#ffffff">
      {$ar2Select}
    </td>
  </tr>
EOHTML;
  }
  $display_block .=<<<EOHTML
</table>
<br>
<input type="submit" name="save" value="Save">
EOHTML;

$confirm = " onclick=\"if (!confirm('do you really want to do that ?')) return false;\""; 
}

?>


<HTML>
<HEAD>
<TITLE>ECISOA Game Assignments</TITLE>
<script language="JavaScript">
<!--
  function checksel(id,name) {
    var cnt = 0;
    var fields = new Array('referee','ar1','ar2');

var val = document.forms.theform.elements["rowdata["+id+"]["+name+"]"].value

for (i=0;i<fields.length;i++) {
  var fval = document.forms.theform.elements["rowdata["+id+"]["+fields[i]+"]"].value
  if (fields[i] != name && fval == val && val!=0)
    cnt++;
}

if (cnt>0) {
  alert("Mo, You've already assigned this referee to another game at this time!!");
  document.forms.theform.elements["rowdata["+id+"]["+name+"]"].selectedIndex=0;
}
  }
//-->
</script>
</HEAD>
<BODY>
<H1><font color="#CC0000" face="Verdana, Arial, Helvetica, sans-serif">ECISOA 
  Game Assignments</font></H1>
<table border="1" cellpadding="1" cellspacing="0" bordercolor="#000000">
  <tr bgcolor="#CCCCCC"> 
    <td colspan="4"> <div align="center"><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><strong>Use 
        the Following Season Codes:</strong></font></div></td>
  </tr>
  <tr> 
    <td width="185"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Winter 
      Season - YYYY-01</font></td>
    <td width="185"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Spring 
      Season - YYYY-02</font></td>
    <td width="185"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Summer 
      Season - YYYY-03</font></td>
    <td width="185"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Fall 
      Season - YYYY-04</font></td>
  </tr>
</table>
<form name="theform" action="<?PHP echo $_SERVER['PHP_SELF']; ?>" method="post">
  Season Code: 
  <input name="thedate" value="<?PHP echo $thedate; ?>" method="post">&nbsp;&nbsp;
  <input type="submit" name="seldate" value="Change Season"<?PHP echo $confirm; ?>>
  <br>
<? echo "$display_block"; ?>
</form>
<P><a href="/assignor/assignor_login2_2.php"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Return to Menu</font></a></P>
</BODY>
</HTML>

 

[mtmosier]

No, there's no need to use aliases. Its just a shorthand to make long queries more readable. I never use them myself. You'll see at the bottom of the mysql page several examples which don't use them.

So I'm going to make a few assumptions here. I assume that the data we're searching on is "$scheduleDate", which is checked against the "gamecode" column of the schedule table. I'm also assuming that the link you want between these two tables is the column called "date" in both tables. And that what you want to return is the date, user_id, f_name, and l_name from the availability table.

$sql  = "SELECT DISTINCT availability.user_id, availability.f_name, availability.l_name, availability.date ";
$sql .= "FROM availability INNER JOIN schedule USING(date) ";
$sql .= "WHERE schedule.gamecode = '".mysql_real_escape_string($scheduleDate)."' ";
$sql .= "ORDER BY availability.l_name";

Obviously this is just a small part of the changes you'll have to make to your script.

[Roger_Ramjet]

Nope, t1 and t2 are aliases for the real table names. There are ocasions where aliases are essential, but this is not one of them. All they would do here is save you key strokes. Forget them for now, they are only going to confuse you.

simple case.
A join is used to match data in one table to data in another.

For a join to work efficiently, you need to index the column in each table that will be used for the match. In this case that would be the date column in each table. Easiest thing is also to have them named the same, but do not use the word 'date' for a column name, date is a reserved word. Call them g_date or something.

I can see you know about the need for the backtick to overcome this reserved word problem. Forget it, you are only setting up a bug for the future. One day you will forget the backticks or someone will missread your code. DATETIME is a column type so don't use it as an alias either

You would then write the query thus, and I'm not going to type out your field list

SELECT schedule.*, reflist.* FROM schedule INNER JOIN reflist USING (g_date)

This will return a resultset where each row has the game data and a refs data. The game data will therefore be repeated for every ref who is available for each game in the schedule.

[Roger_Ramjet]

Hey peanut, could you not cross-post here please

Your post in Coding

http://www.phpbuilder.com/board/showthread.php?s=&postid=10586839#post10586839

[peanutgallery83]

Okay, so now my question is, where the heck do I put this in the code? It seems like what you both are saying is that I need to modify my query....but I don't know which query needs to be modified (the part where I query my schedule, or the part where I query my reflist?) Or is this supposed to combine it all into one step?

Sorry if this is a stupid question....this is well above my head....I'm trying my best to learn but it's very confusing. Unfortunately I have to make this work before I can complete the last parts of the project which are all considerably easier.

Thank you for your time and help, I appreciate it very much.

Landis