Duplicate result in MySQL no while

Question

Duplicate result in MySQL no while

Navigation

#1 by (0 votes)

0

The table has two columns, one of user data and another of user points, user points works like the bank statement scheme, however I need to return only the last value, and I did so

SELECT 'usuario'.*, 'pontos'.'ponto_valor' FROM 'usuario' INNER JOIN 'pontos' ON 'pontos'.'ponto_id_user' = 'usuario'.'user_id' ORDER BY 'usuario'.'data_cad' DESC, 'pontos'.'data_cad' DESC;

And I did a WHILE with PHP for this:

<?php
$instrucaoSQL = "SELECT 
                'usuario'.*, 
                'pontos'.'ponto_valor' 
            FROM 
                'usuario' 
            INNER JOIN 
                'pontos' 
            ON 
                'pontos'.'ponto_id_user' = 'usuario'.'user_id' 
            ORDER BY 
                'usuario'.'data_cad' 
                    DESC,
                'pontos'.'data_cad'
                    DESC;";

$instrucaoQuery = mysqli_query($conn, $instrucaoSQL);

$contaQuery = mysqli_num_rows($instrucaoQuery);

if($contaQuery > 0) {

    while ( $r = mysqli_fetch_array($instrucaoQuery) ) {

        echo $r['user_nome'] . " - " . $r['ponto_valor'] . "<br>";

    }
?>

But the result is all doubled, I would like only the last value (row) of the 'points' table to be displayed, without duplicating the names of the' user 'table every time I find a related value in the' / p>

php mysql mysqli

asked by anonymous 16.01.2018 / 19:39

1 answer

Recover Json after success and use in PHP Insert comma automatically while typing

score 0 · Answer 1

I solved my SQL like this:

$instrucaoSQL = "
            SELECT 
                'usuario'.*,
                'pontos'.'ponto_valor'
            FROM 
                'usuario' 
            INNER JOIN
                'pontos'
            ON
                'pontos'.'ponto_id_user' = 'usuario'.'user_id'
               WHERE
                (
                SELECT 
                    'ponto_valor'
                ORDER BY
                    'pontos'.'data_cad'
                        DESC
                LIMIT 1
                )
             GROUP BY
                'usuario'.'user_nome'
                        ";