I have the table called modulos and inside it I include idModulo, idModuloBase, module, folder and datacriacao
When the idModuloBase is 0, it is the category, if it is > 0, is the completed module. I need to do a SQL that lists the categories and subcategories within each ...
Can anyone suggest me some SQL to do this? Thankful.
As there are only two levels, just a JOIN of the table with itself (a self join ):
SELECT
modulo.*,
principal.idModulo AS idModuloBase,
principal.modulo AS moduloBase,
principal.pasta AS pastaBase
FROM modulos AS modulo
INNER JOIN modulos AS principal
ON principal.idModulo = modulo.idModuloBase
I do not see a query that can solve the problem well, so I suggest doing it in PHP.
<?php
$sql = "SELECT * FROM modulos WHERE idMoludoBase = 0";
// Execute sua query
$modulos = Array();
while ($row = $res->fetch()) {
$sql = "SELECT * FROM modulos WHERE idMoludoBase = {$row['idModulo']}";
// Execute sua query
// Se ten submodulos
if ($sres){
// Adicione todos na chave submodulos
$row['submodulos'] = $sres->fetchAll();
}
// Adicona a linha ao array de modulos
$modulos[] = $row;
}
The code above is "just an algorithm", it should be adapted according to your database .
Output from variable $modulos :
$modulos => Array(
[
idModulo => 1
idModuloBase => 0
modulo => Cadastros
...
submodulos => Array(
[
idModulo => 2
idModuloBase => 1
modulo => Cedentes
...
],
[ ... ]
)
],
[ ... ]
)