[RESOLVED] Code to display table entries in a drop down menu

[NiallThistle]

Hi all,

I hope somebody can help me. An internet search has me more confused than I probably should be.

I am working with code I use for a set of fixtures and results for a football league.

I have successfully been able so far to display all fixtures from the season on one page, ordered by date.

http://www.tainthistle.co.uk/testfolder/display.php

However, what I want to be able to do is filter the results by each team.

I know roughly what I need to do but am stumped on the code. I want to basically request that if OpponentName entry 1 is selected from the drop down, then any fixtures featuring the hometeam or awayteam of OpponentName entry 1 would be displayed on screen.

Here is my current code for the link above:

<?php

include('user.php');
$connection = mysql_connect("$host","$user","$password")
or die(mysql_error());
mysql_select_db("$txt_db_name",$connection)
or die(mysql_error());

{
$print_date = '%d.%m.%Y';
}

{
$get_matches = mysql_query("
SELECT O.OpponentName AS hometeam,
OP.OpponentName AS awayteam,
LM.LeagueMatchHomeGoals AS goals_home,
LM.LeagueMatchAwayGoals AS goals_away,
LM.LeagueMatchID AS id,
DATE_FORMAT(LM.LeagueMatchDate, '$print_date') AS date
FROM tplls_leaguematches LM, tplls_opponents O, tplls_opponents OP
WHERE O.OpponentID = LM.LeagueMatchHomeID AND
OP.OpponentID = LM.LeagueMatchAwayID AND
LeagueMatchSeasonID = '4'
ORDER BY LM.LeagueMatchDate",$connection)
or die(mysql_error());
}

while($data = mysql_fetch_array($get_matches)){

echo "

<table>
<tr>
<td align=\"left\" colspan=\"2\">
<b>$data[date]</b>
</td>
</tr>
</table>

<table>
<tr>
<td align=\"left\" valign=\"top\" width=\"90%\">$data[hometeam] $data[goals_home]-$data[goals_away] $data[awayteam]</td>
<td align=\"left\" valign=\"top\" width=\"10%\"></td>
</tr>
</table><br>";

}

?>

[ixalmida]

You have two choices based on whether you care about server post-back or not:

1) Force a form post using the onChange event of the drop-down box, then query the database using the posted data (I'm assuming a lot about how your database is set up, but it's only an example):

if(isset($_POST['team1'])
  {
  $team_id = $_POST['team1'];
  // (maybe validate the team id here)
  // Get opponents...
  $query = "SELECT opponent_name, etc FROM Games WHERE team_id = '$team_id'";
  $result = mysql_query($query, $link);
  while($row = mysql_fetch_array($result))
    {
    extract($row);
    $opponents[$opponent_id] = $opponent_name;
    }
  }

<!-- Replace "formName" below with the name of the form, of course... -->
<select name="team1" onChange="javascript:document.formName.submit();">
  <!-- If you are using PHP to get the team names, you would add code here
    to determine which option is currently selected.  -->
  <option value="1">Red Team</option>
  <option value="2">Blue Team</option>
  <!-- etc... -->
</select>

// Populate opponent if a team was selected...
if(isset($opponents))
  {
  echo "<table>\n";
  foreach($opponents as $team_id => $team_name)
    {
    echo "<tr><td>$team_name</td><td>$etc</td></tr>\n";
    }
  echo "</table>";
  }

2) The second option is avoiding a post-back by using Javascript arrays to hold all the possible combinations. You can then use a Javascript function to show/hide table values using the selection from the team1 drop-down box. I won't go into the Javascript right now, but I can post something later if you need it.

[NiallThistle]

Excuse my ignorance, but could you please explain what post-back is, and why I may want to avoid it?

[ixalmida]

Post-back means posting data TO the server and re-rendering the entire current page FROM the server, as opposed to updating the information displayed on the page using a client technology such as Javascript.

The reason you might want to avoid post-back is that it may increase bandwidth and processing on the server side, depending on what information you are posting and displaying. Or it may not be a big deal, depending on site traffic.

There may also be reasons for preferring a post-back. For example, for security reasons you may not want the client to be able to see ALL of the data by examining the HTML code. Or there may be a HUGE result set, and you don't want a HUGE web page to go with it, so you'd only display partial results in each page (like a Google search).

[NiallThistle]

ixalmida;10910395 wrote:

Post-back means posting data TO the server and re-rendering the entire current page FROM the server, as opposed to updating the information displayed on the page using a client technology such as Javascript.

The reason you might want to avoid post-back is that it may increase bandwidth and processing on the server side, depending on what information you are posting and displaying. Or it may not be a big deal, depending on site traffic.

There may also be reasons for preferring a post-back. For example, for security reasons you may not want the client to be able to see ALL of the data by examining the HTML code. Or there may be a HUGE result set, and you don't want a HUGE web page to go with it, so you'd only display partial results in each page (like a Google search).

Ah I see.

In the event that other pages are using the same SQL database, should I be avoiding postback? The same fixture and result data is also used to put together a league table which is an include on the main site.

[NiallThistle]

I think I may take a different approach to the database idea.

I think I would rather have each team, using it's own unique ID to load the page - would probably be easier in the long run...

Getting rid of the drop down, and instead have separate links for each team. There are only ten teams after all.

i.e. ../display.php?id=10 for team number 10.

Again, I'm not really sure how I should be defining page id's in this way however. Any help with basic id handling would be great.

[NiallThistle]

Here is a better explanation of what I want to do.

I will have a list of teams on a main page, and when a user clicks on a team to view their page, it will redirect them to display.php.

I will have each link differentiated by a teamid, i.e. Balintore FC are 1, and Tain FC are 7.

So, by visiting display.php?teamid=7, I would expect the fixtures and results for Tain to appear.

At the moment, display.php is configured to show all fixtures/results.

A problem I am coming across is that there are TWO different team variables for each team, as sometimes they are a home team and sometimes they are an away team, so there needs to be some kind of OR condition, stating that either hometeam or awayteam being equal to teamid is criteria enough for the match to be included.

What I need advice on:

How do I get display.php to read the teamid from the URL when it loads and then therefore compare that teamid to the field within my table of opponents and therefore only show their matches, in a way that is in keeping with the code I am already using. (Above)

[ixalmida]

How do I get display.php to read the teamid from the URL

$team2search = $_GET['teamid'];

therefore compare that teamid to the field within my table of opponents

$query = "SELECT field1, field2, etc FROM Games WHERE team1_id = $team2search OR team2_id = $team2search";

Since you are using part of the URL in your query, you will also want to validate it to avoid SQL injection:

$team2check = (int) $team2search;
if($team2check < 1 || $team2check > 10)
  {
  // Show error, and maybe redirect the user...
  echo "Invalid team id! <a href='error_page.php'>Click to continue...</a>";
  }

*** SQL injection is a way for a hacker to inject a sub-query into your query and do bad things in your database. In this case, validation ensures the URL only contains a number between 1 and 10, and not a sub-query.

[NiallThistle]

That worked fantastically, thank you so much.

My next question is:

How should I be writing <a> tags to force a link to keep the teamid...i.e. if a link to displaycup.php is clicked, how can I configure display.php to add ?teamid=whatever the current id is?

[NiallThistle]

I have worked it out! Thanks to the wonder that is Google.

<a href='display.php?teamid=".$team2search."'>League</a>

[ixalmida]

You probably already know you can set a form's method to "get" instead of "post", but that usually doesn't make sense. So the only time you'd really be using a GET variable is when you're creating a hyperlink.

The HTML version is:

<a href="displaycup.php?teamid=<?php echo $team_id; ?>">Team #1</a>

Or...

echo "<a href='displaycup.php?teamid=$team_id'>Team #1</a>";

Notice how the full PHP line is actually shorter and easier to read. The drawback is that the colorful code hints go away.