Jump to content
Larry Ullman's Book Forums

Recommended Posts

Hey

 

I'm currently saving lots of football statistic in a database. I need to dynamically create INSERT QUERIES with the MATCH ID and PLAYER ID's of the footballers attending the match.

 

There are possibly 14 players in a football match (11 starters and 3 substitues). I therefor need to query ALL players in the database 14 times

 

The Player's ID's is selected from another table in the database. All Player ID'S are displayed in a html option list like this:

 

$number_of_players = 14;

echo '<form enctype="multipart/form-data" action="" method="post">';

for ($i=0; $i<$number_of_players; $i++)
   {
	$result = mysql_query($query, $connect);
	echo '<select name="player.$i.'">';
		while($row = mysql_fetch_array($result, MYSQLI_ASSOC)) 
       	{
			echo '<option value="'.$row[id].'">'.$row[name].'</option>';
		}
	echo '</select> <br /> <br />';
}
echo '<input type="submit" name="submit" value="Find IDs" />
</form>';

 

This produces a list of 14 HTML Select with NAME player+$i (player0, player1, player2, player3, etc). This creates global $_POST["players'.$i.'"]

 

for ($i=0; $i<$antall; $i++) {
  echo 'INSERT INTO abc_players_matches ( match_id, player_id, sub) VALUES ('.$match.', '.$_POST["player'.$i.'"].' , 0);' . '<br />';
}

 

The problem is that $_POST["players'.$i.'"] is empty. What am I doing wrong?

 

The simple interface is looking like this:

Link to comment
Share on other sites

I'd approach it a bit differently.

 

1) Don't append an ID to the end of the select name - instead set the name like so: name="players[]" which will give you an array you can just loop through:

 

foreach($_POST['players'] as $player){
   // Execute query
}

 

2) Use a prepared statement instead of a standard query - so you only have to pass the SQL to the DB once.

 

$q = "INSERT INTO abc_players_matches (match_id, player_id, sub) VALUES (?, ?, ?)";
$stmt = mysqli_prepare($dbc, $q);
mysqli_stmt_bind_param($stmt, 'iii', $match, $player, $sub);

foreach($_POST['players'] as $player){

 $player = intval($player);
 mysqli_stmt_execute($stmt);

}

mysqli_stmt_close($stmt);

 

Need to define $match and $sub also to actually use that code.

  • Upvote 1
Link to comment
Share on other sites

  • 3 weeks later...

Actually. Not quite solved yet.

 

This is the case:

 

I need to create a multi-dimensional array with the following example information:

 

$players = array(
	0 => array("match_id" => 855, "player_id" =>  1, "time_played" => 90 , "sub" => 0), 
	1 => array("match_id" => 855, "player_id" => 11, "time_played" => 45 , "sub" => 1),
	2 => array("match_id" => 855, "player_id" =>  7, "time_played" => 45 , "sub" => 2),
	3 => array("match_id" => 855, "player_id" =>  9, "time_played" => 90 , "sub" => 0)
);

 

The match_id is not important, but I need the players ID, How many minutes they've played (standard is 90), and their substitution status (0 is full match, 1 in subbed inn, 2 is subbed out).

 

How can I manage this with input lists and checkboxes in Xhtml? The code for getting player id, time played and sub status looks like this:

 

	// The player's ID is queried from the DB before this.

while($row = mysql_fetch_array($result, MYSQLI_ASSOC)) {

	echo '<div style="max-width: 300px width: 300px;"><input type="checkbox" name="player[][id]" value="'.$row[id].'"  /> '.$row[name].'</div>
	<input type="text" name="player[][time]" value="90" maxlength="2" />
	<div>INN? <input type="checkbox" name="player[][inn]" value="1" /></div>
	<div>UT? <input type="checkbox" name="player[][out]" value="2" /></div>
	<br />';

}

 

And how would the foreach work with this? I'm not (any) good with arrays.

Link to comment
Share on other sites

I am a little confused by what you want, but it seems like the while loop should handle everything for displaying the information, and you don't need a foreach loop.

 

Do you want a separate form for each player, or do you want a single form for all the players?

 

Also, is this for a back end that can be used for editing player stats?

 

If that's the case, I'd probably use standard input fields for both the player number and how many minutes they've played.

 

Then, I'd make a drop-down list for the three substitution options.

 

Is that not what you want? Sorry, but a bit confused.

Link to comment
Share on other sites

First of all:

 

Sorry for the long post. This can seem VERY much more complex than I think it is. English is not my main language, so there you go. Please ask if you need clarification of something. :)

 

I am a little confused by what you want, but it seems like the while loop should handle everything for displaying the information, and you don't need a foreach loop.

 

The while loop is working perfectly. But they are only needed to get the ID of the game and the SELECTED players ID from the form. This form will THEN create number of minutes and substitution status bases on input fields connected to every player.

 

Do you want a separate form for each player, or do you want a single form for all the players?

 

That is not important. As long as the input from the form can be linked with THAT spesific player, it does not matter.

 

Also, is this for a back end that can be used for editing player stats?

 

Exactly! :)

 

The players ID and game ID is used for saving information regarding that player.

 

If that's the case, I'd probably use standard input fields for both the player number and how many minutes they've played

 

Then, I'd make a drop-down list for the three substitution options.

 

Is that not what you want? Sorry, but a bit confused..

The number of minutes is a standard input field (at least I thought so.) The players ID cannot be as those using the backend might not know the ID of every player (which may differ from their shirt number)

 

The drop down list for substitution is genius though. Will definitely use that.

 

I will write in list form. Probably make it a bit more clear.

 

This is what I want:

- Saving players stats over whole seasons.

- I need the game ID, the players ID, the minutes played and substitution info on every player for every game of the season.

 

The structure of the table look like this:

stats (game_id*, player_id*, minutes, substitution)

 

Underscore: Primary key

Asterix (*): Foreign keys

What I already have:

- Tables for player information

- Tables for league, clubs, teams and matches.

 

What I want with the data: (And is possible through getting this I'm working on right)

- Query the number of games of each player (witch will again be used to calculate other stats. Like goals per game, bookings per game, etc)

- Query the number of times a player is substituted onto the pitch

- Query how many minutes a player has had in a season, last five games, previous game

- A lot more

 

Screenshot of the backend:

 

921511.jpeg921512.jpeg

Edited by Antonio Conte
Link to comment
Share on other sites

As an unrelated point, I'm I bit confused about the best way to structure the database for what you're doing. What I mean is that you have a players table and a games table, which seem to have a many-to-many relationship. How did you go about resolving that?

 

Anyway, to your more immediate issue, having separate forms for each player would definitely be the easier way to go. That way, when you submit a form, you know exactly what player it's for.

 

If you use one form for all the players (in what I assume is one game), then you need to either check the players whose stats you want to edit, or you need to submit the stats for every player everytime a change is made, which seems wasteful.

 

With that said, I'm thinking that if you attach the player IDs to their corresponding fields, it might be possible. For example, you might have something like the following:

 

Pseudo code

 

Start of form

Check box for player #1
Minutes field for player #1 (The name of this field is minutes1.)
Drop-down list of sub status for player #1 (The name of the selected value is sub1.)

Check box for player #57
Minutes field for player #57 (The name of this field is minutes57.)
Drop-down list of sub status for player #57 (The name of the selected value is sub57.)

Rinse and repeat.

End of form

 

Anyway, that's just one idea. Please excuse me, as I haven't thought this through thoroughly. Please give me some more time to think about it. In the meantime, hopefully you'll find a good solution or someone else will provide some sage-like advice.

  • Upvote 1
Link to comment
Share on other sites

I actually solved it myself!

 

Thanks anyway Hartleysan. You have had some really good suggestions and questions that helped me a great deal. :)

 

------------------------------------

 

The script:

 

To get the game:

echo '<select name="giornata">';

while($games = mysql_fetch_array($game_result, MYSQLI_ASSOC)) 
{
	echo '<option value="'.$games[id].'"> '.$games[hometeam] .' - '. $games[awayteam].'</option>';
}

echo '</select>';

To get the players ID and output form to save stats to them:

 

$i = 0;

while( $row = mysql_fetch_array( $result, MYSQLI_ASSOC ) ) 
{			
	echo '
  		<input type="checkbox" name="player['.$i.'][id]" value="'.$row[id].'"  /> '.$row[name].'
	<input type="text" name="player['.$i.'][time]" value="" maxlength="2" />
	<select name="player['.$i.'][sub]">
		<option value="0">Full match</option>
		<option value="1">Subbed Inn</option>
		<option value="2">Subbed Out</option>
	</select>';
	$i++;
}

 

To display (or insert) the data from the players:

 

	
  foreach ($_POST[player] as $player) {
	echo  'INSERT INTO abc_players_matches ( match_id, player_id, min, sub) VALUES  ('.$_POST[giornata].', '.$player['id'].', '.$player['time'].',  '.$player['sub'].'); <br />';
}

What it now produces (it's correct!!!):

INSERT INTO abc_players_matches ( match_id, player_id, min, sub) VALUES (857, 1, 30, 0);

INSERT INTO abc_players_matches ( match_id, player_id, min, sub) VALUES (857, 13, 40, 1);

INSERT INTO abc_players_matches ( match_id, player_id, min, sub) VALUES (857, 30, 50, 2);

etc...

 

-----------------------------------

The database

 

...is quite large with a lot of many-to-one and many-to-many relationships. That is not a problem.

 

Seasons:

season (id, name)

Leagues:

league (id, name, season*, total_rounds, games_per_round)

 

Clubs:

clubs (id*, country*, name, logo, stadium_name)

 

Games:

games (id, league_id*, round, club1_id*, club2_id*, kickoff_time, goals_club1, goals_club2)

 

Players:

players (id, number, firstname, lastname, birthplace, country*, birthdate, position)

 

Game stats by players:

stats_players_games (match_id*, player_id*, league_id*, time, sub)

 

+ A lot of other tables for country names, country codes, player biographies, table calculation, etc. They are not really that interesting here.

Views:

 

To display game stats:

view_players_games (match_id*, league*, season*, hometeam_name, awayteam_name, player_id*, player_name, sub, kickoff*)

 

--------------------------------

 

The views link together all information about seasons, leagues, clubs, games, players and everything else. I only need to fill stats_players_games with info about the players attending the match.

 

The same script will be modified and used to save stats about (on a per-game-setting):

- Goals

- Yellow cards

- Red cards

- Goal assists

 

It would be possible to save all information at once. But as that adds several layers of complexity, I will start with saving players in each match to start. Adding stats such as goals and cards are a lot less work.

-----------------------------------

I posted this anyway as someone else might need to do something similar. (Not that unlikely with movies, books, cars, or other stuff) :)

Link to comment
Share on other sites

Thank you for posting your solution, Antonio. I'm trying to create a very similar script, although it's for a French dictionary, and was struggling with the $i variable: I knew I had to use it somehow, but was still grappling in the dark!

Link to comment
Share on other sites

That multidimensional array approach is definitely better than what I came up with. In a way, you're creating an object for each player, which makes total sense. Wish I had thought of that!

 

Anyway, if Larry or someone else wouldn't mind piping in, I'm still curious as to the best way to handle many-to-many relationships, like the one that exists here between players and games (i.e., a player can be in multiple games (matches), and a game can have multiple players).

Link to comment
Share on other sites

Did you manage to get it work?

 

I haven't worked at it since I read your solution, but I think it will work. I had the idea to search for a similar solution since reading #27 from Wicked Cool PHP where they use it (although the $i variable is used in a hidden field), saying it's often the way to go when you know you will have a multidimensional array but don't know how many elements there will be in it in advance. Their example is for a shopping cart: you don't know how many items the customer will buy, so you number them this way. I happened to read this yesterday, just an hour or two before you posted your solution, so I was trying to apply it to your problem and mine, but you were quicker!

Link to comment
Share on other sites

Anyway, if Larry or someone else wouldn't mind piping in, I'm still curious as to the best way to handle many-to-many relationships, like the one that exists here between players and games (i.e., a player can be in multiple games (matches), and a game can have multiple players).

 

Sorry. Don't quite understand what you are trying to say here. But I'll make a shot at it.

 

You could create some very much alike my table structure, but use an incremented ID, and use the player and match id as unique. That would allow you to save ANY information about that player in that match.

 

Understand? Besides that, there is really not much to do to improve the structure.

 

Eighter way. Keep it simple.

Link to comment
Share on other sites

I understand the fact that each player and each match has a unique ID. That's fine.

 

What I was trying to say is that because many different players are in one match, but also, a single player is (probably) in many matches, it seems like a many-to-many relationship.

 

For example, if the match IDs were used as foreign keys in the players table, then each player would have a field with a ton of match IDs, right? Likewise, if the matches table uses the player ID as a foreign key to list all the players in a given match, that field would be massive.

 

So my question is, how are you managing all this information so that you can determine exactly what matches a player was in and how long that player was in those matches?

Link to comment
Share on other sites

In short, a third table. Players, Games, Game_stats.

 

- On for players

- On for games (foreign keys for clubs, league and season)

- On for stats (foreign keys from players and games

 

What I do, is keep all tables (around 15) on INF3 or Boyce Codd, allow for very little redudancy, and make sure the data has integrity. (Non to little chance mispelling etc)

 

Then I write large views that join all these pieceses of information together for me.

 

The view for game stats has season id, season name, league id, league name, home and away team, player id, player name and match time. That way I can write really easy select queries with what I want to display, and still query things like WHERE (season_id = 8) AND (player_id = 10) ORDER BY gametime desc LIMIT 5. (the posibilities are many)

 

I actually list very many of my tables in a post in this thread. It does not show relationship, but the foreign keys give you a lot of info of how it is done. :)

 

-------------------------------------

 

By the way. If you have many-to-many relationships, that is often a sign that you need another table. Redudancy also gives you away. (I have a small table with nicknames for football players. It's only five-six rows of info, and uses the same player_id as foreign key to the players table)

Link to comment
Share on other sites

Okay. Well, that being the case, it sounds like the stats table is really big (i.e., each player in each game has a record). Is that true? If that's the case, then I understand how your system works.

 

Also, very good idea to use views. Smart!

Link to comment
Share on other sites

I think what you need is another table to do just the multiple links. Lets name it multis. Multis will have just two pieces of data. For example if you had Products and Categories. Each row will be an id for the Category and an id for the Product. As many rows of data as you need. This table links the other two tables with mulit-multi links.

Link to comment
Share on other sites

That's 100% right and the way I've done things, abigail. Don't know if you tried to help our just explained it, but thanks anyway. :)

 

I've actually solved this issue, but I and HartleySan are just discussing table structures.

 

Larry could label this is [sOLVED] if he cares.

 

Thanks everyone for your help. I could explain more over PM or even MSN/Skype if you want HartleySan.

Link to comment
Share on other sites

Very slick, Antonio. It sounds like you're going to be the database of European soccer (sorry, American!) around the world before too long.

 

Been working on my own site for quite some time as well, and hoping to share it here once I finally finish it.

 

Edit: Antonio, I might hit you up on that PM offer another time, but I want to think about things more first. As far as I can tell, your solution is the best, but perhaps there are other options.

 

I really don't know though. I think your method is probably the best though.

Link to comment
Share on other sites

 Share

×
×
  • Create New...