Listing in indication system

Question

I’m having a hard time. I’m creating a PHP referral system and I’m having difficulty listing the clients and the amount of referrals he made. The system has two tables, the table 'clients' which is where the data of the same is next to the indication code generated automatically, and the table 'indicated' where the id of the client who has just registered and the code of the person who indicated it. Now I’m not sure which logica to use to make the listing of the customer who has more nominees.

I gave it a try, but it still didn’t work out so well.

<?php
                    $PDO = conectar();
                    $sql = "SELECT * FROM clientes,indicados WHERE cod_cli=codcli_indic ORDER BY id_cli ASC";
                    $stmt = $PDO->prepare($sql);
                    $stmt->execute();
                    while($resultado = $stmt->fetchAll(PDO::FETCH_ASSOC)){
                        foreach($resultado as $user){
                            $qtd = count($user['codcli_indic']);
                        echo "
                        <tr>
                        <td>".$user['id_cli']."</td>
                        <td>".$user['nome_cli']."</td>
                        <td>".$user['loja_cli']."</td>
                        <td>".$qtd."</td>
                    </tr>
                        ";}
                    }
                ?>

The result was so:

Mauricio indicated 2 people, so there in indicated should appear 2 and not appear twice Mauricio in the table.

Good people, I tried several ways and I arrived at a satisfactory result, I will leave here as it was the code, in case someone has the same difficulty.

<?php
                    $PDO = conectar();

                    $sql = "SELECT *, count(*) as count FROM clientes,indicados WHERE cod_cli=codcli_indic GROUP BY codcli_indic";

                    $stmt = $PDO->prepare($sql);
                    $stmt->execute();
                    $resultado = $stmt->fetchAll(PDO::FETCH_ASSOC);

                    //$qtd = count($resultado);


                   foreach($resultado as $user){
                       if ($user['cod_cli'] == $user['codcli_indic']){
                        $qtd = count($user['id_indic']);
                       }


                        echo "
                        <tr>
                        <td>".$user['id_cli']."</td>
                        <td>".$user['nome_cli']."</td>
                        <td>".$user['loja_cli']."</td>
                        <td>".$user['count']."</td>
                    </tr>
                        ";
                    }
                ?>

Answer 1

'Cause then, in your case, I think it would be good for you to wear a INNER JOIN, in case there is a positive result in your query... Which would be in the case so:

SELECT * 
FROM clientes t1
INNER JOIN indicados t2
ON t1.cod_cli = t2.codcli_indic
ORDER BY t1.id_cli ASC

Suggesting you also read an illustration of how the queries occur JOINS for greater understanding of such consultations! :)

---

The duplication of results is due to the fact of having a foreach within a while, where you do not have the slightest need, and your code can stay like this:

while($resultado = $stmt->fetchAll(PDO::FETCH_ASSOC)){
    $qtd = count($resultado['codcli_indic']);
    echo "
    <tr>
    <td>".$resultado['id_cli']."</td>
    <td>".$resultado['nome_cli']."</td>
    <td>".$resultado['loja_cli']."</td>
    <td>".$qtd."</td>
    </tr>
    ";
}