Help with JOINS in 3 tables

Navigation
3

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

But it did almost all right, but at the time of the search for the sales code, instead of bringing me only the result referring to the code consulted, it brings me the other code and repeated, and several times. In addition to looping.

I'm posting below the code used for friends to take a look, and tell me where I'm missing something. 

<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>
    
asked by anonymous 21.12.2015 / 03:17

2 answers

0

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

But when I FTP to the server, it does not work, it's looping right.

I do not understand why it works on the local server, not the hosting.

I'm posting the entire search page code, so friends can parse it and tell me where it's wrong or if something is 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 going on.

link

If someone can help me, I'll be grateful.

Thanks for the attention of everyone.

    
22.12.2015 / 02:15
0

Solved, I was forgetting this: 

if(isset($_POST['busca'])){

At the beginning of PHP, and the tables I was putting them in uppercase, and in BD they are in lowercase.

The working code looks like this: 

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

    if(isset($_POST['busca'])){ //Estava faltando esta linha

$buscar = $_POST['buscar']; 

//As tabelas estavam em maiúsculas, e no BD elas estão em minusculas.
$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%'")or die(mysql_error());

$total_registros = mysql_num_rows($sql_listar);

    if(mysql_num_rows($sql_listar) <= 0){

        echo "<meta http-equiv='refresh' content='0; URL= prod_consulta_venda.php'>
<script language='javascript'>
window.alert('Desculpe! Nenhuma Venda foi encontrada com esse código!');
</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>';
    }}}     
?>
<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>

Well, that's it, thank you for the attention of all and I hope to have contributed to help resolve future doubts regarding this case.

Happy Holidays, and I embrace you all.

    
22.12.2015 / 03:11
Rotate image keeping proportions Client-Server, Threads, client error when going online