CODEWITHSUNDEEP
phpmysql
Gabriel Goudarzi
Gabriel Goudarzi

Reputation: 77

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?

Upvotes: 0

Views: 312

Answers (3)

Niklesh Raut
Niklesh Raut

Reputation: 34914

You can assign row value to any array and use that array. 

<?php
    include "l_connection.php";
    $sql = "SELECT * FROM data ORDER BY Score";
    $result = mysqli_query($conn, $sql);
    if($result->num_rows>0){
        $rows = array();
        while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
            $rows[] = $row;
        }
        echo "Result = '".$rows[1]['Username']."'";
    }

?>

Or if you want only second highest score from column you can user limit as

$sql = "SELECT * FROM data ORDER BY Score limit 1,1";

Upvotes: 0

Saty
Saty

Reputation: 22532

Because you write where condition after ORDER by at

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

The sequence of query is

 SELECT * FROM data // select first
 WHERE ID = 1
 ORDER BY Score DESC// Order by at last

Check http://dev.mysql.com/doc/refman/5.7/en/select.html

And for second case you need to fetch Username inside while loop and use $row['Username'] instead $row[1]['Username'] 

while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
   echo "Result = '".$row['Username']."''";// write inside while loop
}

Upvotes: 1

Dhara Parmar
Dhara Parmar

Reputation: 8101

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

Upvotes: 1

Related Questions