CODEWITHSUNDEEP
phpmysql
user3162341
user3162341

Reputation: 251

show query results by category

I have a sql table "fournisseurs", for each line i have three column :

FRN_ID, FRN_RGN_ID, FRN_TYPE_ENTREE.

FRN_TYPE_ENTREE can be equal to "LES FONTAINES A VINS" , "LES BOUTEILLES" or "PRODUITS CONNEXE".

FRN_ID is the id of the producer.

FRN_RGN_ID is the id of the state. (Bordeaux, Alsace etc...).

I want to show something like this :

LES BOUTEILLES :

The ids of the states (FRN_RGN_ID)

LES FONTAINES A VIN :

The ids of the states (FRN_RGN_ID)

PRODUITS CONNEXES :

The ids of the states (FRN_RGN_ID)

For each line i have these columns (FRN_ID, FRN_RGN_ID, FRN_TYPE_ENTREE)

I want to do it in PHP.

CREATE TABLE IF NOT EXISTS `fournisseur` (
  `FRN_ID` int(11) NOT NULL AUTO_INCREMENT,
  `FRN_RGN_ID` text NOT NULL,
  `FRN_TYPE_ENTREE` text NOT NULL,
  PRIMARY KEY (`FRN_ID`)
...

Upvotes: 0

Views: 45

Answers (2)

Dysosmus
Dysosmus

Reputation: 832

In php :

$query = 'SELECT DISTINCT FRN_TYPE_ENTREE,
                          FRN_RGN_ID            
          FROM Fournisseurs ORDER BY FRN_TYPE_ENTREE';
// exec the query & cie
$prev_type = null;
foreach($results as $result) {
    if($prev_type != $result['FRN_TYPE_ENTREE']) {
       $prev_type = $result['FRN_TYPE_ENTREE'];
       echo $result['FRN_TYPE_ENTREE'];
    } 

    echo $result['FRN_RGN_ID'];
}

Or 

SELECT FRN_TYPE_ENTREE, GROUP_CONCAT(DISTINCT FRN_RGN_ID) AS 'frn_rgn_ids'
FROM Fournisseurs
GROUP BY FRN_TYPE_ENTREE

Upvotes: 2

digitai
digitai

Reputation: 1842

You can use CASE statements inside the query.

SELECT  CASE WHEN FRN_TYPE_ENTREE=`LES BOUTEILLES` then FRN_RGN_ID else NULL END AS 
`LES BOUTEILLES`, CASE WHEN FRN_TYPE_ENTREE=`LES FONTAINES A VINS` then FRN_RGN_ID 
else NULL END AS `LES FONTAINES A VINS`, CASE WHEN FRN_TYPE_ENTREE=`PRODUITS CONNEXES` 
then FRN_RGN_ID else NULL END AS `PRODUITS CONNEXES`   FROM  `fournisseur`

You`ll get a pivoted table or cross tabbed one for each or your categories:

LES BOUTEILLES  |  LES FONTAINES A VINS  |  PRODUITS CONNEXES
AUTRE              BORDEAUX
LOIRE              BORDEAUX
AUTRE              BORDEAUX  
LANGUEDOC ROUSSILLON
BORDEAUX           BORDEAUX

In PHP you can position each column as you want.

Upvotes: 1

Related Questions