CODEWITHSUNDEEP
phpmysql
Kragalon
Kragalon

Reputation: 450

Selecting rows with same value from different columns

Say I had a database storing data from recent football games with my friends. I'd store the names of my friends in each row in no particular order. How could I pull that data back out without having to go through each row looking for each value in every column?

Here's an example table:

gameID   player1     player2    player3   .......
1        John        Chance     Gordon
2        Jim         Gordon     John
3        Derek       Chance     Richard

How could I select the rows that John played in just based on if he was in any column? I'm planning on putting them into a PHP array and counting their appearances. What is the most efficient way of checking every columns without making something like: 

$playerarray=array{'Derek','John','Gordon','Richard','Jim','Chance',....}
foreach($playerarray as $key => $value){
    //Is this even horribly inefficient?
    $sql = mysql_query(SELECT * from game_table WHERE player1='$value' OR player2='$value' OR player3='$value'...)
}

Upvotes: 2

Views: 265

Answers (2)

DaveG
DaveG

Reputation: 753

The SQL statement that you need is the following:

SELECT * FROM game_table WHERE CONCAT_WS(',', player1, player2, player3) LIKE '%John%'

This query will return all fields of the rows where John comes in: first the 3 players' columns are concatenated with commas, and then searched whether John is in it.

If you would just want to count them:

SELECT COUNT(DISTINCT gameID) as numberofmatches FROM game_table WHERE CONCAT_WS(',', player1, player2, player3) LIKE '%,John,%' OR CONCAT_WS(',', player1, player2, player3) LIKE 'John,%' OR CONCAT_WS(',', player1, player2, player3) LIKE '%,John'

Or if you want a list of all the players:

(SELECT DISTINCT player1 AS player FROM game_table) UNION
(SELECT DISTINCT player2 AS player FROM game_table) UNION
(SELECT DISTINCT player3 AS player  FROM game_table)
ORDER BY player

You could also combine this with the previous, which gives me exactly what you want (I think):

SELECT player,count(distinct gameID) as numberofgames FROM game_table t1 JOIN ((SELECT DISTINCT player1 AS player FROM game_table) UNION (SELECT DISTINCT player2 AS player FROM game_table) UNION (SELECT DISTINCT player3 AS player  FROM game_table) ORDER BY player) t2 WHERE t2.player=t1.player1 OR t2.player=t1.player2 OR t2.player=t1.player3 GROUP by t2.player

This returns you a table with the names of all the different players, including the number of games they played !

If you want to select only a certain person (John), do the following:

SELECT player,count(distinct gameID) as numberofgames FROM game_table t1 JOIN ((SELECT DISTINCT player1 AS player FROM game_table) UNION (SELECT DISTINCT player2 AS player FROM game_table) UNION (SELECT DISTINCT player3 AS player  FROM game_table) ORDER BY player) t2 WHERE (t2.player=t1.player1 OR t2.player=t1.player2 OR t2.player=t1.player3) AND t2.player='John' GROUP by t2.player

Upvotes: 1

Allmighty
Allmighty

Reputation: 1519

You could use the 'IN' clause like:

$playerarray = array{'Derek','John','Gordon','Richard','Jim','Chance',....};
$sql = mysqli_query("SELECT * from game_table WHERE player1 IN (" . $value . ") OR player2 IN  (" . $value . ") OR player3 IN  (" . $value . ") OR player4 IN  (" . $value . ")");

Also, don't use mysql but mysqli functions as the first one is depricated.

Alternatively, or rather preferably, if there's alot of players (meaning: alot of columns) you would be better of changing the design of your database. Normalization should make things easier here.

Upvotes: 0

Related Questions