Jump to content

Returning correct data from database

brewsky

By brewsky
in PHP Coding Help

 Share

Recommended Posts

brewsky Newbie

brewsky

Posted
Posted

I am currently trying to make a baseball database with player statistics for each particular game.

The page as of now looks something like this:

Opponent #1

    Player #1

    Player #2

    Player #7

Opponent #2

  Player #1

  Player #2

  Player #5

 

and this goes on.

When I click on Player #1 under Opponent #1 I get the correct stats.

However when I click on Player #1 under Opponent #2 I get the same stats as if I was clicking Player #1 under Opponent #1.

 

Here is the coding for the content page:

<?php require_once("includes/functions.php"); ?>
<?php
    if (isset($_GET['gm'])) {
        $sel_gm = get_game_by_id($_GET['gm']);
        $sel_player = NULL;
    } elseif (isset($_GET['player'])) {
        $sel_gm = NULL;
        $sel_player = get_player_by_id($_GET['player'], $sel_gm);
    } else {
        $sel_gm = NULL;
        $sel_player = NULL;
    }
?>
<?php include("includes/header.php"); ?>
<table id="structure">
    <tr>
        <td id="navigation">
            <ul class="subjects">
        <?php
        $game_set = get_all_games();
        while ($game = mysql_fetch_array($game_set)) {
            echo "<li";
            if ($game["Game_ID"] == $sel_gm) { echo " class=\"selected\""; }
            echo "><a href=\"content.php?gm=" . urlencode($game["Game_ID"]) .
                "\">{$game["Opponent"]}</a></li>";
            $player_set = get_players_for_game($game["Game_ID"]);
            echo "<ul class=\"pages\">";
            while ($player = mysql_fetch_array($player_set)){
                echo "<li";
                if ($player["Player_ID"] == $sel_player) { echo " class=\"selected\""; }
                echo "><a href=\"content.php?player=" . urlencode($player["Player_ID"]) .
                    "\">{$player["First"]}" . " " . "{$player["Last"]}</a></li>";
            }  
            echo "</ul>";
        }
        
        ?>
            </ul>
        </td>
        <td id="page">
        <?php if (!is_null($sel_gm)) { // game selected ?>
            <h2><?php echo $sel_gm['Opponent']; ?></h2>
            <?php echo $sel_gm['Game_ID']; ?>
        <?php } elseif (!is_null($sel_player)) { // player selected ?>
            <h2><?php echo "{$sel_player['First']}" . " " . "{$sel_player['Last']}"; ?></h2>
            <div class="page-content">
                <h3><?php echo $sel_player['Opponent']. "<br />" . " " ?></h3>
                <?php echo "PA: " . $sel_player['PA']
                    . "<br />" . " AB: " . $sel_player['AB']. "<br />" . " H: " . $sel_player['H']
                    . "<br />" . " HR: " . $sel_player['HR']. "<br />" . " RBI: " . $sel_player['RBI']
                    . "<br />" . " BB: " . $sel_player['BB']. "<br />" . " Runs: " . $sel_player['Runs']
                    . "<br />" . " SAC: " . $sel_player['SAC']. "<br />" . " ROE: " . $sel_player['ROE']
                    . "<br />" . " 1b: " . $sel_player['1b']. "<br />" . " 2b: " . $sel_player['2b']
                    . "<br />" . " 3b: " . $sel_player['3b']. "<br />" . " TB: " . $sel_player['TB']
                    . "<br />" . " SO: " . $sel_player['SO']. "<br />" . " GIDP: " . $sel_player['GIDP']
                    . "<br />" . " SB: " . $sel_player['SB']. "<br />" . " CS: " . $sel_player['CS']; ?>
            </div>
        <?php } else { // nothing selected ?>
            <h2>Select a game or player to edit</h2>
        <?php } ?>
        </td>
    </tr>
</table>
<?php require("includes/footer.php"); ?>

 

And Here is the coding for the functions:

<?php

    function confirm_query($result_set) {
        if (!$result_set) {
            die("Database query failed:" . mysql_error());
        }
    }
      
    function get_all_games() {
        global $connection;
        $query = "SELECT * ";
        $query .= "FROM offense ";
        $query .= "LEFT JOIN players ON offense.Player_ID = players.Player_ID ";
        $query .= "LEFT JOIN game ON offense.Game_ID = game.Game_ID ";
        $query .= "ORDER BY players.Player_ID ASC ";
        $query .= "LIMIT 1";
        $game_set = mysql_query($query, $connection);
        confirm_query($game_set);
        return $game_set;
    }
    
    function get_players_for_game($Game_ID) {
        global $connection;
        $query = "SELECT players.*, offense.* FROM players
                  INNER JOIN offense ON players.Player_ID = offense.Player_ID
                  WHERE Game_ID = {$Game_ID}
                  ORDER BY players.Player_ID ASC";
        $player_set = mysql_query($query, $connection);
        confirm_query($player_set);
        return $player_set;
    }
    
    function get_game_by_id($Game_ID) {
        global $connection;
        $query = "SELECT game.*, offense.* ";
        $query .= "FROM game ";
        $query .= "INNER JOIN offense ON game.Game_ID = offense.Game_ID ";
        $query .= "WHERE offense.Game_ID=" . $Game_ID . " ";
        $query .= "LIMIT 1";
        $result_set = mysql_query($query, $connection);
        confirm_query($result_set);
        if($game = mysql_fetch_array($result_set)) {
            return $game;
        } else {
            return NULL;
        }
    }
    
    function get_player_by_id($sel_player, $sel_gm) {
        global $connection;
        $query = "SELECT * ";
        $query .= "FROM offense ";
        $query .= "INNER JOIN players ON offense.Player_ID = players.Player_ID ";
        $query .= "INNER JOIN game ON offense.Game_ID = game.Game_ID ";
        $query .= "WHERE offense.Player_ID =" . $sel_player . " ";
        $query .= "AND offense.Game_ID =" . $sel_gm . " ";
        $query .= "ORDER BY players.Player_ID ASC ";
        $query .= "LIMIT 1";
        $result_set = mysql_query($query, $connection);
        confirm_query($result_set);
        if($player = mysql_fetch_array($result_set)) {
            return $player;
        } else {
            return NULL;
        }
    }
?>

 

My 5 tables in my database are:

defense: Player_ID, Game_ID, (this also has all of the stats for defense)

game: Game_ID, Opponent, Game_Date

offense: Player_ID, Game_ID, (this also has all of the stats for offense)

pitching: Player_ID, Game_ID, (this also has all of the stats for pitching)

players: Player_ID, First, Last

Any help would be greatly appreciated.

Thank You

 

 

Link to comment
Share on other sites
brewsky Newbie

brewsky

Posted
  • Author
Posted 
[b]Offense:[/b]
+----------------+---------+------+-----+---------+-------+
| Field          | Type    | Null | Key | Default | Extra |
+----------------+---------+------+-----+---------+-------+
| Player_Game_ID | int(11) | NO   |     | NULL    |       |
| Player_ID      | int(11) | NO   | PRI | NULL    |       |
| Game_ID        | int(11) | NO   | PRI | NULL    |       |
| PA             | int(11) | YES  |     | NULL    |       |
| AB             | int(11) | YES  |     | NULL    |       |
| H              | int(11) | YES  |     | NULL    |       |
| HR             | int(11) | YES  |     | NULL    |       |
| RBI            | int(11) | YES  |     | NULL    |       |
| BB             | int(11) | YES  |     | NULL    |       |
| Runs           | int(11) | YES  |     | NULL    |       |
| HBP            | int(11) | YES  |     | NULL    |       |
| SAC            | int(11) | YES  |     | NULL    |       |
| ROE            | int(11) | YES  |     | NULL    |       |
| 1b             | int(11) | YES  |     | NULL    |       |
| 2b             | int(11) | YES  |     | NULL    |       |
| 3b             | int(11) | YES  |     | NULL    |       |
| TB             | int(11) | YES  |     | NULL    |       |
| SO             | int(11) | YES  |     | NULL    |       |
| GIDP           | int(11) | YES  |     | NULL    |       |
| SB             | int(11) | YES  |     | NULL    |       |
| CS             | int(11) | YES  |     | NULL    |       |
+----------------+---------+------+-----+---------+-------+

[b]players:[/b]
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| Player_ID | int(11)     | NO   | PRI | NULL    | auto_increment |
| First     | varchar(15) | NO   |     | NULL    |                |
| Last      | varchar(20) | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

[b]game:[/b]
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| Game_ID   | int(11)     | NO   | PRI | NULL    | auto_increment |
| Opponent  | varchar(25) | NO   |     | NULL    |                |
| Game_Date | date        | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

Link to comment
Share on other sites
brewsky Newbie

brewsky

Posted
  • Author
Posted

Alright, I think I figured out. I marked the changes I made in red.

[b]content:[/b]
<?php require_once("includes/functions.php"); ?>
<?php
    if (isset($_GET['gm'][color=red], $_GET['player'][/color])) {
        $sel_gm = get_game_by_id($_GET['gm'][color=red], $_GET['player'][/color]);
        $sel_player = NULL;
    } elseif (isset($_GET['player'])[color=red], $_GET['gm'][/color]) {
        $sel_gm = NULL;
        $sel_player = get_player_by_id($_GET['player'], [color=red]$_GET['gm'][/color]);
    } else {
        $sel_gm = NULL;
        $sel_player = NULL;
    }
?>
<?php include("includes/header.php"); ?>
<table id="structure">
    <tr>
        <td id="navigation">
            <ul class="subjects">
        <?php
        $game_set = get_all_games();
        while ($game = mysql_fetch_array($game_set)) {
            echo "<li";
            if ($game["Game_ID"] == $sel_gm) { echo " class=\"selected\""; }
            echo "><a href=\"content.php?gm=" . urlencode($game["Game_ID"]) .
                "\">{$game["Opponent"]}</a></li>";
            $player_set = get_players_for_game($game["Game_ID"]);
            echo "<ul class=\"pages\">";
            while ($player = mysql_fetch_array($player_set)){
                echo "<li";
                if ($player["Player_ID"] == $sel_player) { echo " class=\"selected\""; }
               [color=red] $query_string = 'player=' . urlencode($player['Player_ID']) . '&gm=' . urlencode($player['Game_ID']);[/color]
                echo "><a href=\"content.php?=" . [color=red]htmlentities($query_string)[/color] .
                    "\">{$player["First"]}" . " " . "{$player["Last"]}</a></li>";
            } 
            echo "</ul>";
        }
       
        ?>
            </ul>
        </td>
        <td id="page">
        <?php if (!is_null([color=red]$sel_player[/color])) { // game selected ?>
            <h2><?php echo [color=red]$sel_player[/color]['Opponent']; ?></h2>
            <?php echo [color=red]$sel_player['Opponent'][/color]; ?>
        <?php } elseif (!is_null([color=red]$sel_gm[/color])) { // player selected ?>
            <h2><?php echo "{[color=red]$sel_gm[/color]['First']}" . " " . "{[color=red]$sel_gm[/color]['Last']}"; ?></h2>
            <div class="page-content">
                <h3><?php echo [color=red]$sel_gm['Opponent'][/color]. "<br />" . " " ?></h3>
                <?php echo "PA: " . [color=red]$sel_gm[/color]['PA']
                    . "<br />" . " AB: " . [color=red]$sel_gm[/color]['AB']. "<br />" . " H: " . [color=red]$sel_gm[/color]['H']
                    . "<br />" . " HR: " . [color=red]$sel_gm[/color]['HR']. "<br />" . " RBI: " . [color=red]$sel_gm[/color]['RBI']
                    . "<br />" . " BB: " . [color=red]$sel_gm[/color]['BB']. "<br />" . " Runs: " . [color=red]$sel_gm[/color]['Runs']
                    . "<br />" . " SAC: " . [color=red]$sel_gm[/color]['SAC']. "<br />" . " ROE: " . [color=red]$sel_gm[/color]['ROE']
                    . "<br />" . " 1b: " . [color=red]$sel_gm[/color]['1b']. "<br />" . " 2b: " . [color=red]$sel_gm[/color]['2b']
                    . "<br />" . " 3b: " . [color=red]$sel_gm[/color]['3b']. "<br />" . " TB: " . [color=red]$sel_gm[/color]['TB']
                    . "<br />" . " SO: " . [color=red]$sel_gm[/color]['SO']. "<br />" . " GIDP: " . [color=red]$sel_gm[/color]['GIDP']
                    . "<br />" . " SB: " . [color=red]$sel_gm[/color]['SB']. "<br />" . " CS: " . [color=red]$sel_gm[/color]['CS']; ?>
            </div>
        <?php } else { // nothing selected ?>
            <h2>Select a game or player to edit</h2>
        <?php } ?>
        </td>
    </tr>
</table>
<?php require("includes/footer.php"); ?>

[b]Functions:[/b]
<?php

    function confirm_query($result_set) {
        if (!$result_set) {
            die("Database query failed:" . mysql_error());
        }
    }
     
    function get_all_games() {
        [color=red]global $connection;
        $query = "SELECT * FROM game
                  ORDER BY Game_Date ASC";
        $game_set = mysql_query($query, $connection);
        confirm_query($game_set);
        return $game_set;[/color]
    }
   
    function get_players_for_game($Game_ID) {
        global $connection;
        $query = "SELECT players.*, offense.* FROM [color=red]offense[/color]
                  INNER JOIN [color=red]players [/color]ON [color=red]offense[/color].Player_ID = [color=red]players[/color].Player_ID
                  WHERE [color=red]offense[/color].Game_ID = {$Game_ID}
                  ORDER BY players.Player_ID ASC";
        $player_set = mysql_query($query, $connection);
        confirm_query($player_set);
        return $player_set;
    }
   
    function get_game_by_id($Game_ID, [color=red]$Player_ID[/color]) {
        global $connection;
        $query = "SELECT [color=red]*[/color] ";
        $query .= "FROM [color=red]offense [/color] ";
        $query .= "INNER JOIN [color=red]game [/color]ON [color=red]offense[/color].Game_ID = [color=red]game[/color].Game_ID ";
        [color=red]$query .= "INNER JOIN players ON offense.Player_ID = players.Player_ID ";[/color]
        $query .= "WHERE offense.Game_ID=" . $Game_ID . " ";
        [color=red]$query .= "AND offense.Player_ID =" . $Player_ID . " ";[/color]
        $query .= "LIMIT 1";
        $result_set = mysql_query($query, $connection);
        confirm_query($result_set);
        if($game = mysql_fetch_array($result_set)) {
            return $game;
        } else {
            return NULL;
        }
    }
   
    
?>

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.