Nested repeat region headache...:( Category and Sub-category problems.
4 tables...
Documents
Categories
Sub-Categories
CategorySubcategoryDocumentLinkTable
I want to display a list of categories that documents exist in, A-Z, but only if there is a document in any of those categories. Easy enough...
<?php
$rsCATS = new WA_MySQLi_RS("rsCATS",$csdbmysqli,0);
$rsCATS->setQuery("SELECT DGPSTdocuments.PSdocumentID, DGPSTdocuments.PSdocumentDELETE, DGPSTcatsublink.categoryID, DGPSTcatgeory.categoryTITLE FROM DGPSTdocuments INNER JOIN DGPSTcatsublink ON DGPSTdocuments.PSdocumentID = DGPSTcatsublink.documentID INNER JOIN DGPSTcatgeory ON DGPSTcatsublink.categoryID = DGPSTcatgeory.categoryID WHERE DGPSTdocuments.PSdocumentDELETE = 2 GROUP BY DGPSTcatgeory.categoryTITLE ASC");
$rsCATS->execute();?>
<?php
$wa_startindex = 0;
while(!$rsCATS->atEnd()) {
$wa_startindex = $rsCATS->Index;
?>
<?php echo($rsCATS->getColumnVal("categoryTITLE")); ?>
<?php
$rsCATS->moveNext();
}
$rsCATS->moveFirst(); //return RS to first record
unset($wa_startindex);
unset($wa_repeatcount);
?>
So, as a sub-menu under each category title, I'd like to display a list sub-categories that documents in that category exist in, again A-Z and again only if documents exist in any subcategory.
My understanding is that I must place the sub-category recordset and the sub-category repeat region code within the category repeat region however there are instances where a document won't have a sub-category so I assumed I'd have to use LEFT OUTER JOIN but have hit a brick wall with it.
My last attempt being what I thought was very close to the solution....
<?php
$rsCATS = new WA_MySQLi_RS("rsCATS",$csdbmysqli,0);
$rsCATS->setQuery("SELECT DGPSTdocuments.PSdocumentID, DGPSTdocuments.PSdocumentDELETE, DGPSTcatsublink.categoryID, DGPSTcatgeory.categoryTITLE FROM DGPSTdocuments INNER JOIN DGPSTcatsublink ON DGPSTdocuments.PSdocumentID = DGPSTcatsublink.documentID INNER JOIN DGPSTcatgeory ON DGPSTcatsublink.categoryID = DGPSTcatgeory.categoryID WHERE DGPSTdocuments.PSdocumentDELETE = 2 GROUP BY DGPSTcatgeory.categoryTITLE ASC");
$rsCATS->execute();?>
<?php
$wa_startindex = 0;
while(!$rsCATS->atEnd()) {
$wa_startindex = $rsCATS->Index;
?>
<?php echo($rsCATS->getColumnVal("categoryTITLE")); ?><br>
<?php
$rsSUBCATS = new WA_MySQLi_RS("rsSUBCATS",$csdbmysqli,0);
$rsSUBCATS->setQuery("SELECT DGPSTdocuments.PSdocumentID, DGPSTdocuments.PSdocumentDELETE, DGPSTcatsublink.categoryID, DGPSTcatsublink.subcategoryID, DGPSTsubcategory.subcategoryTITLE FROM DGPSTdocuments LEFT OUTER JOIN DGPSTcatsublink ON DGPSTdocuments.PSdocumentID = DGPSTcatsublink.documentID INNER JOIN DGPSTsubcategory ON DGPSTcatsublink.subcategoryID = DGPSTsubcategory.subcategoryID WHERE DGPSTcatsublink.categoryID = ? AND DGPSTdocuments.PSdocumentDELETE = 2 GROUP BY DGPSTsubcategory.subcategoryTITLE");
$rsSUBCATS->bindParam("i", "".($rsCATS->getColumnVal["categoryID"])."", "-1"); //WAQB_Param1
$rsSUBCATS->execute();
$wa_startindex = 0;
while(!$rsSUBCATS->atEnd()) {
$wa_startindex = $rsSUBCATS->Index;
?>
<?php echo($rsSUBCATS->getColumnVal("subcategoryTITLE")); ?><br> <?php
$rsSUBCATS->moveNext();
}
$rsSUBCATS->moveFirst(); //return RS to first record
unset($wa_startindex);
unset($wa_repeatcount);
?>
<?php
$rsCATS->moveNext();
}
$rsCATS->moveFirst(); //return RS to first record
unset($wa_startindex);
unset($wa_repeatcount);
?>
Am I making this more complicated than it needs to be?
Appreciate any advice offered. Thank you.
NJ
