[SOLVED] MySQL array?

[Phate]

Hi,

 

need some beginner help with my tournament script again.

 

I have my db, with in there, 2 tables: users and tournaments.

 

Now I need to kinda "link" those. There's a page that query's for all a tournaments info and displays it, and there a player can subscribe.

But than they need to show on the tournament info page to.

 

I've been thinking, and I've come up 2 ways (or atleast the logic part in it, not the practical work)

 

1. I place the player names in a text field in my tournaments table separated by comma's.

But that way, I cannot make player pages that summons all a players info, including which tournaments he is attending.

I also wouldn't be able to let them unsubscribe I think.

 

2. I add the tournament name to the usertable. But that seems even harder.

 

So I need some way of linking those 2, a way that I can still have freedom with the data, so make a player page with all his tournaments, a tournament page with all the players, allow the play to unsubscribe, ...

 

Can anyone tell me the best way to get this done?

[Daukan]

You can join two tables in an sql query. Not sure how your schema is set up but here is an example of a generic join. There are also RIGHT JION and LEFT JOIN. You might want to look up joins in the mysql manual

<?php
$query = "
        SELECT u.name, t.torny_name, t.date 
        FROM users as u, tournaments as t
        WHERE u.uid=t.uid";
?>

[Daniel0]

CREATE TABLE `tournaments` (
  `id`            INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name`          VARCHAR(100) NOT NULL,
  # etc...
);

CREATE TABLE `users` (
  `id`            INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `username`      VARCHAR(100) NOT NULL,
  # etc...
);

CREATE TABLE `users_tournaments` (
  `user_id`        INTEGER NOT NULL REFERENCES `user`(`id`),
  `tournament_id`  INTEGER NOT NULL REFERENCES `tournaments`(`id`)
);

 

<?php
try {
$db = new PDO('mysql:dbname=something;host=localhost', 'username', 'password');
}
catch(PDOException $e)
{
die('Connection failed: ' . $e->getMessage());
}

if(empty($_GET['tournament_id']))
{
die('No tournament id set');
}

$tournament_stmt = $db->prepare('SELECT * FROM `tournament` WHERE `id` = :id LIMIT 1');
$tournament_stmt->exec(array(':id' => $_GET['tournament_id']));
$tournament = $tournament_stmt->fetch(PDO::FETCH_ASSOC);

$users_stmt = $db->prepare('SELECT u.* FROM `users_tournaments` AS ut JOIN `users` AS u ON u.`id` = ut.`user_id` WHERE ut.`tournament_id` = :tournament_id ORDER BY u.`username`');
$users_stmt->exec(array(':tournament_id' => $_GET['tournament_id']));
$users = $users_stmt->fetchAll(PDO::FETCH_ASSOC);
?>

 

Not tested.

[Gamic]

Your solutions involve column overloading, which is probably not the best solution to your problem.

 

ok, what you have here is a many to many relationship between tournaments and players.

 

You brake a many to many with another table.

 

players -< player_tournament_link >- tournaments

 

How this would work in implementation is something like this:

 

If players has a primary key of playerID and tournaments has a primary key of tournamentID then your new link table with have two fields, both playerID and tournamentID. This two fields are the primary key for that link table.

 

This allows you to have many players in one tournament

and to have the same player in different tournaments.

 

You would create something like this, like this:

 

create table players(
playerID int auto_increment not null,
primary key(playerID)
);

create table tournaments(
tournamentID int auto_increment not null,
primary key(tournamentID)
);

create table player_tournament(
playerID int not null,
tournamentID int not null,
primary key (tournamentID,playerID)
);

/*
testing data
*/

insert into players values(1);
insert into players values(2);
insert into players values (3);
insert into tournaments values(1);
insert into tournaments values(2);
insert into player_tournament(playerID,tournamentID) values(1,1);
insert into player_tournament(playerID,tournamentID) values(1,2);
insert into player_tournament(playerID,tournamentID) values(2,2);
insert into player_tournament(playerID,tournamentID) values(3,1);

 

And if we wanted to find out which players where in a tournament or which tournaments a player was in we could do something like this:

select * from players natural join player_tournaments natural join tournaments
//where tournaments.tournamentID =1;//all players in tournament 1
//where players.playerID=1;//all tournaments player 1 has been in.

 

If this hasn't helped at all there is some good info on google about many to many relationships.

[Daniel0]

Your solutions involve column overloading, which is probably not the best solution to your problem.

 

Are you referring to my or Daukan's code or his own suggested solutions?

[Gamic]

Your solutions involve column overloading, which is probably not the best solution to your problem.

 

Are you referring to my or Daukan's code or his own suggested solutions?

 

Sorry, I was referring to the original post.

[Phate]

Thanks guys, the many to many relationship is new to me and now seems very logical.

 

If I encounter any problems, I'll hop in on IRC.

 

Thanks again!