CODEWITHSUNDEEP
phpmysqli
wakey
wakey

Reputation: 2399

How to use a mysqli result row more than once in PHP?

I am trying to put together a tool to help me with my upcoming fantasy hockey draft while also learning PHP. I am trying to create multiple lists on a page, one that displays the top 10 available players overall and then others that display the top 10 available players by position.

Here is my SQL query/code

include 'db/connect.php';

$sql='SELECT * FROM players WHERE pick IS NULL';

$players=$conn->query($sql);

if($players === false) {
    trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);
} else {
    $rows_returned = $players->num_rows;
}

Then later in the page I have a while loop that generates a table with the top ten players

while ($row = $players->fetch_array()) {
    if ($i == 10) {
        break;
    }

    echo "<tr>";
    echo "<td>" . $row['Rank'] . "</td>";
    echo "<td>" . $row['Player'] . "</td>";
    echo "<td>" . $row['Team'] . "</td>";
    ...

And all that works fine. However, when I go to use the same method to generate a list containing only a certain position (C, RW/LW, etc...) it starts off where the top 10 player list ends. (See what I mean here: https://i.sstatic.net/9axZU.png)

I assume this has to do with the $players->fetch_array() however I do not know what the best way would be to get around it.

Any ideas? Thanks!

Upvotes: 1

Views: 960

Answers (2)

Your Common Sense
Your Common Sense

Reputation: 157828

Well, for the future visitors, lured by the misleading title, the other answer is okay.

While for you personally, the other answer, as well as your question, is wrong.

And it's your idea on using databases is wrong in the first place.

A database is not like a text file, which you but bound to read every time from first line to last. Databases are quite intelligent and intended to return you the very data you requested.

Think it this way: what if your league will grow up to employ thousands of players. It will burden PHP script with lots of useless info, when it needs only a hundred of players.

So, it seems you need different queries to get differen data sets. First, you need a query

SELECT * FROM players WHERE pick IS NULL ORDER BY field DESC LIMIT 10

To get overall top ten, where field is whatever field you're using to determine the "top" player. And then several queries, each getting players for the certain position.

SELECT * FROM players WHERE pick IS NULL AND position=? ORDER BY field DESC LIMIT 10

Upvotes: 0

Alice
Alice

Reputation: 701

Populate rows with all the players.

while ($row = $players->fetch_array()) {   //→ $rows = $players->fetch_all();
    $rows[] = $row;
}

You can use count() to get total amount of players in the array

$totalPlayers = count($rows);

Now you can loop through the array with for loop

for($i = 0; $i < $totalPlayers; $i++){
    //echo out the stuff you want
    echo $rows[$i]['player'];
}

Or only ten

for($i = 0; $i < 10; $i++){
    //echo out the stuff you want
    echo $rows[$i]['player'];
}

Upvotes: 6

Related Questions