Good advice for design before i start

[Bendude14]

What i would like to achieve is this. I have a database called teams and inside that database i am storing Team Names and all the players who play for that team. On the site i currently display the biggest defeat of one team over another updated once a week. So i have wrote this code that can selects the correct details and then echos it to the page.

 

<?php 
$win_team = "Bens team";
$lose_team = "Johns team";

  $sql = "SELECT team_name, player1, player2, player3, player4, player5, player6 FROM teams WHERE team_id = '1'";
  $result = mysql_query($sql) or trigger_error('Query failed: '. mysql_error());
  $win_players = mysql_fetch_row($result) or trigger_error('Query failed: '. mysql_error());
  
  $sql2 = "SELECT team_name, player1, player2, player3, player4, player5, player6 FROM teams WHERE team_id = '2'";
  $result2 = mysql_query($sql2) or trigger_error('Query failed: '. mysql_error());
  $lose_players = mysql_fetch_row($result2) or trigger_error('Query failed: '. mysql_error());
  
  
  ?>
  <table>
  
      <?php $i = 0;
  foreach($win_players as $players) {
  
  
  		echo "<tr><td>".$players."</td><td> </td><td> </td> <td>".$lose_players[$i]."</td></tr>";
		$i++;

  }

?>

 

I would like to make a form that can be filled in probably just using a drop down box so i can simply select the winning team and the losing team post the form and it will update this into the database probably with a timestamp. How to store this info i dont no yet??

 

Then i would like the homepage to automatically display the latest results using the team info and the info submitted with the form which will be both stored in the same DB presumably in different tables for the last month?

 

Hope this makes sense..

I am mainly just after some ideas on the best way to go about this before i start to build the database etc.. At the moment im just testing locally with a dummy database so any suggestions in the DB design would also be helpful..

 

Thanks

Ben

[Bendude14]

Can someone help me with this??

 

I have now tried to make a table of players and a table of teams with a foreign key linking them but i dont no if im any better off.?

 

Some advice would be great thanks.

 

 

[MasterACE14]

you can simplify the queries first...

 $sql = "SELECT * FROM teams WHERE team_id = '1'";

 

you can use your drop down box to post to the same page and use something like.

 

<?php

if(isset($_POST['update']))
{

// run your mysql update query here

} else {

$win_team = "Bens team";
$lose_team = "Johns team";

// etc etc

?>

 

For the homepage you can use mysql WHERE clause to find last months using the timestamp.

eg:

WHERE `time` < '2419200'

 

something like that. I'll leave the that to you to work out how you want it to be done.

 

I have a database called teams

FROM teams

Thats a table 

 

Regards ACE

 

 

 

[Bendude14]

thanks for the reply ive tried this two ways now so just another quick question.

 

am i better of with a table of players and a table teams?

 

or with one table including both teams and players like in my previous example...

[Bendude14]

also i can not simplify the SQL Statements to select * because then i get the player_id and the timestamp been retrieved which i dont want..

 

Thanks again