Help with INNER JOINS

Navigation
2

I'm having a problem with INNER JOIN.

When I do a search for a certain sale, it brings me all sales related to the customer code, since it is the field "coder" that associates the 03 tables (Customer, Sale and Item Sales), besides taking only a total value of sales.

I'm posting the access address of the page, so that friends can analyze and who knows what to do to get the result of just the searched search.

Sales that are registered with the same customer are 141, 146 and 147 all registered with the same customer.

link

Below I publish the code on the search page. 

    <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'])){
$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%'")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>

My question is how do I receive only the data of the sale that is being consulted regarding the code of the searched search, regardless of whether it is the same client or not.

I appreciate the attention of all, already wishing for Happy Holidays.

    
asked by anonymous 23.12.2015 / 11:59

1 answer

3

The correct modeling of your tables would be the customer code associated with the sale (which is the header) and each sales item (details) being associated with the sale, something like this: 

TABELA cliente (codcliente, nome, etc)
TABELA venda (codcliente, numvenda, data, etc)
TABELA itemvenda (numvenda, codproduto, quantidade, etc)

In this way the sql to look for the data would be adapted as follows (taken from your code) 

SELECT * FROM cliente
INNER JOIN venda
ON cliente.codcliente = venda.codcliente
INNER JOIN itemvenda
ON venda.numvenda = itemvenda.numvenda
WHERE venda.codvenda LIKE '%$buscar%'
    
23.12.2015 / 12:51
how to get the date of the server with JQuery My site gets messed up on Microsoft Edge, Firefox and Safari. How to solve this problem? [closed]