displaying date using WHERE x=y

[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;
}

[eoghain]

You need to do a Join query, something like this should work:

SELECT a.user_id, a.f_name, a.l_name
  FROM availability a
 INNER JOIN games g ON (g.date = a.date)
 WHERE g.date = 'date'
 ORDER BY a.l_name

You'll still have to put in the 'date' that you want to run this query for, but it should give you a list of users in the availability table that have a date that matches the date that you are searching for in your games table.

[peanutgallery83]

Okay, that's what i'm looking for.

The thing is, I don't enter a date to query, the code displays ALL of the games in rows of a table. (Like an excel sheet).

Perhaps it would be best if you see how it works:

www.refphotos.com/assignor/assignor_login.html

Username: landiswiley
Password: 08291983

Click on "Assign Referees" on the right. Enter 2005-02 in the box at the top and press the button.

All those drop down menus are what I'm trying to generate, but it needs to be so they only display the names of people who have indicated they are available on that date.

Thoughts?

Thanks!

[eoghain]

I'm thinking that you have a logic flaw in what you are trying to do, here is how I would do this to make everything work.

1. select all games for the given season
2. loop over each game
3. for each game select the list of referees available
4. display each game on my page

Now that logic works but it creates n+1 queries to be processed (n = number of games in the season, 1 = the original query to get the games). Running n+1 queries could get to be very large and time consuming so I'd change this to work using a single query that got me all of my games for a season with all of the referees that are available for those games, and I'd do the processing work in PHP.

SELECT g.date, g.time, g.location, g.home, g.visitor, g.age, a.name
  FROM games g
 INNER JOIN availability a ON (g.date = a.date)
 WHERE g.season = "2005-02"
 ORDER BY g.date

Now that query will get me all of my game information plus each referee that is available for that date. But it will get me all of my game information for each referee.

date, time, location, home, visitor, age, name
1/1, 10:00, field1, team1, team2, 18, John Doe
1/1, 10:00, field1, team1, team2, 18, Jane Doe
1/1, 10:00, field1, team1, team2, 18, Frank Smith
1/1, 10:00, field1, team1, team2, 18, Sammy Someone

As you can see there is alot of duplicate data that we will need to deal with, but the advantage is we only ran 1 query. So to process this data I'd do something like this:

$currDate = null;  // Placeholder
$gameArray = null; // Another Placeholder
$games = array();

while ($row = mysql_fetch_assoc($res)) 
{
   if ($currDate != $row["date"])
   {
      $currDate = $row["date"];
      if ($gameArray != null)
      {
         $games[] = $gameArray;
      }
      $gameArray["date"] = $row["date"];
      $gameArray["time"] = $row["time"];
      $gameArray["location"] = $row["location"];
      $gameArray["home"] = $row["home"];
      $gameArray["visitor"] = $row["visitor"];
      $gameArray["age"] = $row["age"];
      $gameArray["refs"] = array();
   }

   $gameArray["refs"][] = $row["name"];
}

// Process $games array here to build table/form

Now I've run a single query to get all of my data and I've walked through that data to put it into a form that I can easily use to build my table and form for display to my user.

[Roger_Ramjet]

Hey peanut, could you not cross-post here please.

Your post in Newbies

http://www.phpbuilder.com/board/showthread.php?s=&threadid=10292685