Jump to content

Generating URL for each table ID

edm73

By edm73
in PHP Coding Help

 Share

Recommended Posts

edm73 Newbie

edm73

Posted
Posted

As a complete newbie, I'm not sure Ive got the terminology right in my subject header. But this is what I am trying to do.

 

I have a soccer stats site with data entered into each of numerous 'seasons', accessible via a drop-down menu, as here:

http://www.fleethistory.co.uk/stats/matches.php

 

Now regardless of what season I select, the URL will stay the same as above. What I want to do is have users go direct to a particular season from a link without having to use the drop-down. How would I generate a unique URL for each of the seasons listed in the drop-down?

 

Don't know if you need the PHP code, but here it is just in case:

 

 

 

<?php
/*
***************************************************************************
*   tplSoccerStats
*   -----------------------------------------------------------------------
*   Copyright: TPL Design
*   email:     info@tpl-design.com
*   www:       www.tpl-design.com/tplsoccerstats
***************************************************************************
*/

// Starts a new session or resumes the current one.
session_start();

// Get db host, db username, db password, and db name from admin/user.php
// (each team has its own db).
include('admin/user.php');

// Establish db connection. Select appropriate team db.
$connection = mysql_connect("$host", "$user", "$password") or die(mysql_error());
mysql_select_db("$txt_db_name", $connection) or die(mysql_error());

// Get preferences stored in db.
$sql = "SELECT * FROM tplss_preferences WHERE ID = '0'";
$pref = mysql_query($sql, $connection) or die(mysql_error());
$pdata = mysql_fetch_array($pref);
mysql_free_result($pref);

// Include php preferences.
include('preferences.inc');

// Use session defaults if available, otherwise set them from database values.
if ((!isSet($_SESSION['defaultseasonid_tplss']))    ||
    (!isSet($_SESSION['defaultmatchtypeid_tplss'])) ||
    (!isSet($_SESSION['defaultlanguage_tplss'])))    {

    $_SESSION['defaultseasonid_tplss'] = $pdata['DefaultSeasonID'];
    $_SESSION['defaultmatchtypeid_tplss'] = $pdata['DefaultMatchTypeID'];
    $_SESSION['defaultlanguage_tplss'] = $pdata['DefaultLanguage'];
}
$defaultseasonid = $_SESSION['defaultseasonid_tplss'];
$defaultmatchtypeid = $_SESSION['defaultmatchtypeid_tplss'];
$defaultlanguage = $_SESSION['defaultlanguage_tplss'];

// Include language based global vars, in our case english, e.g. $txt_preview = 'Preview'
include('language.inc');

// Include pafc banner and menu bar.
include('header.php');

// Start an html form.
echo '<form method="post" action="change.php">' . "\n";

// Print images at top of form if we have them (we don't).
$image_url = "images/header.jpg";
$image_url2 = "images/header.gif";
if(file_exists($image_url) || file_exists($image_url2))
{
  echo '<table align="center" width="' . $tb_width . '" cellspacing="0" cellpadding="0" border="0">' . "\n";
  echo "<tr>\n";
  echo '<td align="center">' . "\n";

  if (file_exists($image_url)) {
    echo '<img src="' . $image_url . '" ALT=""><br>' . "\n";
  }
  elseif (file_exists($image_url2)) {
    echo '<img src="' . $image_url2 . '" ALT="">' . "\n";
  }
  echo "</td></tr></table>\n\n";
}

?>

<!-- Print change bar table -->
<table align="center" width="<?php echo $tb_width ?>" cellspacing="0" cellpadding="0" border="0" bgcolor="<?php echo $border_c ?>">
<tr>
<td>
<table width="100%" cellspacing="1" cellpadding="5" border="0">
<tr>
<td bgcolor="<?php echo $inside_c ?>" align="center">
<?= $txt_change ?>:
<select name="season">
<option value="0"><?= $txt_all ?></option>
<?php
$sql = "SELECT * FROM tplss_seasonnames WHERE SeasonPublish = '1' ORDER BY SeasonName DESC";
$get_seasons = mysql_query($sql, $connection) or die(mysql_error());
while($data = mysql_fetch_array($get_seasons))
{
  if($data['SeasonID'] == $defaultseasonid)
    echo '<option value="' . $data['SeasonID'] . '" SELECTED>' . $data['SeasonName'] . "</option>\n";
  else
    echo '<option value="' . $data['SeasonID'] . '">' . $data['SeasonName'] . "</option>\n";
}
mysql_free_result($get_seasons);
?>
</select> or Competition: 
<select name="matchtype">
<option value="0"><?= $txt_all ?></option>
<?php
$sql = "SELECT * FROM tplss_matchtypes ORDER BY MatchTypeName";
$get_types = mysql_query($sql, $connection) or die(mysql_error());
while($data = mysql_fetch_array($get_types))
{
  if($data['MatchTypeID'] == $defaultmatchtypeid)
    echo '<option value="' . $data['MatchTypeID'] . '" SELECTED>' . $data['MatchTypeName'] . "</option>\n";
  else
    echo '<option value="' . $data['MatchTypeID'] . '">' . $data['MatchTypeName'] . "</option>\n";
}
mysql_free_result($get_types);
?>
</select> <input type="submit" name="submit" value="Select">

</td>
</tr>
</table>
</td>
</tr>
</table>

<!-- Print outer fixture tables -->
<table align="center" width="<?php echo $tb_width ?>" cellspacing="0" cellpadding="0" border="0" bgcolor="<?php echo $border_c ?>">
<tr>
<td>
<table width="100%" cellspacing="1" cellpadding="5" border="0">
<tr>
<td bgcolor="<?php echo $inside_c ?>" align="center">

<!-- Print inner fixture table -->
<table width="<?= $tb2_width ?>%" cellspacing="0" cellpadding="4" border="0">

<tr>
<td align="left" valign="middle" bgcolor="#D01818">
<font color="#FFffff"><b>Date</b></font>
</td>

<td align="center" valign="middle" bgcolor="#D01818">
<font color="#FFffff"><b>H/A</b></font>
</td>

<td align="left" valign="middle" bgcolor="#D01818">
<font color="#FFffff"><b>Opponent</b></font>
</td>

<td align="left" valign="middle" bgcolor="#D01818">
<font color="#FFffff"><b>Competition</b></font>
</td>

<td align="center" valign="middle" bgcolor="#D01818" colspan="2">
<font color="#FFffff"><b>Result</b></font>
</td>

<td align="center" valign="middle" bgcolor="#D01818">
<font color="#FFffff"><b>Att</b></font>
</td>

<td align="center" valign="middle" bgcolor="#D01818">
<font color="#FFffff"><b>Details</b></font>
</td>
</tr>

<?php
// Construct db query to get fixtures from database.
$selectClause = "SELECT
                 M.MatchID AS id,
         M.MatchAdditionalType AS additype,
         O.OpponentName AS opponent,
         O.OpponentID AS oppid,
         M.MatchGoals AS goals,
         M.MatchGoalsOpponent AS goals_opponent,
         M.MatchPenaltyGoals AS penalty_goals,
         M.MatchPenaltyGoalsOpponent AS penalty_goals_opponent,
         M.MatchOvertime AS overtime,
         M.MatchPenaltyShootout AS penalty_shootout,
         DATE_FORMAT(M.MatchDateTime, '%M %Y') AS month,
         DATE_FORMAT(M.MatchDateTime, '%a %e') AS date,
         DATE_FORMAT(M.MatchDateTime, '%H:%i') AS time,
         M.MatchPlaceID AS place,
                 M.MatchAttendance AS att,
         M.MatchPublish AS publish,
         MT.MatchTypeName AS typename,
         P.PreviewText AS prewtext
         FROM ( tplss_matches M, tplss_matchtypes MT, tplss_opponents O )
         LEFT OUTER JOIN tplss_previews P ON M.MatchID = P.PreviewMatchID ";

if (($defaultseasonid != 00) && ($defaultmatchtypeid != 0)) {
  $whereClause = "WHERE M.MatchTypeID = '$defaultmatchtypeid'
                  AND M.MatchSeasonID = '$defaultseasonid'
                  AND M.MatchTypeID = MT.MatchTypeID
          AND M.MatchOpponent = O.OpponentID ";
} elseif (($defaultseasonid == 0) && ($defaultmatchtypeid != 0)) {
  $whereClause = "WHERE M.MatchTypeID = '$defaultmatchtypeid'
          AND M.MatchTypeID = MT.MatchTypeID
          AND M.MatchOpponent = O.OpponentID ";
} elseif (($defaultseasonid != 0) && ($defaultmatchtypeid == 0)) {
  $whereClause = "WHERE M.MatchSeasonID = '$defaultseasonid'
          AND M.MatchTypeID = MT.MatchTypeID
          AND M.MatchOpponent = O.OpponentID ";
} elseif (($defaultseasonid == 0) && ($defaultmatchtypeid == 0)) {
  $whereClause = "WHERE M.MatchTypeID = MT.MatchTypeID
          AND M.MatchOpponent = O.OpponentID ";
}

$orderByClause = "ORDER BY M.MatchDateTime";

// Execute query.
$sql = $selectClause . $whereClause . $orderByClause;
$get_matches = mysql_query($sql, $connection) or die(mysql_error());

// Loop round fixtures (which come back from database in date order).
while($data = mysql_fetch_array($get_matches))
{
  // Print a month header row each time we hit a new month.
  if ($data['month'] <> $lastMonth) {
    echo "<tr>\n";
    echo '<td align="left" valign="middle" bgcolor="#7c0606" colspan="9">';
    echo '<font color="#FFffff"><b>' . $data['month'] . "</b></font>";
    echo "</td>\n";
    echo "</tr>\n\n";
    $lastMonth = $data['month'];
  }

  // Assign home/away based vars.
  if ($data['place'] == 1) {
    $placeBg = $bg4;
    $venue = "H";
  } else {
    $placeBg = $bg3;
    $venue = "A";
  }

  // Print date and venue.
  echo"<tr>\n";
  echo '<td align="left" valign="middle" bgcolor="' . $placeBg . '">';
  echo $data['date'];
  echo "</td>\n";

  echo '<td align="center" valign="middle" bgcolor="' . $placeBg . '">';
  echo $venue;
  echo "</td>\n";

  // Print opponent team name (as a link if possible).
  echo '<td align="left" valign="middle" bgcolor="' . $placeBg . '">';
  if ($data['oppid'] == 1) {
    echo '$data[opponent]';
  } else {
    echo '<a href="opponent.php?opp=' . $data['oppid'] . '">' . $data['opponent'] . "</a>";
  }
  echo "</td>\n";

  // Print competition type.
  echo '<td align="left" valign="middle" bgcolor="' . $placeBg . '">';
  echo $data['typename'];
  if ($data['additype'] != '') {
    echo " / " . $data['additype'];
  }
  echo "</td>\n";

  // Print result, attendance and match report.
  if ($data['goals'] == NULL || $data['goals_opponent'] == NULL) {
    // No goals recorded - match can't have been played yet - print empty fields.
    echo '<td bgcolor="' . $placeBg . '"> </td>' . "\n";
    echo '<td bgcolor="' . $placeBg . '"> </td>' . "\n";
    echo '<td bgcolor="' . $placeBg . '"> </td>' . "\n";
    echo '<td align="center" valign="middle" bgcolor="' . $placeBg . '">';
    if ($data['prewtext'] == '') {
      echo " ";
    } else {
      echo '<a href="preview.php?id=' . $data['id'] . '">' . $txt_preview . "</a>";
    }
    echo "</td>\n";
  } else {
    // Goals recorded - figure out result and score - print required fields.
    if ($data['penalty_goals'] == NULL || $data['penalty_goals_opponent'] == NULL) {
      if ($data['goals'] > $data['goals_opponent'])
$result = '<img src="images/win.jpg">';

      elseif ($data['goals'] < $data['goals_opponent'])
$result = '<img src="images/lose.jpg">';
      else
$result = '<img src="images/draw.jpg">';
      $score = $data['goals'] . " - " . $data['goals_opponent'];
    } else {
      if ($data['penalty_goals'] > $data['penalty_goals_opponent'])
$result = "<b>W</b>";
      else
$result = "L";
      $score = $data['goals'] . " - " . $data['goals_opponent'] . " (" . $data['penalty_goals'] . " - " . $data['penalty_goals_opponent'] . ")";
    }
    echo '<td align="center" valign="middle" bgcolor="' . $placeBg . '">' . $result . "</td>\n";
    echo '<td align="center" valign="middle" bgcolor="' . $placeBg . '">' . $score . "</td>\n";
    echo '<td align="center" valign="middle" bgcolor="' . $placeBg . '">' . $data['att'] . "</td>\n";
    if($data['publish'] == 1)
    {
    echo '<td align="center" valign="middle" bgcolor="' . $placeBg . '"><a href="matchdetails.php?id=' . $data['id'] . '">Yes</td></a></td>' . "\n";
    }
    else
    {
    echo '<td align="center" valign="middle" bgcolor="' . $placeBg . '"> </td></a></td>' . "\n";
    }
  }

  echo "</tr>\n\n";
}

// Free resultset.
mysql_free_result($get_matches);

?>
</table>

</td>
</tr>
</table>
</td>
</tr>
</table>
<?php
// Print tpl soccers stats message.
include('bottom.txt');
?>
</form>

<?php
// Finish off html.
include('footer.php');
?>

Link to comment
Share on other sites
requinix Prolific Member

requinix

Posted
Posted

Unfortunately for you, the form thing you have is complicated. The form goes to change.php which sets some session values, then redirects to matches.php which displays the table accordingly.

 

What does change.php look like? Hopefully it can be incorporated into matches.php instead of existing as a separate file.

Link to comment
Share on other sites
edm73 Newbie

edm73

Posted
  • Author
Posted

Thanks requinix... i think this might be equally complicated but this is it:

 

<?php

session_start();

$HTTP_REFERER = $_SERVER['HTTP_REFERER'];

$submit = $_POST['submit'];
$submit2 = $_POST['submit2'];
$changepage = $_POST['changepage'];
$change_opponent = $_POST['change_opponent'];
$change_playeropponent = $_POST['change_playeropponent'];
$change_player = $_POST['change_player'];

// try to login
if( $_POST['dologin'] )
{
    include_once( "./../forum/config.php" );
    include_once( "./../forum/db/mysql.php" );

    $db = new sql_db($dbhost, $dbuser, $dbpasswd, $dbname, false);
    if( $db->db_connect_id )
    {
        $auth = $_POST['auth'];

	$username = trim(htmlspecialchars( $auth['username'] )) ;
	$username = substr(str_replace("\\'", "'", $username), 0, 25);
	$username = str_replace("'", "\\'", $username);
	$password = $auth['password'];

	$sql = "SELECT user_id, username, user_password, user_active, user_level
		FROM phpbb_users
		WHERE username = '" . str_replace("\\'", "''", $username) . "'";
	if ( $result = $db->sql_query($sql) )
            if( $row = $db->sql_fetchrow($result) )
                if( md5($password) == $row['user_password'] && $row['user_active'] )
                {
                    $_SESSION['user_logged'] = 1;
                    $_SESSION['user_id'] = $row['user_id'];
                    $_SESSION['user_username'] = $row['username'];
                }
    }
}

if( $_POST['dologout'] )
    $_SESSION['user_logged'] = 0;


// update list of wanted matches
if( $_POST['dowanted'] && $_SESSION['user_logged'] == 1 && $_SESSION['user_id'] )
{
    include('admin/user.php');
    $connection = mysql_connect("$host","$user","$password")
    or die(mysql_error());
    mysql_select_db("$txt_db_name",$connection)
    or die(mysql_error());
    if( is_array($_POST['wantedall']) )
        mysql_query( "DELETE FROM tplss_wanted WHERE MatchID IN ('".join("','",$_POST['wantedall'])."') AND UserID=" . (int)$_SESSION['user_id'], $connection );
    if( is_array($_POST['wanted']) )
    {
        foreach( $_POST['wanted'] as $wantedmatch )
        mysql_query( "INSERT INTO tplss_wanted (MatchID,UserID) VALUES (".(int)$wantedmatch.",".(int)$_SESSION['user_id'].")", $connection );
    }
}
//
//Onko lomakkeen tiedot lähetetty
//
if(isset($submit) || isset($submit2))
{
$season = $_POST['season'];
$matchtype = $_POST['matchtype'];
$language = $_POST['language'];
$use_filters = $_POST['use_filters'];
$f_dia = $_POST['f_dia'];
$f_mes = $_POST['f_mes'];

//
//Monikielituki on/off?
//
if(!isset($language))
{
	$language = $_SESSION['defaultlanguage_tplss'];
}

//
//Asetetaan arvot
//
$_SESSION['defaultlanguage_tplss'] = $language;
$_SESSION['defaultseasonid_tplss'] = $season;
$_SESSION['defaultmatchtypeid_tplss'] = $matchtype;
$_SESSION['use_filters'] = $use_filters;
$_SESSION['f_dia'] = $f_dia;
$_SESSION['f_mes'] = $f_mes;

//
//Onko tultu pelaajasivulta?
//
if(isset($submit2))
{
	$matchtype_player = $_POST['matchtype_player'];
	$_SESSION['defaultmatchtypeid_tplss'] = $matchtype_player;
}

header("Location: $HTTP_REFERER");
}
elseif(isset($changepage))
{
$changeto = $_POST['changeto'];

//
//Redirect?
//
if($changeto == 0)
	header("Location: index.php");
elseif($changeto == 1)
	header("Location: matches.php");
elseif($changeto == 2)
	header("Location: recordbook.php");
elseif($changeto == 3)
	header("Location: searchengine.php");
elseif($changeto == 4)
	header("Location: thisday.php");
elseif($changeto == 5)
	header("Location: opponentlist.php");
elseif($changeto == 6)
	header("Location: playerlist.php");
elseif($changeto == 7)
	header("Location: links.php");
elseif($changeto == 
	header("Location: http://www.welltrustfc.net/league");
}
elseif(isset($change_opponent))
{
$id = $_POST['opponentid'];

header("Location: opponent.php?opp=$id");
}
elseif(isset($change_player) || isset($change_playeropponent))
{
$id = $_POST['playerid'];
$oppid = $_POST['oppid'];

header("Location: player.php?id=$id&oppid=$oppid");
}
else
{
header("Location: $HTTP_REFERER");
}



?>

Link to comment
Share on other sites
requinix Prolific Member

requinix

Posted
Posted

Yikes.

 

This is a total hack. It is not the correct solution, but it does make a minimal amount of changes to both files. Also: all redirections lead to Rome.

 

matches.php:

// ...
$defaultseasonid = $_SESSION['defaultseasonid_tplss'];
$defaultmatchtypeid = $_SESSION['defaultmatchtypeid_tplss'];
$defaultlanguage = $_SESSION['defaultlanguage_tplss'];


// case 1: user goes to matches.php; session values are X,Y
// - redirects to matches.php?season=X&matchtype=Y
// -> see case 2
// case 2: user goes to matches.php?season=X&matchtype=Y and the season and matchtype values agree with session values X,Y
// - done
// case 3: user goes to matches.php?season=X&matchtype=Y and the season and/or matchtype don't agree with session values U,V
// - redirects to matches.php?season=U&matchtype=V
// -> see case 2
// case 4: user goes to matches.php?season=X&matchtype=Y and submits the form (with season=U matchtype=V)
// - submits to change.php, redirects to matches.php?season=X&matchtype=Y
// -> see case 2 or 3

if ((/* case 1 */ empty($_GET["season"]) || empty($_GET["matchtype"]) ||
    (/* case 3 */ $_GET["season"] != $defaultseasonid || $_GET["matchtype"] != $defaultmatchtypeid))
{
header("Location: matches.php?season=" . urlencode($defaultseasonid) . "&matchtype=" . urlencode($defaultmatchtypeid));
return;
}


// Include language based global vars, in our case english, e.g. $txt_preview = 'Preview'
include('language.inc');
// ...

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

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
Go to topic listing
×
×
  • 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.