php fetch multiple rows from mysql

Question

I want to make a live top 6 users

I want to extract from mysql highest value of each row, and their username.

<?php  
    include_once 'config.php';  

    $query = $config -> prepare("
SELECT `UserID`
    , `Humanity`
    , `Headshots`
    , `Murders`
    , `BanditsKilled`
    , `ZombiesKilled`
    , `pAliveTime` 
 FROM `users` 
 ORDER 
      BY `UserID` ASC 
   LIMIT 10
 ");  
    if($query -> execute())  
    {  
        $query_results = $query->fetchAll();

    }  
?>

then in html

<div class="colw_3 spec-l border-right">
          <p></p><p><strong><em><font color="white">Humanity</font></em></strong><br>
          </p>
          <p><strong><em><font color="white">Headshots:</font></em></strong><br>
          </p>
          <p><strong><em><font color="white">Bandits Killed:</font></em></strong><br>
          </p>
          <p><strong><em><font color="white">Murders</font></em></strong><br>
          </p>
          <p><strong><em><font color="white">Zombies Killed</font></em></strong><br>
          </p>
          <p><em><strong><font color="white">Alive Time:</font></strong></em><br>
          </p>
        </div>
        <!-- END col_6 -->

        <div class="colw_3 paddbott100 spec-r">
          <p></p><p><strong><font color="white"> <?php echo $query_result["Humanity"]; ?></font></strong><br>
          </p>
          <p><strong><font color="white"> <?php echo $query_result["Headshots"]; ?></font></strong><br>
          </p>
          <p><strong><font color="white"> <?php echo $query_result["BanditsKilled"]; ?></font></strong><br>
          </p>
          <p><strong><font color="white"> <?php echo $query_result["Murders"]; ?></font></strong><br>
          </p>
          <p><strong><font color="white"> <?php echo $query_result["ZombiesKilled"]; ?></font></strong><br>
          </p>
          <p><strong><font color="white"> <?php echo $query_result["pAliveTime"]; ?></font></strong><br>
          </p>
        </div>

but I don't know how should I write the query so I get the values and the username of those values

it should look like this

Humanity: 5000 - Username's value. (without 's value) Bandits Killed: 4 - Username's value (another username, or the same username, depends who has the highest value on BanditsKilled column)

Currently my code orders ascending by userID..

You're probably looking to either add a WHERE clause to your query to limit it to a specific user ID, or to loop over $query_results with a foreach loop. Also, please note that the <font> tag is not supported in HTML5. Use CSS for styling instead :) — Obsidian Age
– Obsidian Age, Commented Mar 18, 2018 at 21:32
And how should code look like if I add WHERE clause? Because I can't imagine it, what condition I need to put to where? — please help
– please help, Commented Mar 18, 2018 at 21:45
I want to get the biggest value of each columns and who has it (Ex John has 4, Henry has 5. I want to get 5 and Henry) — please help
– please help, Commented Mar 18, 2018 at 22:00

Syscall · Accepted Answer · 2018-03-18 23:02:56Z

1

You could make a loop over the columns and execute a query ordered by the column, limited to one row:

$top_users = [];
$columns = ['Humanity', 'Headshots', 'Murders', 'BanditsKilled', 'ZombiesKilled', 'pAliveTime'];
foreach ($columns as $column) {
    $query = $config->prepare("SELECT UserID, UserName, $column as num FROM users ORDER BY $column DESC LIMIT 1");
    if($query->execute()) {
        $top_users[$column] = $query->fetch();
    }
}

print_r($top_users);

Possible (theoretical) output:

Array(
    [Humanity]      => Array([UserId] => 21, [UserName] => foo1, [num] => 5)
    [Headshots]     => Array([UserId] => 22, [UserName] => foo2, [num] => 4)
    [Murders]       => Array([UserId] => 23, [UserName] => foo3, [num] => 8)
    [BanditsKilled] => Array([UserId] => 24, [UserName] => foo4, [num] => 7)
    [ZombiesKilled] => Array([UserId] => 25, [UserName] => foo5, [num] => 5)
    [pAliveTime]    => Array([UserId] => 26, [UserName] => foo6, [num] => 2)
)

So you can get values:

foreach ($top_users as $column => $data) {
    echo $data['UserName'] . ' : ' . $data['num'] ;
}

edited Mar 18, 2018 at 23:02

answered Mar 18, 2018 at 22:05

Syscall

19.8k10 gold badges44 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

please help Over a year ago

and at HTML section what should i type. print_r($top_users); ? I've post you an image to see what I want to do exactly i.imgur.com/8hjoYa3.png. There are many users, Bob, Bill, Johnson, each has the same or different values, but John has the biggest on Headhsots and Mark on the Bandits Killed.

Syscall Over a year ago

No, you should use print_r(), it's just to see your values. You have to use foreach() as pointed out at the end of answer. The data in $top_users should have to data you want: here, username "foo1" have the greatest value for "Humanity", and so on.

please help Over a year ago

Errors: Trying to get property of non-object in

please help Over a year ago

Wrong error, it was before edit. When I put the array into the php, it gives me syntax error, unexpected 'Object' (T_STRING), expecting ')

Syscall Over a year ago

It seems you are printing also the keys. I don't know how you implements your echo inside the loop. Try also to use fetch(PDO::FETCH_ASSOC) to remove the unnecessary keys.

|

Collectives™ on Stack Overflow

php fetch multiple rows from mysql

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related