PDO recursive query

Question

I've found this similar question although I haven't been able to wrap my head around the chosen answer, or determine if it even applies to my situation.

I have the following tables for an image gallery:

CREATE TABLE image_categories (
  cat_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  parent_id INTEGER UNSIGNED DEFAULT NULL,
  title VARCHAR(100) NOT NULL,
  valid TINYINT(1) UNSIGNED NOT NULL DEFAULT 1,

  PRIMARY KEY(cat_id),
  FOREIGN KEY (parent_id)
    REFERENCES image_categories(cat_id)
);

CREATE TABLE image_gallery (
  img_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  cat_id INTEGER UNSIGNED NOT NULL,
  fname CHAR(45) NOT NULL,
  title VARCHAR(100) DEFAULT NULL,
  description VARCHAR(256) DEFAULT NULL,
  create_date DATETIME NOT NULL,
  valid TINYINT(1) UNSIGNED NOT NULL DEFAULT 1,

  PRIMARY KEY(img_id),
  FOREIGN KEY (cat_id)
    REFERENCES image_categories(cat_id)
);

Similar to a file tree directory, categories are like folders in that they can have children, and those children can have more children creating an infinite hierarchy.

Given a specific cat_id I can pull the images as follows:

$sql = "SELECT * FROM image_gallery WHERE cat_id = :cat_id AND valid = TRUE";
$sth = $this->db->prepare($sql);
$sth->execute(array(':cat_id' => $cat_id));

$images = array();
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
    $images[$row['img_id']]['cat_id'] = $row['cat_id'];
    $images[$row['img_id']]['fname'] = $row['fname'];
    $images[$row['img_id']]['title'] = $row['title'];
    $images[$row['img_id']]['description'] = $row['description'];
    $images[$row['img_id']]['create_date'] = $row['create_date'];
    $images[$row['img_id']]['valid'] = $row['valid'];
}

What I would like to do, however, is pull the images for the specified category in addition to any sub-categories until no more sub-categories are found.

How can I achieve those results? Either with a modified query or some type of recursive function?

Add a `parent_id` to each `cat_id`, then when you check a `cat_id` you can look for any "children" of that id by referencing their "parent." — Kirk Powell, Mar 31 '15 at 19:57
For this type of querying behavior, I don't believe your schema (adjacency list) will allow you to query the tree structure effectively (you would need to have a fixed depth that you are working with or recursively query until you understood the whole path in question). I think you need to take a nested set approach. See this excellent article for discussion - http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ Particularly note the "retrieving a single path" sections as this is in essence what you want to do to determine categories in play for querying images. — Mike Brant, Mar 31 '15 at 20:43

score 1 · Accepted Answer · edited May 23 '17 at 11:53

You must check the code, but something like this should let you show images from parent categories. First, save all the related category IDs into an array:

function get_cat_id($parent_id) {
    $sql = "SELECT cat_id FROM image_categories WHERE parent_id = :parent_id AND valid = TRUE";
    $sth = $this->db->prepare($sql);
    $sth->execute(array(':parent_id' => $parent_id));

    $category = $sth->fetch(PDO::FETCH_ASSOC);

    if ($sth->rowCount() > 0) {
        return $category['cat_id'];
    } else {
        return FALSE;
    }
}

$cat_ids = array();
$parent_id = $cat_id; /* This is the $cat_id you are using now... */
while ($cat_id = get_cat_id($parent_id)) {
    $cat_ids[] = $cat_id;
    $parent_id = $cat_id;
}

Then, modify your SQL query (you can see an example here PHP - Using PDO with IN clause array):

$in = str_repeat('?,', count($cat_ids) - 1) . '?'; 
$sql = "SELECT * FROM image_gallery WHERE cat_id IN ($in) AND valid = TRUE";
$sth = $this->db->prepare($sql);
$sth->execute($cat_ids);

$images = array();
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
    $images[$row['img_id']]['cat_id'] = $row['cat_id'];
    $images[$row['img_id']]['fname'] = $row['fname'];
    $images[$row['img_id']]['title'] = $row['title'];
    $images[$row['img_id']]['description'] = $row['description'];
    $images[$row['img_id']]['create_date'] = $row['create_date'];
    $images[$row['img_id']]['valid'] = $row['valid'];
}

EDIT: Sorry, my code only complains one subcategory. Just replace the function get_cat_id and the while loop with this code. I test it and I think it might work:

function get_subcategories($parent_id) {
    global $cat_ids;

    $sth = $this->db->prepare($sql);
    $sth->execute(array(':parent_id' => $parent_id));
    $categories = $sth->fetchAll(PDO::FETCH_ASSOC);

    foreach ($categories as $category) {
        $cat_ids[] = $category['cat_id'];
        get_subcategories($category['cat_id']);
    }   
}

 /* This is the $cat_id you are using now... */
$cat_ids = array($cat_id);
get_subcategories($cat_id);

The rest of the code (the IN clause) doesn't change.

I've been trying to get your example to work, however the `get_cat_id` function in your example doesn't take into account that a parent may have multiple children. After fixing that, the `while` loop approach no longer works. — mister martin, Apr 01 '15 at 18:38
I edited my answer including a new function that does the trick. — o1dskoo1, Apr 01 '15 at 21:39
Thanks, I came up with a solution based on your example, the main difference is I'm passing the `$cat_ids` by reference instead of declaring it global. It may not be the most elegant solution but it works! — mister martin, Apr 01 '15 at 21:42
I also think that pass the var by reference is so much better than declare it global. I'm glad it works. — o1dskoo1, Apr 01 '15 at 21:53

PDO recursive query

1 Answers1