CODEWITHSUNDEEP
phpmysqlarrays
Northfield82
Northfield82

Reputation: 77

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;
} 


}

Upvotes: 2

Views: 54

Answers (1)

RDowns
RDowns

Reputation: 659

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

Upvotes: 1

Related Questions