Sql com resultado dinamico

Carlos Rocha · Agosto 20, 2010

Bom dia!

Como eu faria uma consulta sql que me retornasse, por exemplo,

Tenho 3 tabelas

fornecedores

produtos

produtopreco

Nessa última, o fornecedor loga no site e escolhe varios produtos e cadastra o preço que ele da ao produito.

tipo assim é gravado na tabela produtopreco:

id idProduto  idFornecedor preço
1   1            1          2.30
2   2            1          3.20       
3   1            2          2.35

Assim por diante. A consuilta tem que me retornar os ids dos produtos, a somatória dos preços dos produtos e uma vez o numero id do fornecedor. Isso é claro, de cada fornecedor. Tem como fazer uma consulta sql que retorne isso? A idéia é retrornar os dados da seguinte forma:

Ta aqui a estrutura das tabelas : Estou usando php

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE */;
/*!40101 SET SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES */;
/*!40103 SET SQL_NOTES='ON' */;

CREATE TABLE admin (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  nome varchar(200) NOT NULL DEFAULT '',
  Usuario varchar(35) NOT NULL DEFAULT '',
  Senha varchar(35) NOT NULL DEFAULT '',
  bloqueio char(1) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
CREATE TABLE cidades (
  id int(5) NOT NULL DEFAULT '0',
  estado char(2) NOT NULL DEFAULT '',
  nome varchar(200) NOT NULL DEFAULT '',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE contato (
  Id int(4) unsigned NOT NULL AUTO_INCREMENT,
  IdFornecedor int(4) NOT NULL DEFAULT '0',
  Email varchar(100) NOT NULL DEFAULT '',
  Titulo varchar(100) NOT NULL DEFAULT '',
  Texto text NOT NULL,
  PRIMARY KEY (Id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE fornecedores (
  id int(4) unsigned NOT NULL AUTO_INCREMENT,
  nome varchar(200) NOT NULL DEFAULT '',
  responsavel varchar(200) NOT NULL DEFAULT '',
  cpf varchar(11) NOT NULL DEFAULT '0',
  endereco varchar(200) NOT NULL DEFAULT '',
  numero varchar(20) NOT NULL DEFAULT '0',
  bairro varchar(100) NOT NULL DEFAULT '',
  estado char(2) NOT NULL DEFAULT '0',
  cidade int(6) NOT NULL DEFAULT '0',
  tel varchar(10) NOT NULL DEFAULT '0',
  cel varchar(10) DEFAULT '0',
  usuario varchar(6) NOT NULL DEFAULT '',
  senha varchar(6) NOT NULL DEFAULT '',
  data date NOT NULL DEFAULT '0000-00-00',
  bloqueio char(1) NOT NULL DEFAULT '',
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
CREATE TABLE produtopreco (
  id int(5) unsigned NOT NULL AUTO_INCREMENT,
  idProduto int(4) NOT NULL,
  idFornecedor int(4) NOT NULL,
  preço varchar(15) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
CREATE TABLE produtos (
  id int(5) unsigned NOT NULL AUTO_INCREMENT,
  nome varchar(150) NOT NULL DEFAULT '',
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

Estou usando a seguinte sql mas essa só me retorna apenas o primeiro produto cadastrado na tabela.

select 
                       distinct(pp.idFornecedor), 
                       f.nome,
                       sum(pp.preço) 
                    from produtopreco pp
                      inner join fornecedores f on f.id=pp.idFornecedor
                    where 
                      $produtoid
                    group by 
                       pp.idFornecedor
                    order by 
                       sum(pp.preço)

Editado Agosto 20, 2010 por Carlos Rocha

Carlos Rocha · Agosto 20, 2010

Ok, consegui assim: completim.

<?
 $produtos=mysql_query("
                    SELECT
                      id,
                      nome  
                    FROM 
                      produtos
                      ");
 if (mysql_num_rows($produtos)==0){print "Num deu!";}
 else {
?>
<form name="verpreco" method="post" action="?acao=verpreco">
<table width="500" border="1" align="center">
  <tr>
    <td colspan="10" align="center" valign="middle">Pesquisa de preços de fornecedores:</td>
  </tr>
  <tr><td colspan="10">Produto:</td></tr>
    <?
    $i=1;
    while (list($id, $nome) = mysql_fetch_row($produtos))
    {
            echo '<td><input type="checkbox" name="produtoid[]" value="'.$id.'" />'.$nome.'</td>';
            if( $i%10==0 ) {echo '</tr><tr>';}
    
            $i++;
    }

    ?>    
    
  <tr>
    <td colspan="10" align="center" valign="middle"><input type="submit" value="Pesquizar" /></td>
  </tr>
</table>
</form>
<?     }    ?>

-------------------------------------------------
<?
if($acao=="verpreco") {
    //aqui, verifico de já foi clikado na paginaçao e se a variavel iproduto(do link da paginação) veio. Se sim, decodifica, se não, prossegue o código.
    if     ($idproduto)
    {
        $produtoid=urldecode($idproduto);
        
    }
    else
    {
        foreach($_POST['produtoid'] AS $key => $value)
        { 
          $var[]="pp.idProduto=".$value." or";
        }
        $produtoid=implode(' ',$var); 
        $produtoid= substr($produtoid,0, -3);
    }
    //aqui,faço uma pesquisa pelos nomes dos produitos para exibir no cabeçalho da tabela
    $pesquisaprodutos=mysql_query("
                                   select 
                                     distinct(nome )
                                   from 
                                     produtos p 
                                   inner join 
                                      produtopreco pp on pp.idProduto=p.id
                                   where 
                                     $produtoid
                               ");
    while(list($nomesdeprodutos) = mysql_fetch_row($pesquisaprodutos))
    {
              $varnome[]=$nomesdeprodutos." e ";
    }
    $nomes=implode(' ',$varnome); 
    $produtonome= substr($nomes,0, -2);
    
    //aqui, começo a tabela
print "<table align='center' width=400 bgcolor='#0080C0'>";    
print "<tr                  ><td align=center colspan=3>Preços dos fornecedores para os produtos $produtonome</td></tr>";
//aqui começo a paginação.
$sql = mysql_query("
                    select 
                       distinct(pp.idFornecedor), 
                       f.nome,
                       sum(pp.preço) 
                    from produtopreco pp
                      inner join fornecedores f on f.id=pp.idFornecedor
                    where 
                      $produtoid
                    group by 
                       pp.idFornecedor
                    order by 
                       sum(pp.preço) 
                  ");
                   
$total = mysql_num_rows($sql); // Esta função irá retornar o total de linhas na tabela
$lpp = 10; // Especifique quantos resultados você quer por página
$paginas = ceil($total / $lpp); // Retorna o total de páginas
if(!isset($pagina)) { $pagina = 0; } // Especifica uma valor para variavel pagina caso a mesma não esteja setada
$inicio = $pagina * $lpp; // Retorna qual será a primeira linha a ser mostrada no MySQL
$sql = mysql_query("
                    select 
                       distinct(pp.idFornecedor), 
                       f.nome,
                       sum(pp.preço) 
                    from produtopreco pp
                      inner join fornecedores f on f.id=pp.idFornecedor
                    where 
                      $produtoid
                    group by 
                       pp.idFornecedor
                    order by 
                       sum(pp.preço) 
                    LIMIT $inicio, $lpp
                  ");
    if ($total == "0") {?><tr><td colspan=8 align="center" valign="middle" bgcolor="#FFFFFF"><center><font color=red>=>Não foi encontrado nenhum registro para a sua pesquisa</font></center></td></tr><?php }
    else {
print "<tr BGCOLOR='#CCCCCC'>";    
print "    <td align=center>Produto</td>          ";    
print "    <td align=center>Preço Total</td>          ";    
print "    <td align=center>Fornecedor</td>           ";
print "</tr>";               
while(list($idFornecedor, $nomefornecedor, $precototal)=mysql_fetch_row($sql)){
    $sql2=mysql_query("
                 select
                  p.nome, pp.preço
                 from
                  produtopreco pp
                 inner join produtos p on p.id=pp.idProduto
                 inner join fornecedores f on f.id=pp.idFornecedor
                 where pp.idFornecedor = $idFornecedor                   
                   ");

  print "<tr BGCOLOR='#FFFFFF'>";
  print "    <td align=center>";
  while (list($nomeproduto,$preço)=mysql_fetch_row($sql2))
  {
   print  $nomeproduto." - R$ ".formata_moeda($preço)."<br>";    
  }
  print "</td>";
  print "    <td align=center>R$ ".formata_moeda($precototal)." </td>        ";      
  print "    <td align=center>$nomefornecedor      </td>        ";
  print "</tr>";               
                                                                                }    
       }   
if($pagina > 0) {
   $menos = $pagina - 1;
   $url = "$PHP_SELF?acao=verpreco&pagina=$menos&idproduto=".urlencode($produtoid);
   echo "<a href=".$url.">Anterior</a>"; // Vai para a página anterior
}
for($i=0;$i<$paginas;$i++) { // Gera um loop com o link para as páginas
   $url = "$PHP_SELF?acao=verpreco&pagina=$i&idproduto=".urlencode($produtoid);
   echo " | <a href=".$url.">$i</a>";
}
if($pagina < ($paginas - 1)) {
   $mais = $pagina + 1;
   $url = "$PHP_SELF?acao=verpreco&pagina=$mais&idproduto=".urlencode($produtoid);
   echo " | <a href=".$url.">Próxima</a>";
}                                                                                                        
print "</table>";                                                                                   
}
?>

Carlos Rocha · Agosto 22, 2010

Ola pessoal.

Na tentativa de otimizar a consulta sql cheguei a uma conclusão e preciso tirar uma duvida:

Ví na internet ontem, que se acrescentasse um simulador ds tecla enter daria certo, testei mas não deu certo.

Onde será que esta o erro?

consigo chegar a esse resultado

produto                                        somatória dos preços dos produtos        nome do forneceddordoce 
2.35, bala 2.35                                            4.70                                               fornecedor 1

Será que tem como fazer uma quebra de linha entre um produto e outro. tipo assim:

produto                              somatória dos preços dos produtos                                 nome do fornecedor
doce 2.35, 
bala 2.35                                        4.70                               fornecedor 1

Dai precisa de um select que desse uma quebra de linha em um select (tipo o <br> do html.Mais ou menos do jeito abaixo: tipo assim:

SELECT 
  GROUP_CONCAT(pr.nome,pp.preço + char(13)) Produtos,
  sum(pp.preço) Preco_Total,
  fo.nome Fornecedor
FROM 
  fornecedores fo ,
  produtopreco pp,
  produtos pr
where 
  pp.idproduto = pr.id and pp.idfornecedor = fo.id
GROUP BY 
  fo.nome

usei o chr(13) mas não funciona (ou não estou implmentando da forma correta.

E aproveitando, tem como formatar (em sql) esses valores com R$ e duas casas depois da virgula em sql sem ter que usar php pra isso? Um abraço e obrigado pelo apoio.Carlos Rocha!

Editado Agosto 22, 2010 por Carlos Rocha

Entrar

Sql com resultado dinamico

Pergunta

Carlos Rocha

Link para o comentário

Compartilhar em outros sites

2 respostass a esta questão

Posts Recomendados

Carlos Rocha

Link para o comentário

Compartilhar em outros sites

Carlos Rocha

Link para o comentário

Compartilhar em outros sites

Participe da discussão

Estatísticas dos Fóruns

Navegação

Atividades