Listing in display system

Question

Listing in display system

Navigation

#1 by (0 votes)

-2

I'm having a hard time. I'm creating a PHP referral system and I'm having a hard time listing clients and how many appointments they've made. The system has two tables, the table 'clients' which is where the data of the same is next to the code of indication of it automatically generated, and the table 'indicated' where it is the id of the client that just registered and the the code of the person who indicated it. Now I'm not sure what logic to use to make the listing of the client that has the most indicated.

Itried,butitstilldidnotwork.

<?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 looks like this:

Mauricioindicated2people,thenthereshouldappear2inMauritiusandnotappeartwiceMauriciointhetable.

Well,I'vetrieddifferentwaysandI'vegotasatisfactoryresult.I'llleavethecodehereifanyonehasthesamedifficulty.

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

php

asked by anonymous 04.12.2018 / 15:15

1 answer

Calculate percentage in real time Error Can not convert object of type

score 0 · Answer 1

So, in your case, I think it would be good for you to use a INNER JOIN , so there is a positive result in your query ... That would look like this:

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 the illustration of how JOINS queries for a greater understanding of such queries! :)

Duplicating results is because you have a foreach within a while , where you do not have the slightest need, your code might look like this: / p>

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