Show higher value and name

0

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 as well.

    
asked by anonymous 11.12.2018 / 14:59

2 answers

5

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 inform the columns that you want to return separated by commas, and in MAX(pontos) you create an alias (in this case I put maior ).

Editing

A problem well observed by friend Anderson Carlos Woss with regard to versions starting with MySQL 5.7 and which is addressed in this response and in this .

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

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

DBFiddle Test

    
11.12.2018 / 15:24
2

Obsolete MySQL: do not use mysql functions * *

The MySQL library, which includes 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 more. 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 was already recommended to use MySQLi instead of MySQL.

However, many programmers continued using (and still use) the MySQL library.

To change this scenario, the PHP team took a rather drastic approach: it removed the MySQL library from PHP 7.

  

Alternatives to the MySQL extension

  • Use the MySQLi extension instead of MySQL.
  • Use the PDO extension (PHP Data Object).
  • Example using PDO

      

    works on 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;
    
        
    11.12.2018 / 16:26