php – Help with JOINS on 3 tables

Question:

I found a tutorial talking about JOINS and implemented it in the search for sales made.

But almost everything worked, except that when searching for the sales code, instead of bringing me only the result referring to the consulted code, it brings me the other code and repeated it several times. In addition to looping.

I'm publishing below the code used so that friends can take a look, and tell me where I'm wrong or if something is missing.

<div align="left" style=" padding:2px; width:315px; height:auto; float:left;">
<label>Entre com o código da Venda</label>
<form action="prod_consulta_venda.php" enctype="multipart/form-data" name="busca" method="post">
<input size="6" type="text" value="" name="buscar"/>
    <input type="submit" name="busca" value="Buscar Venda"/>
</form>
    </div>
</div>

<div align="left" style=" padding:2px; width:1000px; height:auto; border-top:solid 2px; float:left;">

<br />

<?php
include 'conexao.php';

$buscar = $_POST['buscar']; 

$sql_listar = mysql_query("SELECT VENDA.codvenda, VENDA.codcliente, VENDA.datavenda, VENDA.total, ITEMVENDA.codproduto, ITEMVENDA.quant, ITEMVENDA.preco, CLIENTE.nome, CLIENTE.endereco, CLIENTE.bairro, CLIENTE.cidade, CLIENTE.estado, CLIENTE.cep FROM VENDA, ITEMVENDA, CLIENTE WHERE VENDA.codcliente = ITEMVENDA.codcliente = CLIENTE.codcliente LIKE '%$buscar%'");

$total_registros = mysql_num_rows($sql_listar);

if(mysql_num_rows($sql_listar) <= 0){
    echo '<script type="text/javascript">
            alert("Desculpe! Nenhuma Venda foi encontrada com esse código!");
            window.location.href = "prod_consulta_venda.php";
            </script>';
    }else{

    while($res = mysql_fetch_array($sql_listar)){

    $conteudotabela1 .= '<tr style="color:#090;">

                <td align="center">'.$res['codvenda'].'</td>

                <td align="center">'.$res['codcliente'].'</td>

                <td align="center">'.$res['datavenda'].'</td>

                <td align="center">'.$res['total'].'</td>

                <td align="center">'.$res['codproduto'].'</td>

                <td align="center">'.$res['quant'].'</td>

                <td align="center">'.$res['preco'].'</td>

                <td align="center">'.$res['nome'].'</td>

                <td align="center">'.$res['endereco'].'</td>

                <td align="center">'.$res['bairro'].'</td>

                <td align="center">'.$res['cidade'].'</td>

                <td align="center">'.$res['estado'].'</td>

                <td align="center">'.$res['cep'].'</td>

            </tr>';
    }}

?>

<table class="tbllista" style="width: 15%">
    <thead>
        <tr align="center">
            <th style="width: 5%"></th>
            <th style="width: 5%">
        </tr>
    </thead>
</table>

<table class="tbllista" style="width: 100%">
    <thead>
        <tr align="center">
            <th align="center">Cód. Venda</th>
            <th align="center">Cód. Cliente</th>
            <th align="center">Data da Venda</th>
            <th align="center">Total da Venda</th>
            <th align="center">Cód. Produto</th>
            <th align="center">Quantidade</th>
            <th align="center">Preço Unitário</th>
            <th align="center">Nome</th>
            <th align="center">Endereço</th>
            <th align="center">Bairro</th>
            <th align="center">Cidade</th>
            <th align="center">Estado</th>
            <th align="center">Cep</th>
         </tr>
    </thead>
    <tbody>
            <?php echo $conteudotabela1; ?>

    </tbody>

Answer:

I solved the problem with the INNER JOIN, using WampServer 2.5's LocalHost it worked just fine, bringing the correct result regarding the sale requested in the search.

But when I do the FTP to the server, it doesn't work, it's looping directly.

I don't understand why it works on the local server and not on the Hosting.

I'm publishing all the code for the search page, so friends can analyze it and tell me where it's wrong or if something's missing to make it run right.

<?php
include 'conexao.php';
?>

<meta http-equiv="Content-Type" content="text/html; charset=windows-1252" />

<div align="center"  style="margin: 0 0 0 180px; max-width:1000px; width: 90%;">
<div align="left" style="margin:0 0 0 10px;"><h3>Consultar Venda</h3></div>

<div align="center" style=" padding:2px; width:655px; height:auto; float:left;">
    <div align="left" style=" padding:2px; width:315px; height:auto; float:left;">
        <label>Entre com o código da Venda</label>
            <form action="prod_consulta_venda.php" enctype="multipart/form-data" name="busca" method="post">
            <input size="6" type="text" value="" name="buscar"/>
            <input type="submit" name="busca" value="Buscar Venda"/>
        </form>
    </div>
</div>

<div align="left" style=" padding:2px; width:1000px; height:auto; border-top:solid 2px; float:left;">

<br />

<?php
    include 'conexao.php';

$buscar = $_POST['buscar']; 

$sql_listar = mysql_query("SELECT * FROM cliente
INNER JOIN venda
ON CLIENTE.codcliente = VENDA.codcliente
INNER JOIN itemvenda
ON CLIENTE.codcliente = ITEMVENDA.codcliente
WHERE VENDA.codvenda LIKE '%$buscar%'");

$total_registros = mysql_num_rows($sql_listar);

    if(mysql_num_rows($sql_listar) <= 0){
        echo '<script type="text/javascript">
            alert("Desculpe! Nenhuma Venda foi encontrada com esse código!");
            window.location.href = "prod_consulta_venda.php";
            </script>';

    while($res = mysql_fetch_array($sql_listar)){

        $conteudotabela1 .= '<tr style="color:#090;">

                <td align="center">'.$res['codvenda'].'</td>

                <td align="center">'.$res['codcliente'].'</td>

                <td align="center">'.$res['datavenda'].'</td>

                <td align="center">'.$res['total'].'</td>

                <td align="center">'.$res['codproduto'].'</td>

                <td align="center">'.$res['quant'].'</td>

                <td align="center">'.$res['preco'].'</td>

                <td align="center">'.$res['nome'].'</td>

                <td align="center">'.$res['endereco'].'</td>

                <td align="center">'.$res['bairro'].'</td>

                <td align="center">'.$res['cidade'].'</td>

                <td align="center">'.$res['estado'].'</td>

                <td align="center">'.$res['cep'].'</td>

            </tr>';
    }}
?>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252" />

<table class="tbllista" style="width: 15%">
    <thead>
        <tr align="center">
            <th style="width: 5%"></th>
            <th style="width: 5%">
        </tr>
    </thead>
</table>

<table class="tbllista" style="width: 100%">
    <thead>
        <tr align="center">
            <th align="center">Cód. Venda</th>
            <th align="center">Cód. Cliente</th>
            <th align="center">Data da Venda</th>
            <th align="center">Total da Venda</th>
            <th align="center">Cód. Produto</th>
            <th align="center">Quantidade</th>
            <th align="center">Preço Unitário</th>
            <th align="center">Nome</th>
            <th align="center">Endereço</th>
            <th align="center">Bairro</th>
            <th align="center">Cidade</th>
            <th align="center">Estado</th>
            <th align="center">Cep</th>
         </tr>
    </thead>
    <tbody>
            <?php echo $conteudotabela1; ?>
    </tbody>
</div>

I'm leaving the address below to check what's happening.

http://www.lccinformatica.com.br/prod_consulta_venda.php

If anyone can help me, I would be grateful.

Thanks for everyone's attention.

Scroll to Top