Creating a table column on the fly

PHP
1

I have 2 tables, tourneys and Teams. When I create a tourney it is auto-incremented and when I create a team it too, is auto-incremented. What I need to do is relate the team and the tourney. Easy enough if I have only 1 tourney. My thought is to add a column to the team that is the same as the tourney tid, call these columns ‘tid1’ ‘tid2’ tid3 etc. and do this upon tourney creation

If I know the ‘tid’ of the tourney would it be best to add the tid column to the Team table on tourney creation?
How would you solve this problem?

I was thinking of playing with this code to achieve my desired result. What are your thoughts?


//Copyright Lawrence Truett and www.FluffyCat.com May 29, 2007  
  function addColumnIfItDoesNotExist() {
     $query = ("SHOW COLUMNS ".
              "FROM tableNameToAddColumnTo ".
              "LIKE '%newColumnName%'");
    $result = mysql_query($query)
        or die("select table tableNameToAddColumnTo ".
             "in addColumnIfItDoesNotExist() not successful: ".
      mysql_error());    $rarray = mysql_fetch_array($result);
    if (NULL == $rarray[0]) {
      $query = ("ALTER TABLE tableNameToAddColumnTo ". 
               "ADD COLUMN newColumnName VARCHAR(100) ".
                "AFTER columnBeforeNewColumn;");
      $result = mysql_query($query)
         or die("altering table tableNameToAddColumnTo not successful: ".
        mysql_error());
          }
    return;
  }

Thanks,
Chevy

2

This is a many to many relationship (each team can participate at more than one tourney, and at each tourney participate more teams), and what you need is a third table, that connects the teams and tourneys. This table contains two columns: team id and tourney id, and you will add a row for each new team-tourney combination.