CODEWITHSUNDEEP
phpmysqlarrays
RDowns
RDowns

Reputation: 659

How do I structure an array in this desired format that is run inside a foreach() loop?

In the following scenario, $communityPlayerIds is an array of the Id's of people in a community, and $noPlayers is the count of that array.

e.g

$communityPlayerIds = [2,5,6] $noPlayers = 3

The following function should do the following:

Run an sql query for the number of times represented by $noPlayers, each time retrieving the desired data of a different $communityPlayerId.

At the moment this is creating one new array, players of 24 items, 8 for each player.

public function getCommunityForm($communityId, $noPlayers, $communityPlayersIds){

    $returnValue = array();
    $i = 0;
    foreach ($communityPlayersIds as $cPI){

        $sql = " SELECT player1_result, player1_name, date , results_id FROM `results` WHERE player1_id = '".$cPI."'  AND community_id = '".$communityId."'  UNION ALL SELECT player2_result, player2_name,date, results_id FROM `results` WHERE player2_id = '".$cPI."' AND community_id = '".$communityId."'  ORDER BY date DESC Limit 8";

        $result = $this->conn->query($sql);

        if (mysqli_num_rows($result) === 0) {
            $returnValue[] = ['status' => "nil"];
        }

        if($result != null && (mysqli_num_rows($result) >= 1)){
            while($row = $result -> fetch_array(MYSQLI_ASSOC)){
              if(!empty($row)){
                  $returnValue['players'][$i] = $row;
                 $i++;

                 }
            }
        }

    }
    return $returnValue;
}

What I want is to return a single array, that has within it 3 separate arrays, 1 for each query run.

How do I do this?

Upvotes: 0

Views: 50

Answers (2)

miken32
miken32

Reputation: 42691

First thing I did was simplify your database query (well, I at least made it more efficient) by getting rid of the UNION with the second query.

Next, I set up a prepared statement. This reduces all the overhead of repeated queries to the database. It's been a few years since I worked with mysqli but I believe it should be working, as long as your ID columns are all numbers. I would hope so, but your original code had quotes around them. If they're strings, change iiiii to sssss, and seriously reconsider your database schema (more on that below.)

You don't need a counter since you already have the player ID, just use that as the array index.

public function getCommunityForm($communityId, $noPlayers, $communityPlayersIds){
    $sql = " SELECT IF(player1_id=?, player1_result, player2_result) AS result, IF(player1_id=?, player1_name, player2_name) AS name, date, results_id FROM `results` WHERE (player1_id=? OR player2_id=?) AND community_id=? ORDER BY date DESC Limit 8";
    $stmt = $this->conn->prepare($sql);

    foreach ($communityPlayersIds as $cPI) {
        $stmt->bind_param("iiiii", $cPI, $cPI, $cPI, $cPI, $communityId);
        $stmt->execute();
        if ($result = $stmt->get_result()) {
            while($row = $result->fetch_array(MYSQLI_ASSOC)){
                 $returnValue['players'][$cPI][] = $row;
                 }
            }
        }
    }
    return $returnValue;
}

And for free, here's the PDO version. I'd strongly recommending looking into PDO. It's more modern and less verbose than mysqli. You'll notice no binding of parameters, we get to used named parameters, and getting an array out of it is much easier.

public function getCommunityForm($communityId, $noPlayers, $communityPlayersIds){
    $sql = " SELECT IF(player1_id=:pid, player1_result, player2_result) AS result, IF(player1_id=:pid, player1_name, player2_name) AS name, date, results_id FROM `results` WHERE (player1_id=:pid OR player2_id=:pid) AND community_id=:cid ORDER BY date DESC Limit 8";
    $stmt = $this->conn->prepare($sql);

    foreach ($communityPlayersIds as $cPI) {
        if ($stmt->execute([":pid"=>$cPI, ":cid"=>$communityID])) {
             $returnValue['players'][$cPI] = $stmt->fetchAll(PDO::FETCH_ASSOC);
        }
    }
    return $returnValue;
}

As for your database schema, you should not have a column for player names in your result table. How many times are names repeated in that table? What if a user wanted to change their name? You should instead have a player table, and then use a join to pull in their details.

Upvotes: 0

spencer7593
spencer7593

Reputation: 108450

Use two separate counters. Use the $i counter for the queries, and another counter for the rows of each query.

In our code, move the increment of $i to the end of the foreach loop, so it gets incremented only one time each pass through that outer loop.

 $i = 0
 foreach ($communityPlayersIds as $cPI){
    $sql = "...";
    // process each query
    $i++;
 }

Within the body of the foreach loop, when you process the rows returned by a query, use another counter for the rows. Initialize before the loop, and increment as the last step in the loop.

And add another dimension to your result array

       $rn = 0;
       while($row = $result->fetch_array(MYSQLI_ASSOC)){
          //
          $returnValue['players'][$i][$rn] = ... ;
          rn++;
       }

EDIT

As Paul Spiegel notes, the $rn counter isn't strictly necessary. An assignment to an array using empty square brackets will add a new element to an array.

       while($row = $result->fetch_array(MYSQLI_ASSOC)){
          //
          $returnValue['players'][$i][] = ... ;
       }

Upvotes: 1

Related Questions