php - Selecting rows with same value from different columns -

php - Selecting rows with same value from different columns -

say had database storing info recent football game games friends. i'd store names of friends in each row in no particular order. how pull info out without having go through each row looking each value in every column?

here's illustration table:

gameid player1 player2 player3 ....... 1 john chance gordon 2 jim gordon john 3 derek chance richard

how select rows john played in based on if in column? i'm planning on putting them php array , counting appearances. efficient way of checking every columns without making like:

$playerarray=array{'derek','john','gordon','richard','jim','chance',....} foreach($playerarray $key => $value){ //is horribly inefficient? $sql = mysql_query(select * game_table player1='$value' or player2='$value' or player3='$value'...) }

the sql statement need following:

select * game_table concat_ws(',', player1, player2, player3) '%john%'

this query homecoming fields of rows john comes in: first 3 players' columns concatenated commas, , searched whether john in it.

if want count them:

select count(distinct gameid) numberofmatches game_table concat_ws(',', player1, player2, player3) '%,john,%' or concat_ws(',', player1, player2, player3) 'john,%' or concat_ws(',', player1, player2, player3) '%,john'

or if want list of players:

(select distinct player1 player game_table) union (select distinct player2 player game_table) union (select distinct player3 player game_table) order player

you combine previous, gives me want (i think):

select player,count(distinct gameid) numberofgames game_table t1 bring together ((select distinct player1 player game_table) union (select distinct player2 player game_table) union (select distinct player3 player game_table) order player) t2 t2.player=t1.player1 or t2.player=t1.player2 or t2.player=t1.player3 grouping t2.player

this returns table names of different players, including number of games played !

if want select person (john), following:

select player,count(distinct gameid) numberofgames game_table t1 bring together ((select distinct player1 player game_table) union (select distinct player2 player game_table) union (select distinct player3 player game_table) order player) t2 (t2.player=t1.player1 or t2.player=t1.player2 or t2.player=t1.player3) , t2.player='john' grouping t2.player

php mysql