Problem with JOIN function

[peanutgallery83]

Okay, the purpose of this code is to link the "date" columns in the table 'availability' to the same column in the table 'schedule'.

What's happening is that users are logging in and selecting dates that they are available. These dates are being stored in the 'availability' table which has fields avail_id | user_id | f_name | l_name | date

In a separate process, an administrator logs in and on a schedule that lists out games, selects 3 people to work each game through the use of drop down menus. I'm trying to make it so that only users who said they were available on a date will be on the drop down.

Based on responses to a question I asked earlier, I was told that I would need to use the JOIN function to link the two tables, so I tried to do it, the code is below. However, whats happening is that the drop downs are displaying EVERY user who has indicated an availability. In other words, it is displaying every name that appears in the 'availability' table, regardless of a matching date or not.

Please help! What am I missing?? I'm new to PHP and trying my best, and this is very new....so please "dumbify" your response as much as possible....thank you VERY much in advance!

 
// 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;
}
//  [COLOR=deeppink]  [B]THIS IS THE QUERY AREA THAT I AM WORKING ON AND NEED THE HELP[/B] [/COLOR]
// get referee array
function getReferees($conn) {
  $retVal[0] = "";

  $sql = "SELECT schedule.id, schedule.g_date, availability.user_id, availability.f_name, availability.l_name, availability.g_date
   FROM availability INNER JOIN schedule USING (g_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['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, `g_date`, `time`, trim(concat(`g_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;
}

Thanks again for any help.

Landis

[Roger_Ramjet]

As I pointed out in your other thread:

DATE IS A RESERVED WORD - READ IT

I told you that you would have problems with using Date as a column name, NOW I'M YELLING AT YOU "CHANGE THE NAME"

There is NO reason to ever use a reserved word for a table or column name. If you want to return a column with a resreved word as the name for display purposes, you could do it with an alias, but that will still require backticks so MySQL does not get confused. Best to substitute the reserved word in your code after you get the query results back.

[peanutgallery83]

Okay, I changed the column names and the corresponding code to g_date. Unfortunately it did not fix my problem and it's still displaying every name in the availability table regardless of matching date or not.

The code in my 1st post has been modified with the changed date code.

Please help.

[Roger_Ramjet]

Good.

Now, you want to list all games in the schedule and match them to refs who are available on the game date for each game in the schedule

The following query will do that. I am using the select all fields syntax as I'm not going to type a list of field names, you can do that if you want.

I have also changed it so that it uses a LEFT JOIN. This will still return a result if no refs are available for a game date. The ref fields will be set to NULL when that happens: and you need to know that.

$sql = "SELECT * FROM schedule AS s 
        LEFT JOIN availability AS a USING (g_date) 
        ORDER BY a.l_name";

You want to do this with a function that returns an array of refs for each game.

You therefore have to pass the game date or the game id to the function for it to limit the query. I chose the game date

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

  $sql = "SELECT * FROM schedule AS s 
        LEFT JOIN availability AS a USING (g_date) 
        ORDER BY a.l_name
        WHERE s.g_date=$g_date";

  $res = @mysql_query($sql,$conn) or die("SQL error (referees): ".mysql_error());
  while ($row = mysql_fetch_assoc($res)) {
    $retVal[$row['a.user_id']] = $row['a.l_name'].", ".$row['a.f_name'];    

  }

  return $retVal;
} 

[Roger_Ramjet]

Looking back at my post I realise that the function query can be much simplified. I was concentrating on the missing parameter for the function.

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

  $sql = "SELECT * FROM availability
        ORDER BY l_name
        WHERE g_date=$g_date";

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

  }

  return $retVal;
}

Personally, I would not do it this way. If you had a lot in the schedule then this use of a fucntion to query the db for each game would be very slow.

I would use my very first query once to select all tha data for all the games with all the available refs and then use code to order and output the list og games with a select list of refs for each.

[peanutgallery83]

Okay, I tried this out (I used your first code because the 2nd one didn't work at all, and I could understand what was going on in the 1st more easily), had to modify a little bit because I was getting some errors....almost made it but not quite...now I've got an error and a problem:

The error being returned it:
Warning: Missing argument 2 for getreferees() in /home/refphoto/public_html/assignor/show_assigngames2.php on line 23

The problem is that all the drop down menus are now empty. The schedule shows just fine, but the drop down menus have no name in them (and yes, there are matching dates in the tables...I double checked 😉 )

I was able to get rid of the error by removing $g_date from the function, and from the WHERE code, but still the drop downs were empty.

Am I missing something? I feel quite stupid continually asking for help but I'll be darned if I know what to do.

Thanks for your help.

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

// [b]THIS IS LINE 23[/b]
function getReferees($conn, $g_date) { 
  $retVal[0] = ""; 

  $sql = "SELECT * FROM schedule AS s  

        LEFT JOIN availability AS a USING (g_date) 
        WHERE s.g_date='$g_date'
        ORDER BY a.l_name"; 

  $res = @mysql_query($sql,$conn) or die("SQL error (referees): ".mysql_error()); 
  while ($row = mysql_fetch_assoc($res)) { 
    $retVal[$row['a.user_id']] = $row['a.l_name'].", ".$row['a.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, `g_date`, `time`, trim(concat(`g_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;
}

[Roger_Ramjet]

You have to pass the game date to the refs function: that is what : Missing argument 2 for getreferees() is saying. The second parameter declared in the function is missing: ie function getReferees($conn, $g_date) see the $g_date??

Your original problem was that your refs function returned ALL refs regardless of their availability for an individual game. So I added the game date to the function and now, my version, will just get the refs for the particular game date you pass it.

So when you call the function include both the db link and the game date for the specific game.

[Roger_Ramjet]

Once you have got your head around it and it is returning the right refs list, replace the current query woth the one from my second post.

[peanutgallery83]

Well....good news is I got rid of the error. Bad news is, I'm still returning blank drop down menus.

I think I made the proper change to call the function, I don't know. This is certainly making me feel rather stupid, and I'm sure I'm missing something very small.

Below is the full code for the page. Can you see where I'm missing something?? Or perhaps I'm just creating a conflict somewhere.

This link will show a picture of the page and how it looks, perhaps it will help. http://refphotos.com/assignmentpagepic.jpg

Again, I really appreciate the help.

Thanks.

Landis

<?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,$g_date) { 
  $retVal[0] = ""; 

  $sql = "SELECT * FROM schedule AS s  

        LEFT JOIN availability AS a USING (g_date) 
        WHERE s.g_date='$g_date'
        ORDER BY a.l_name"; 

  $res = @mysql_query($sql,$conn) or die("SQL error (referees): ".mysql_error()); 
  while ($row = mysql_fetch_assoc($res)) { 
    $retVal[$row['a.user_id']] = $row['a.l_name'].", ".$row['a.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, `g_date`, `time`, trim(concat(`g_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,$g_date);
$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['g_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>