PHP/MySQL: Better way to write SELECT query?

Is there a better way to wr开发者_高级运维ite this query?

startimg point: index.php?IDGruppo=25,48,47&IDFamiglia=845,587,215,444

now i explode IDGruppo and IDFamiglia and build query like:

there is a best way?

$sql = "SELECT * FROM catalogo_prodotti cp, catalogo_prodotti_attributi cpa WHERE cp.IDProdotto = cpa.IDProdotto ";

if($IDGruppo || $IDVarianti){

    $sql .= "AND ";

    foreach($idg as $v)
    {

    if($IDVarianti){
        $i = 1;
        foreach($idv as $c)
        {
        $sql .= "cp.IDGruppo = '". $v ."' AND cp.IDFamiglia = '".$c."' AND cp.Cancellato = '0' AND cp.Acquistabile = '1' ";

        if($IDTaglia)
         $sql .= "AND cpa.IDFiltro_Taglia = '".$IDTaglia."' ";

        if($IDTessuto)
         $sql .= "AND cp.Key_IT LIKE '%".$IDTessuto."%' ";

        if ( count($idv) != $i ){
         $sql .= "OR ";

         }
        $i++;
        }

    } else {

    $sql .= "cp.IDGruppo = '". $v ."' AND cp.Cancellato = '0' AND cp.Acquistabile = '1' ";

    }
     if ( count($idg) != $j )
     $sql .= "OR ";
     $j++;
    }

}

$sql .= "GROUP BY cp.IDProdotto LIMIT $offset, ".$this->ipp." ";

if($orderBY)
$sql .= "ORDER BY '".$orderBY."'";

---

SELECT * 
FROM catalogo_prodotti cp
JOIN catalogo_prodotti_attributi cpa ON cp.IDProdotto = cpa.IDProdotto 
 AND cp.Acquistabile = '1' AND AND cp.Cancellato = '0'
WHERE
cp.IDGruppo = '33' AND cp.IDFamiglia = '121' 
OR cp.IDGruppo = '33' AND cp.IDFamiglia = '123'  
OR cp.IDGruppo = '33' AND cp.IDFamiglia = '159'
OR cp.IDGruppo = '33' AND cp.IDFamiglia = '179' 
OR cp.IDGruppo = '30' AND cp.IDFamiglia = '121'
OR cp.IDGruppo = '30' AND cp.IDFamiglia = '123'
OR cp.IDGruppo = '30' AND cp.IDFamiglia = '159' 
OR cp.IDGruppo = '30' AND cp.IDFamiglia = '179'
OR cp.IDGruppo = '29' AND cp.IDFamiglia = '121'
OR cp.IDGruppo = '29' AND cp.IDFamiglia = '123' 
OR cp.IDGruppo = '29' AND cp.IDFamiglia = '159'
OR cp.IDGruppo = '29' AND cp.IDFamiglia = '179'

GROUP BY cp.IDProdotto LIMIT 0, 40

Or possibly:

EDIT - Added Brackets

SELECT * 
FROM catalogo_prodotti cp
JOIN catalogo_prodotti_attributi cpa ON cp.IDProdotto = cpa.IDProdotto 
    AND cp.Acquistabile = '1' AND AND cp.Cancellato = '0'
WHERE
(cp.IDGruppo = '33' AND cp.IDFamiglia IN ('121','123','159','179'))
OR (cp.IDGruppo = '30' AND cp.IDFamiglia IN ('121','123','159','179'))
OR (cp.IDGruppo = '29' AND cp.IDFamiglia IN ('121','123','159','179'))

GROUP BY cp.IDProdotto LIMIT 0, 40

---

This replaces a lot of your OR clauses with a couple of IN clauses.

SELECT * FROM catalogo_prodotti cp, catalogo_prodotti_attributi cpa 
WHERE cp.IDProdotto = cpa.IDProdotto 
AND cp.IDGruppo IN (29, 30, 33) 
AND cp.IDFamiglia IN (121, 123, 159, 179)
AND cp.Cancellato = '0' 
AND cp.Acquistabile = '1' 
GROUP BY cp.IDProdotto LIMIT 0, 40

Here's some other examples of using IN, and here's a link to the MySQL documentation for IN.

From the documentation:

• expr IN (value,...)
  Returns 1 if expr is equal to any of the values in the IN list, else returns 0.

---

Grouping the sub-statements with brackets will already help. For example cp.Cancellato appears 12 time in the same stamement instead of just once. Try building a logic tree for yourself and then write the statement.

---

Use some brackets, only use columns in your SELECT clause that appear in your GROUP BY (or aggregate functions), use formatting, and eliminate duplications from your WHERE clause.

SELECT cp.IDProdotto
FROM catalogo_prodotti cp, catalogo_prodotti_attributi cpa 
WHERE cp.IDProdotto = cpa.IDProdotto 
AND cp.Cancellato = '0' 
AND cp.Acquistabile = '1' 
AND (
     (cp.IDGruppo = '33' AND cp.IDFamiglia = '121')
  OR (cp.IDGruppo = '33' AND cp.IDFamiglia = '123')
  OR (cp.IDGruppo = '33' AND cp.IDFamiglia = '159')
  OR (cp.IDGruppo = '33' AND cp.IDFamiglia = '179')
  OR (cp.IDGruppo = '30' AND cp.IDFamiglia = '121')
  OR (cp.IDGruppo = '30' AND cp.IDFamiglia = '123')
  OR (cp.IDGruppo = '30' AND cp.IDFamiglia = '159')
  OR (cp.IDGruppo = '30' AND cp.IDFamiglia = '179')
  OR (cp.IDGruppo = '29' AND cp.IDFamiglia = '121')
  OR (cp.IDGruppo = '29' AND cp.IDFamiglia = '123')
  OR (cp.IDGruppo = '29' AND cp.IDFamiglia = '159')
  OR (cp.IDGruppo = '29' AND cp.IDFamiglia = '179')
)
GROUP BY cp.IDProdotto 
LIMIT 0, 40

---

SELECT * 
FROM catalogo_prodotti cp, catalogo_prodotti_attributi cpa 
WHERE cp.IDProdotto = cpa.IDProdotto
AND cp.Acquistabile = '1' 
AND cp.Cancellato = '0' 
AND cp.IDFamiglia IN('121','123','159','179')
AND cp.IDGruppo IN ('33','30','29')
GROUP BY cp.IDProdotto LIMIT 0, 40

---

The query below seems to match your intent, but you should reconsider what you are trying to achieve. MySQL lets you abuse the GROUP BY statement and return columns that are not being grouped or aggregated. This means that what particular values get returned for these columns is indeterminate and may come from any one of the matching rows. Is that what you want?

SELECT *  
FROM catalogo_prodotti cp 
INNER JOIN catalogo_prodotti_attributi cpa ON cp.IDProdotto = cpa.IDProdotto  
WHERE cp.Acquistabile = '1' 
    AND cp.Cancellato = '0' 
    AND cp.IDGruppo in ('29', '30', '33')
    AND cp.IDFamiglia in ('121', '123', '159', '179')
GROUP BY cp.IDProdotto 
LIMIT 0, 40