Gabriel Goudarzi Gabriel Goudarzi - 5 months ago 12
PHP Question

Getting exact value from fetched array from MySql

Not a duplicate of Select specific value from a fetched array

I have a MySql database as:
enter image description here

Here's my query:

$sql = "SELECT * FROM data ORDER BY Score DESC";


I want it to be a leaderboard which people can update their scores so I can't use

$sql = "SELECT * FROM data ORDER BY Score DESC WHERE ID = 1";


I want to get Username of the second row in my query.So I wrote:

<?php
include "l_connection.php";
$sql = "SELECT * FROM data ORDER BY Score";
$result = mysqli_query($conn, $sql);
if($result->num_rows>0){
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){

}
echo "Result = '".$row[1]['Username']."''";
}

?>


But it returns Result = '' like there's nothing in the array.

But if I write

if($result->num_rows>0){
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
echo "Name = '".$row['Username']."''";
}
}


It will return : Parham, Mojtaba, Gomnam, Masoud,
So what am I doing wrong in the first snippet?

Answer

You can not access $row outside of while loop.

So store result in one new array, and then you can access that new array outside the while loop:

$newResult = array();
if($result->num_rows>0){
  while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
      $newResult[] = $row;
  }
}

echo "Result = '".$newResult[1]['Username']."''"; // thus you can access second name from array