Getting exact value from fetched array from MySql

Question

Not a duplicate of Select specific value from a fetched array

I have a MySql database as:

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?

Dhara Parmar · Accepted Answer · 2016-06-10 05:00:20Z

1

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

answered Jun 10, 2016 at 5:00

Dhara Parmar

8,1231 gold badge19 silver badges27 bronze badges

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

Comments

Saty · Accepted Answer · 2016-06-10 05:12:34Z

1

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
}

edited Jun 10, 2016 at 5:12

answered Jun 10, 2016 at 5:00

Saty

22.5k7 gold badges35 silver badges52 bronze badges

Comments

Niklesh Raut · Accepted Answer · 2016-06-10 05:12:42Z

0

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";

edited Jun 10, 2016 at 5:12

answered Jun 10, 2016 at 5:07

Niklesh Raut

34.9k17 gold badges82 silver badges112 bronze badges

Collectives™ on Stack Overflow

Getting exact value from fetched array from MySql

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related