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 !

Answer 1

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()

Answer 2

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

Answer 3

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.