Display higher value and name

Question

I have the following code:

    <?php
    $consulta = mysql_fetch_assoc(mysql_query('SELECT MAX(pontos) FROM usuarios'));
    echo  intval($consulta['MAX(pontos)']);
?>

However, it only displays the highest value, I wanted it to display the name of who owns it too.

Answer 1

Using mysqli:

$conn = new mysqli($servername, $username, $password, $base);
$consulta = mysqli_fetch_assoc(mysqli_query($conn, 'SELECT login, MAX(pontos) as maior FROM usuarios'));
echo intval($consulta['maior']); // imprime "125"
echo $consulta['login']; // imprime "teste2"

In SELECT you tell the columns you want to return separated by a comma, and in MAX(pontos) you create an alias (in case put maior).

Editing

A problem well noted by the friend Anderson Carlos Woss for versions starting with 5.7 of Mysql and which is addressed in this answer and in this.

If you are using a version 5.7 or higher, use the query:

SELECT login, pontos FROM usuarios
WHERE pontos = (SELECT MAX(pontos) FROM usuarios)

Testing on the Dbfiddle

Answer 2

Obsolete Mysql: Do not use mysql functions_*

The Mysql library, which has functions such as mysql_connect, mysql_query and the like, does not allow you to use new Mysql features such as triggers, stored procedures and others. These features are only available with the use of the Mysqli library.

The Mysqli extension has existed since PHP 5.0, which was released on July 13, 2004. Since then, it has been recommended to use Mysqli instead of Mysql.

However, many programmers continued to use (and still use) the Mysql library.

To change this scenario, the PHP team took a somewhat drastic attitude: it removed the Mysql library from PHP 7.

Alternatives to the Mysql extension

1. Use the Mysqli extension instead of Mysql.
2. Use PDO extension (PHP Data Object).

Example using PDO

works in any version of Mysql - test here

/******************** conexão *********************************************
$hostname="localhost";  
$username="USUARIO";  
$password="SENHA";  
$db = "Nome_BD";  
$pdo = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
**************************************************************************/

$sql= "SELECT login, pontos FROM usuarios order by pontos DESC limit 1"; 
$stmt = $pdo->prepare($sql);
$stmt->execute();
$obj = $stmt->fetchObject();
echo $obj->login;
echo $obj->pontos;