PHP Loading Array with SQL not working

[ded]

The $points[] shows all zero's.

It is like the $worldcuppoints is not being loaded into the array properly.

 

 

 

$query = "SELECT DISTINCT playername FROM `tournamentresults` WHERE `date` >= '2012-01-01' ORDER BY `playername`";
$result = mysql_query($query,$dbh) or die(mysql_error());
$names = array();

while($row = mysql_fetch_array($result))
{
     $names[] = $row[0];
}


for($i = 0; $i < count($names); ++$i) 
{
     $playerid = $names[$i];
     $query2 = "SELECT playername, nationalpoints FROM `tournamentresults` WHERE `playername` = '$playerid' and `date` >= '2012-01-01' ORDER BY `nationalpoints` DESC";
     $result2 = mysql_query($query2,$dbh) or die(mysql_error());
     $count = 0;
     $worldcuppoints = 0;
     while($row2 = mysql_fetch_array($result2))
     {
          $counter++;
          if ($counter > 12);
          {
              break;
          }
          $worldcuppoints = $worldcuppoints + $row2['nationalpoints'];
     }
     $points[$i] = $worldcuppoints;
}

$c = array_combine($names,$points);
print_r($c);

 

 

 

This is the outcome

Array

(

[] => 0

[Aaron Flores] => 0

[Aaron Jameson] => 0

[Aaron Johnson] => 0

[Aaron Singleton] => 0

[Ace] => 0

[Adam Klein] => 0

[Adam Klineflter] => 0

[Adam Leger] => 0

 

 

 

Regards,

David

[Jessica]

The biggest problem here is you need to learn how to do a JOIN - then we can fix the rest of the code.

[ded]

I asked for help, not sarcasm.

[Jessica]

It wasn't sarcasm. You're doing a select query within a loop. You need to use a JOIN instead of running a query in a loop. And restructure the code once that's done.

[ded]

They are are the same table.

I am first gathering the DISTINCT names of all the players from the results table and loading into the names array

I am then running that array against the same to to get the first 12 results for each index of the names array

Adding the points up and populating the points array with the index ID as the name

I then want to merge or combine both arrays to provide the results.

[Jessica]

You can actually join a table to itself, but now that you've said what you're trying to do, the first part is useless. You can get rid of the first query and just do

$query2 = "SELECT playername, nationalpoints FROM `tournamentresults` WHERE  `date` >= '2012-01-01' ORDER BY `nationalpoints` DESC LIMIT 12";

Then loop through that to get the info.

 

edit: Actually you could do the whole action in one SQL statement. Because you want to limit it to the first 12 results it's a little harder. Are you able to export your data?

[ded]

That will not work.  That will grab the first 12 records of the table.  I need to grab the highest 12 points for each DISTINCT player.

[Jessica]

See my edit: It's possible to do it in one statement. If you ask on the SQL board, they'll tell you - never run queries in loops. It's just bad.

[MMDE]

I bet it's something along the lines of what I request here:

http://forums.phpfreaks.com/index.php?topic=353584

 

I had to do a subquery, I bet you will need to do it too, or normalize the database even further.

[xyph]

Couldn't you do something like

 

SELECT playername, SUM(nationalpoints) as wcpoints

FROM tournamentresults

WHERE date >= '2012-01-01'

GROUP BY playername

ORDER BY playername

 

?

 

Oh, I see you're limit it to the top 12. Still possible, gimme a sec.

 

http://www.artfulsoftware.com/infotree/queries.php#104

[Jessica]

Couldn't you do something like

 

SELECT playername, SUM(nationalpoints) as wcpoints

FROM tournamentresults

WHERE date >= '2012-01-01'

GROUP BY playername

ORDER BY playername

 

?

Needs the top 12 scores only to be summed.

 

 

I think this article might help.

http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

 

Other help:

http://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group

[xyph]

I think this article might help.

http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

 

That method has issues when dealing with identical scores.

[ded]

No....some players have 30 sets of points.  For World Cup Points, only the 12 highest points results per player counts.

 

I do IBMi programming and use arrays a lot.  I figured this should be very simple but this command is not working:  $points[$i] = $worldcuppoints;

If I change that to $points[$i] = $row2['nationalpoints'];  I actually get data in the $points[] but only the points from the last record read and not the sum of the top highest results for that player.

[xyph]

No....some players have 30 sets of points.  For World Cup Points, only the 12 highest points results per player counts.

 

I do IBMi programming and use arrays a lot.  I figured this should be very simple but this command is not working:  $points[$i] = $worldcuppoints;

If I change that to $points[$i] = $row2['nationalpoints'];  I actually get data in the $points[] but only the points from the last record read and not the sum of the top highest results for that player.

 

It IS simple, but we can't easily test your code without your data. For all we know your database could be returning 0.

[Jessica]

*shrug*.

 

I know it's POSSIBLE. I don't know how to do it.

Anyway the main idea I wanted to communicate is that you shouldn't be doing separate queries for each player. If doing it in one query is too much, then it's better to just go ahead and select all the scores for everyone, and do the math in PHP like you're trying to do.

$scores = array();
//SQL here to select all
while([...]){
   if(count($scores[$playerID]) < 12){
    $scores[$playerID][] = $score;
  }
}
$totals = array();
foreach($scores AS $player => $player_scores){
   $totals[$player] = array_sum($player_scores);
}

Not tested, just an example.

[ded]

Question:  When doing the sum(nationalpoints) with a LIMIT, the sum(nationalpoints) is the sum of all 50 records correct?

 

I would rather load into arrays, so I change the FOR loop to this

 

for($i = 0; $i < count($names); ++$i)

{

    $playerid = $names[$i];

    $query2 = "SELECT playername, sum(nationalpoints) FROM `tournamentresults` WHERE `playername` = '$playerid' and `date` >= '2012-01-01' ORDER BY `nationalpoints` DESC LIMIT 12";

    $result2 = mysql_query($query2,$dbh) or die(mysql_error());

    $count = 0;

    while($row2 = mysql_fetch_array($result2))

    {

          $points[$i] = $points[$i] + $row2['sum(nationalpoints)'];

    }

}

 

 

But it seems to give ALL records and not just the top 12

Array

(

[] => 10

[Aaron Flores] => 32

[Aaron Jameson] => 40

[Aaron Johnson] => 24

[xyph]

Edit - This code gets the sum of the top 3 scores for all IDs in the table.

 

SELECT tmp.playerid, SUM(tmp.score) as wcpoints
FROM ( 
  SELECT 
    playerid, score,
    IF( @prev <> playerid, @rownum := 1, @rownum := @rownum+1 ) AS rank, 
    @prev := playerid 
  FROM scores s 
  JOIN (SELECT @rownum := NULL, @prev := 0) AS r 
  ORDER BY s.playerid, s.score DESC
) AS tmp 
WHERE tmp.rank <= 3
GROUP BY tmp.playerid
ORDER BY tmp.playerid

 

Results

mysql> SELECT tmp.playerid, SUM(tmp.score) as wcpoints
    -> FROM (
    ->   SELECT
    ->     playerid, score,
    ->     IF( @prev <> playerid, @rownum := 1, @rownum := @rownum+1 ) AS rank,
    ->     @prev := playerid
    ->   FROM scores s
    ->   JOIN (SELECT @rownum := NULL, @prev := 0) AS r
    ->   ORDER BY s.playerid, s.score DESC
    -> ) AS tmp
    -> WHERE tmp.rank <= 3
    -> GROUP BY tmp.playerid
    -> ORDER BY tmp.playerid;
+----------+----------+
| playerid | wcpoints |
+----------+----------+
|        1 |       58 |
|        2 |       52 |
|        3 |       56 |
+----------+----------+
3 rows in set (0.00 sec)

 

Data used:

CREATE TABLE IF NOT EXISTS `scores` (
  `playerid` int(11) NOT NULL,
  `score` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `scores` (`playerid`, `score`) VALUES
(1, 15),
(1, 20),
(1, 20),
(1, 18),
(1, 15),
(2, 12),
(2, 22),
(2, 12),
(2, 18),
(2, 10),
(3, 20),
(3, 10),
(3, 18),
(3, 18),
(3, 10),
(3, 6);

[Jessica]

*Like*

[jazzman1]

pretty cool query buddy 

[Barand]

If you want a PHP solution, this is the equivalent of Xyph's query

 

$sql = "SELECT playerid , score FROM scores2
        ORDER BY playerid, score DESC";
$res = mysql_query($sql) or die(mysql_error());
$prev=null;
$scores = array();
while (list($id,$sc) = mysql_fetch_row($res)) {
    if ($prev != $id) {
        $scores[$id]=0;
        $k = 0;
        $prev = $id;
    }
    if ($k++ < 3) $scores[$id] += $sc;
    
}

 

Result

Array

(

    [1] => 58

    [2] => 52

    [3] => 56

)