Help with JOINS in 3 tables

Asked

Viewed 74 times

3

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

But it was almost all right, only 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 around.

I am publishing below the code used so that friends can take a look, and tell me where I am missing 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>
php html mysql

  • Try using a Outer Join.

  • I’ll give it a try, Patrick, and if it works out, I’ll publish the results, okay? But I edited my problem with a solution on the local server, but this presenting me problem on the Hosting server. If you can give an analysis of why he keeps giving direct loop thank you. But I will try with OUTER JOIN, who knows can be this.

2 answers

0

I solved the problem with INNER JOIN, using the Wampserver Localhost 2.5 worked right, bringing the correct result regarding the sale requested in the search.

But when I do FTP to the server, it does not work, keeps giving direct loop.

I do not understand why it works on the local server and not on the Hosting.

I’m posting all the code on the search page, so friends can analyze it and tell me where it’s wrong or if there’s something 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 am leaving below the address to check what is happening.

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

If anyone can help me out, I’d be grateful.

Thank you for your attention.

0

Settled, I was forgetting that:

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

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

The code working was 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 guys, I appreciate everyone’s attention and I hope I’ve contributed, to help resolve future doubts regarding this case.

Happy holidays, and I embrace you all.

Browser other questions tagged php html mysql

You are not signed in. Login or sign up in order to post.