[PHP / MySQL] Resultset ordenen per categorie

[Verwijderd]

Ik heb 3 tabellen

• nodes: hierin staat alle content die aan de website is toegevoegd
• categories: definieer categorien
• files: koppelingstabel - koppel nodes aan een categorie

code:

nodes                    categories                files
---------------------    ----------------------    --------
nid title                cid module name           nid  cid
---------------------    ----------------------    --------
13  main_template.tpl    3   image  celebrities    13   5
25  mozilla.css          4   image  producten      25   5
26  forum_list.tpl       5   theme  tweakers_v1    26   8
27  jquery.js            8   theme  tweakers_ng    27   8
33  indextools.js                                  33   5
34  printing.css                                   34   5

Nu wil ik een resultset uit de database halen waarmee ik tel hoeveel files er per theme categorie zijn en de files opsommen. Met andere woorden, de resultset zou moeten zijn:

code:

Array (
  [5] => Array (
           name => tweakers_v1
           num_files => 4
           files => Array (
                      [0] => main_template.tpl
                      [1] => mozilla.css
                      [2] => indextools.js
                      [3] => printing.css
                    )
         )
  [8] => Array (
           name => tweakers_ng
           num_files => 2
           files => Array (
                      [0] => forum_list.tpl
                      [1] => jquery.js
                    )
         )
)

Ik ben nu heel onhandig aan het doen met PHP:

PHP:

  $result = db_query("
    SELECT c.cid, c.name, n.title, f.nid, f.extension 
    FROM {categories} c 
      LEFT JOIN {files} f ON c.cid = f.cid 
      LEFT JOIN {nodes} n ON f.nid = n.nid 
    WHERE c.module = 'theme'"
  );

  $themesets = array();
  while ($file = db_fetch_object($result)) {
    if (!array_key_exists($file->cid, $themesets)) {
      $themesets[$file->cid] = array( 
        'name' => $file->name,
        'num_files' => 1
        'files' => array($file->title)
      );
    }
    else {
      $themesets[$file->cid]['num_files']+= 1;
      $themesets[$file->cid]['files'][] = $file->title;
    }
  }

Maar het moet toch mogelijk zijn om een resultaat uit de database te krijgen waarmee ik makkelijker de $themesets array kan vullen? Wie kan mij helpen met de nodige query?

[Face_-_LeSS]

Volgens mij kan dat niet in één resultset (Niet eenvoudiger dat dat je nu doet in ieder geval).

[ Voor 38% gewijzigd door Face_-_LeSS op 10-12-2007 17:05 ]

[FragFrog]

Uit de losse pols:

SQL:

SELECT COUNT(`cid`),
  *
FROM `files`
JOIN `categories` 
  ON `files`.`cid` = `categories`.`cid`
JOIN `nodes`
  ON `files`. `nid` = `nodes`.`nid`
GROUP BY `categories`.`cid`

Als dat niet werkt kun je't altijd nog met een subquery doen tho'

Hou er wel rekening mee dat je resultset er altijd uit zal zien als [categorie] [filenaam] [aantal], resultsets met meer dan 2 dimensies bestaan voor zover ik weet niet in MySQL (en in dat opzicht heeft Face_-_LeSS wel weer gelijk)

[robbert]

Ik zou het zo doen:

PHP:

// alle categorieën in een hashmap gooien:
$qres = mysql_query("
  SELECT cid, name
  FROM categories
  WHERE module = 'theme'
");

$categories = array();
while($row = mysql_fetch_array($qres))
  $categories[$row["cid"]] = array("name" => $row["name"], "files" => array());

// bijbehorende nodes toevoegen
$qres = mysql_query("
  SELECT files.cid, nodes.title
  FROM files INNER JOIN nodes ON(files.nid = nodes.nid)
  WHERE cid IN(SELECT cid FROM categories WHERE module = 'theme')
");

while($row = mysql_fetch_array($qres))
  $categories[$row["cid"]] = $row["title"];

Die num_files opslaan lijkt me nogal zinloos, die is toch gelijk aan count($category["files"])

[ Voor 23% gewijzigd door robbert op 10-12-2007 18:47 ]

[Janoz]

@Robert: Lijkt me niet zo handig om voor elk record weer een query af te schieten op de database.

Zo heel onhandig vind ik trouwens de oplossing in de topicstart niet hoor. Wat je zou kunnen doen om hem minder onhandig te laten lijken is in de query een order by category toe te voegen. Je weet dan dat alle categorieën bij elkaar staan. Je kunt dan simpel in een variabele bij houden wat de categorie het de vorige record was. Zolang deze hetzelfde is als het huidige record dan ben je nog steeds met dezelfde bezig, en anders moet je een nieuwe categorie maken.

[robbert]

Janoz schreef op dinsdag 11 december 2007 @ 08:07:
@Robert: Lijkt me niet zo handig om voor elk record weer een query af te schieten op de database.

Huh, ik voer toch maar 2 queries uit....

[Janoz]

Oeps, sorry. Ik ging ten onrechte uit van een grotere while lus doordat ik geen } zag, maar er is ook helemaal geen {...

[Goodielover]

Verwijderd schreef op maandag 10 december 2007 @ 16:24:
Ik heb 3 tabellen

• nodes: hierin staat alle content die aan de website is toegevoegd
• categories: definieer categorien
• files: koppelingstabel - koppel nodes aan een categorie

code:

nodes                    categories                files
---------------------    ----------------------    --------
nid title                cid module name           nid  cid
---------------------    ----------------------    --------
13  main_template.tpl    3   image  celebrities    13   5
25  mozilla.css          4   image  producten      25   5
26  forum_list.tpl       5   theme  tweakers_v1    26   8
27  jquery.js            8   theme  tweakers_ng    27   8
33  indextools.js                                  33   5
34  printing.css                                   34   5

code:

select c.modulename
      ,count(nid)
from   categories c, files f
where  c.cid=f.cid
group by c.modulename

[Voutloos]

Face_-_LeSS schreef op maandag 10 december 2007 @ 17:02:
Volgens mij kan dat niet in één resultset (Niet eenvoudiger dat dat je nu doet in ieder geval).

Met group_concat kan alles. (uiteraard wel een separator kiezen welke niet in een filename voor kan komen).

PHP:

    $result = db_query("
        SELECT c.cid, c.name, COUNT(*) nfiles, GROUP_CONCAT(n.title SEPARATOR ';') filenames
        FROM `categories` c 
            LEFT JOIN `files` f ON c.cid = f.cid 
            LEFT JOIN `nodes` n ON f.nid = n.nid 
        WHERE c.module = 'theme'
        GROUP BY c.cid
        ORDER BY c.cid"
    );
    
    $themesets = array();
    while ($theme = db_fetch_object($result)) {
        $themesets[] = array(
            'name'      => $theme->name,
            'num_files' => theme->nfiles,
            'files'     => explode(';', theme->filenames),
        );
    }

Dit kan op zoveel manieren geimplementeerd worden, en group_concat is misschien een van de kortere opties, maar dat maakt het niet per se beter natuurlijk.

[ACM]

Ik zou gewoon de originele query en oplossing houden, met de andere oplossingen heb je meer queries, kans op tragere queries (filesorts bijv), of is je php-code minder duidelijk of trager. Ook een order by op categorie zou ik alleen toevoegen als het geen filesort oplevert.

[Voutloos]

Ik kon mij ook al vinden in de originele comment van Janoz. De query in de startpost is gewoon té makkelijk om helemaal te optimaliseren.

Het is gewoon leuk om af en toe group_concat van stal te halen, want het is niet echt de meest bekende functie. Maar het is dan ook niet de heilige graal.