Northfield82 Northfield82 - 18 days ago 6
MySQL Question

Can I query this array in php to produce a result or should it have been done in MySQL?

In the array below I have a collection of data from a results table. This data shows which player has been defeated the most by the user, whether they were Player 1 or Player 2.

[[{"player2_id":"31","player2_name":"Jamie Charles"},
{"player2_id":"31","player2_name":"Jamie Charles"},
{"player2_id":"31","player2_name":"Jamie Charles"},
{"player2_id":"31","player2_name":"Jamie Charles"},
{"player2_id":"31","player2_name":"Jamie Charles"},
{"player1_id":"31","player1_name":"Jamie Charles"},
{"player1_id":"31","player1_name":"Jamie Charles"},
{"player1_id":"31","player1_name":"Jamie Charles"},
{"player1_id":"32","player1_name":"steve downs"},
{"player1_id":"31","player1_name":"Jamie Charles"}]]


As you can see
playerX_id:31
appears 9 times, 5 as player2_id, and 4 as player1_id.

(to get this table I had to run 2x queries and add both results to the table - one for when the user in question was player 1, and one for when the user was player 2)

Essentially all I want to do from this data is see what
playerX_id
appears the most and take the name that corresponds.

So my desired output would be
Jamie Charles


Can I do this from this array or is that something that should have been done at MySQL level?

This is my table:

+----------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+----------------+
| results_id | int(11) | NO | PRI | NULL | auto_increment |
| community_id | int(11) | NO | | NULL | |
| player1_id | int(11) | NO | | NULL | |
| player1_name | varchar(50) | NO | | NULL | |
| player1_team | varchar(50) | NO | | NULL | |
| player1_goals | int(11) | NO | | NULL | |
| player1_result | varchar(3) | NO | | NULL | |
| player2_goals | int(11) | NO | | NULL | |
| player2_result | varchar(3) | NO | | NULL | |
| player2_id | int(11) | NO | | NULL | |
| player2_name | varchar(50) | NO | | NULL | |
| player2_team | varchar(50) | NO | | NULL | |
| player1_pts | int(11) | NO | | NULL | |
| player2_pts | int(11) | NO | | NULL | |
| date | date | NO | | NULL | |
+----------------+-------------+------+-----+---------+----------------+


And this is my MySQL query:

public function getTotalMostWinsAgainst($playerId){

$returnValue = array();
$sql = "SELECT player2_id, player2_name FROM `results` WHERE player1_id = '".$playerId."' AND player1_result = 'W'";

$result = $this->conn->query($sql);
if($result != null && (mysqli_num_rows($result) >= 1)){
while($row = $result -> fetch_array(MYSQLI_ASSOC)){
if(!empty($row)){
$returnValue[] = $row;
}
}
}

$sql = "SELECT player1_id, player1_name FROM `results` WHERE player2_id = '".$playerId."' AND player2_result = 'W'";

$result = $this->conn->query($sql);
if($result != null && (mysqli_num_rows($result) >= 1)){
while($row = $result -> fetch_array(MYSQLI_ASSOC)){
if(!empty($row)){
$returnValue[] = $row;
}
}
}
return $returnValue;
}


}

Answer

Try:

select member, count(*) Total
from 
(
select player2_name as member
from results
WHERE player1_id = 2 AND player1_result = 'W'
union all
select player1_name
from results
WHERE player2_id = 2 AND player2_result = 'W'

) AS T

group by member
order by Total desc
Limit 1

This should give you the first result order by the name that appears most as player 2 when player 1 has won and vice-versa

Comments