Mysql/php random

Question

I want to do an random of some items present in my table

So I have this table :

ID_TEAM  #  NAME_TEAM
1            CHELSEA
2            MANU
3            MANC
4            LIVERPOOL
5            ARSENAL

My problem is to do an random between this teams for have a match with each other

Chelsea vs MANU
MANU vs MANC
LIVERPOOL vs Arsenal
Manu vs Liverpool
Chelsea vs MANC
...

I want that all teams play a match with each other... Can I do that with a random ? If not, what Can I choose ? Thank you !

Do you want this on MySql side? Might be much easier to do it on php-side. — Jonny 5
– Jonny 5, Commented Jan 2, 2014 at 23:14
I want to do that on php side. I want also that when I made this kind of random, I get the ID_TEAM of the team like this CHELSEA VS LIVERPOOL (value=1) vs (value=4) You understand ? — user3149518
– user3149518, Commented Jan 2, 2014 at 23:18
Do you really need to random it? What if it repeats a game? Depending of what you really want to do, instead of random matches, I'd prefer to create all matches like: $team1 vs $team2; $team3 vs $team4; $team5 vs $team1 and them I would random which one would be $team1, which one would be $team2 and etc. — Leo
– Leo, Commented Jan 2, 2014 at 23:23
Maybe it could be helpful: bluebones.net/2005/05/league-fixtures-generator — Louis XIV
– Louis XIV, Commented Jan 2, 2014 at 23:33

Memor-X · Accepted Answer · 2014-01-03 00:46:12Z

5

you can use a CROSS JOIN in MySQL with the table itself like

SELECT team1.name as 'team1', team2.name as 'team2' from team_table AS team1
    CROSS JOIN team_table AS team2
WHERE team1.name <> team2.name
    AND team1.id < team2.id;

here's an SQL Fiddle pages showing you how it works

just use PHP to execute it and you can process the returned data, if you want it random you have the entire results set, format it into an array and use array_rand

EDIT: have fixed up flaw Mr Jonny 5 (couldn't resist) pointed out, updated query now filters identical matching like 1 VS 1 and reversed matching like 1 VS 5 and 5 VS 1, however you will still load up every combination in which case you would then format the returned data into an array like this

$matchings = array(id => array(team1,team2))

this way you can access teams like $matchings[0][0] for the first team and $matchings[0][1] for the second team, to get the random match, you can use array_rand or use rand(0,count($matchings)-1) to get a random id

edited Jan 3, 2014 at 0:46

answered Jan 2, 2014 at 23:50

Memor-X

2,9726 gold badges36 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jonny 5 Over a year ago

This would give you more then desired matches e.g. 1 vs 5 + 5 vs 1 and no random l <-> r

Memor-X Over a year ago

@Jonny5 thanks for pointing that out, i have fixed up the code

Jonny 5 · Accepted Answer · 2014-01-03 00:36:49Z

Check out, if this would do what you like and reload the page few times, to see random combinations.

// example array, set it with data from your db
// where key = (int)$row['team_key'] and value = $row['team_name']
$teams = array(
1 => "CHELSEA",
2 => "RED BULL",
3 => "MANCHESTER",
4 => "LIVERPOOL",
5 => "ARSENAL");

// generate random team-matches
// ------------------

$team_matches = array();
$team_keys = array_keys($teams);

shuffle($team_keys);

foreach($team_keys AS $v)
{
  foreach($team_keys AS $v2)
  {
    if(($v != $v2) && !in_array($v2."_".$v,$team_matches)) {
      $team_matches[] = $v."_".$v2;
    }
  }
}

shuffle($team_matches);

// test output
// ------------------

echo "<pre>"; 

// teams
echo "Teams: "; print_r($teams); echo "\n";

// helper
#echo "Matches: "; print_r($team_matches); echo "\n";

// print matches
echo "\nTeam matches:\n"; 
foreach($team_matches AS $v)
{
  $match_ids = array_map("intval",explode("_",$v));

  // opponents: $match_ids[0] vs $match_ids[1]
  echo $match_ids[0]." vs ".$match_ids[1]." --- ".$teams[$match_ids[0]]." vs ".$teams[$match_ids[1]]."<br />";
}

echo "</pre>";

Example output:

Teams: Array
(
    [1] => CHELSEA
    [2] => RED BULL
    [3] => MANCHESTER
    [4] => LIVERPOOL
    [5] => ARSENAL
)

Team matches:
3 vs 1 --- MANCHESTER vs CHELSEA
1 vs 5 --- CHELSEA vs ARSENAL
3 vs 5 --- MANCHESTER vs ARSENAL
1 vs 2 --- CHELSEA vs RED BULL
2 vs 5 --- RED BULL vs ARSENAL
3 vs 4 --- MANCHESTER vs LIVERPOOL
4 vs 5 --- LIVERPOOL vs ARSENAL
3 vs 2 --- MANCHESTER vs RED BULL
2 vs 4 --- RED BULL vs LIVERPOOL
1 vs 4 --- CHELSEA vs LIVERPOOL

Reload / Run script for shuffle teams again.

Tin Tran · Accepted Answer · 2014-01-03 06:17:27Z

please try this sqlFiddle

SELECT T1.id_team as team1ID,T1.name_team as team1Name,
       T2.id_team as team2ID,T2.name_team as team2Name,
       CONCAT(T1.id_team,' vs ',T2.id_team) as id_vs,
       CONCAT(T1.name_team,' vs ',T2.name_team) as name_vs
FROM team T1,team T2
WHERE T1.id_team < T2.id_team
ORDER BY rand()

You will get a 5 choose 2 (10) rows Each team will get a chance to play the other 4 teams.

if you wanted to have games where the first team represents a home game you can randomize that too using this sqlFiddle all I am doing is picking a random number and based on that random number mix up team1, team2

SELECT @rand := rand() as randValue,
       IF (@rand > 0.5,T1.id_team,T2.id_team) as team1ID,
       IF (@rand > 0.5,T1.name_team,T2.name_team) as team1Name,
       IF (@rand > 0.5,T2.id_team,T1.id_team) as team2ID,
       IF (@rand > 0.5,T2.name_team,T1.name_team) as team2Name,
       IF (@rand > 0.5,CONCAT(T1.id_team,' vs ',T2.id_team),
                       CONCAT(T2.id_team,' vs ',T1.id_team)) as id_vs,
       IF (@rand > 0.5,CONCAT(T1.name_team,' vs ',T2.name_team),
                       CONCAT(T2.name_team,' vs ',T1.name_team)) as name_vs
FROM team T1,team T2
WHERE T1.id_team < T2.id_team
ORDER BY rand()

if you wanted each team to have some games away, and some games at home (having the team one representing a home game) in an even fashion, like each team in this case will have 2 home games and 2 away games. you can try this sqlFiddle

SELECT @row := @row+1 as randValue,
       IF (MOD(@row,2)=0,T1.id_team,T2.id_team) as team1ID,
       IF (MOD(@row,2)=0,T1.name_team,T2.name_team) as team1Name,
       IF (MOD(@row,2)=0,T2.id_team,T1.id_team) as team2ID,
       IF (MOD(@row,2)=0,T2.name_team,T1.name_team) as team2Name,
       IF (MOD(@row,2)=0,CONCAT(T1.id_team,' vs ',T2.id_team),
                         CONCAT(T2.id_team,' vs ',T1.id_team)) as id_vs,
       IF (MOD(@row,2)=0,CONCAT(T1.name_team,' vs ',T2.name_team),
                         CONCAT(T2.name_team,' vs ',T1.name_team)) as name_vs
FROM team T1,team T2,(SELECT @row:=0)variable
WHERE T1.id_team < T2.id_team
ORDER BY rand()

Collectives™ on Stack Overflow

Mysql/php random

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related