CODEWITHSUNDEEP
phpmysqlsearchdirectorysubdirectory
Mokkun
Mokkun

Reputation: 726

MySQL how can I search for media in virtual folders

I'm trying to figure out how to make a search engine for my media database.

The structure for the virtual folders is:

table directories

id name parent

The structure for the medias is:

table medias

id name parent

I'm using the following php function to list all my directories

function print_menu($id = 0) {
    $sql = "SELECT directories.id AS id, directories.name AS name, directories.parent AS parent, directories.icon AS icon FROM directories 
    WHERE directories.parent = '".$id."' GROUP BY directories.id ORDER BY directories.name ASC";
    $req = $cnx->prepare($sql);
    $req->execute();
    if ($req->rowCount()>0) {
        echo "<ul>";
        while ($data=$req->fetch()) {
            echo "<li>".$data['name']."</li>";
            print_menu($data['id']);
        }
        echo "</ul>";
    }
 }

Now lets say I got the following folders

And I want to search for medias named "%media_1%" in "project 1" including all its subfolders, how can I do that without making a query for each folder and subfolders? The level of subfolders can vary.

Upvotes: 1

Views: 111

Answers (2)

Uueerdo
Uueerdo

Reputation: 15941

In general, with a "tree" table, to get a list of all descendants, and a known max depth you can construct a query like this:

SELECT layerN.ID
FROM theTable AS layer1
   INNER JOIN theTable AS layer2 
      ON layer1.id = layer2.parent_id 
      OR layer1.id = layer2.id
   INNER JOIN theTable AS layer3
      ON layer2.id = layer3.parent_id 
      OR layer2.id = layer3.id
   ...
   INNER JOIN theTable AS layerN
      ON `layerN-1`.id = layerN.parent_id 
      OR `layerN-1`.id = layerN.id
WHERE layer1.parent_id = [chosen_parent_node_id]
;

Without the additional "OR" clauses in the ON conditions, intermediate nodes would not be included. If intermediate nodes are not desired and the "leaf" nodes are of varying depths, the INNER JOINs will need to be LEFT JOINs, and the SELECT field will be a bit more complicated:

SELECT IFNULL(layerN.id, IFNULL(`layerN-1`.id, IFNULL(....))) AS leafID

Alternately

You could have an additional table for summarizing total ancestry, the simplest version would have two fields ancestor_id and descendant_id (though you could include a "distance" as well). The biggest problem would be that it would need to be maintained through code of some form, triggers on the tree table would probably be the most effective and reliable method.

Upvotes: 1

Mokkun
Mokkun

Reputation: 726

Ok so I found a solution, I'm using the function posted above to get the ids of all subdirectories for a given directory, lets say "project 1" has the ID 42 I'll use

$all_ids = array();
function print_menu($id = 0) {
    $sql = "SELECT directories.id AS id, directories.name AS name, directories.parent AS parent, directories.icon AS icon FROM directories 
    WHERE directories.parent = '".$id."' GROUP BY directories.id ORDER BY directories.name ASC";
    $req = $cnx->prepare($sql);
    $req->execute();
    if ($req->rowCount()>0) {
        while ($data=$req->fetch()) {
            $all_ids[] = $data['id'];
            print_menu($data['id']);
        }
    }
 }
all_ids[] = 42;
print_menu(42)

Then I'm just doing a request like

SELECT * FROM medias WHERE medias.parent IN (".implode(',', $all_ids).") AND medias.name LIKE '%media_1%'

The request will only search for media in "project 1" and its subdirectories, no matter how many levels

Upvotes: 0

Related Questions