CODEWITHSUNDEEP
mysqlsqlcakephpforeign-keys
Gilko
Gilko

Reputation: 2387

cakePHP : populating 2 fields with the same foreign key and write to database

I have a players table and a videos table. In the videos table I have 2 fields (PlayerA and PlayerB) and the values have to come from the players table. But when I have 2 foreign keys in the table videos with the name player_id it says that I have a duplicate.

Now I can only store 1 player Name to the database.

Players table:

1   player_id   int(11)
2   player_firstname    varchar(45)
3   player_surname  varchar(45)
4   player_birthDate    date
5   player_turnedPro    year(4)
6   player_nickname varchar(45)
7   player_nationality  varchar(45)
8   player_highestBreak varchar(45)
9   player_highestRanking   int(11)
10  player_centuryBreaks    int(11)
11  player_careerWinnings   varchar(55)
12  player_worldChampion    varchar(45)
13  player_image    varchar(255)
14  player_category varchar(45)

Videos table:

1   video_id    int(11)
2   video_title varchar(45)
3   video_date  timestamp
4   video_scoreA    tinyint(4)
5   video_scoreB    tinyint(4)
6   video_url   varchar(255)
7   tournament_id   int(11)
8   timeline_id int(11)
9   player_id   int(11) => PLAYER A
10  player_id   int(11) => PLAYER B => here it says that I have a duplicate

Is there a possibility to store values from a players table, that are populated in 2 fields, in a Videos table?

Update

videos table:

1   video_id    int(11)
2   video_title varchar(45)
3   video_date  timestamp
4   video_scoreA    tinyint(4)
5   video_scoreB    tinyint(4)
6   video_url   varchar(255)
7   tournament_id   int(11)
8   timeline_id int(11)

players_videos table:

1   videos_players_id   int(11)     
2   video_id    int(11)     
3   player_id   int(11)

Video Model:

public $hasAndBelongsToMany = array(
       'Player' =>
           array(
               'className' => 'Player',
               'joinTable' => 'players_videos',
               'foreignKey' => 'video_id',
               'associationForeignKey' => 'player_id',
               'unique' => true,
           )
   );

Player Model:

public $hasAndBelongsToMany = array(
   'Video' =>
       array(
           'className' => 'Video',
           'joinTable' => 'players_videos',
           'foreignKey' => 'player_id',
           'associationForeignKey' => 'video_id',
           'unique' => true,
       )
    );

VideosController in the add action :

$players = $this->Video->Player->find('list', array('fields'=>'player_firstname', 'player_surname'));

$this->set(compact('tournaments', 'timelines', 'players'));

add.ctp View:

echo $this->Form->input('Player.Player');

index.ctp View:

<?php foreach($players as $player): ?>
   <?php //echo h($player['player_id']); ?>
   <p><?php echo $this->Html->link($player['player_id'], array('controller' => 'players', 'action' => 'view', $player['player_id'])); ?></p>
<?php endforeach; ?>

Upvotes: 0

Views: 376

Answers (2)

philipxy
philipxy

Reputation: 15148

You cannot have two fields/columns with the same name in one table. You must give them different names. (If you wanted one in particular, how would you ask for it?) Eg call them player_a and player_b.

A foreign key column/field does not have to have the same name as the column/field it references. In SQL you would write

create table Videos
(....
player_a int(11),
player_b int(11),
FOREIGN KEY player_a REFERENCES Players (player_id),
FOREIGN KEY player_b REFERENCES Players (player_id),
...)

Upvotes: 1

decocodes
decocodes

Reputation: 116

I do not recommend this approach. The HABTM association is a better alternative for this case. You can check the documentation here: http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html#hasandbelongstomany-habtm

But... what you asked for:

In your Video model:

public $hasMany = array(
    'Player A' => array(
        'className' => 'Player',
        'foreignKey' => 'player_a_id'
    ),
    'Player B' => array(
        'className' => 'Player',
        'foreignKey' => 'player_b_id'
    )
)

In your Player model:

public $belongsTo = array(
    'Video' => array(
        'className' => 'Video',
        'foreignKey' => 'video_id'
    )
);

Rename your columns to player_a_id and player_b_id.

Upvotes: 1

Related Questions