Turn database result into array

by

Okay, I’ve written PHP classes that extend the Zend Framework DB table, row, and rowset classes. I’ve been developing this anyway because I’m speaking at PHP Tek-X in a couple of weeks about hierarchical data models.

I don’t want to post all my code to Stack Overflow because they implicitly get licensed under Creative Commons if I do that. update: I committed my code to the Zend Framework extras incubator and my presentation is Models for Hierarchical Data with SQL and PHP at slideshare.

I’ll describe the solution in pseudocode. I’m using zoological taxonomy as test data, downloaded from ITIS.gov. The table is longnames:

CREATE TABLE `longnames` (
  `tsn` int(11) NOT NULL,
  `completename` varchar(164) NOT NULL,
  PRIMARY KEY (`tsn`),
  KEY `tsn` (`tsn`,`completename`)
)

I’ve created a closure table for the paths in the hierarchy of taxonomy:

CREATE TABLE `closure` (
  `a` int(11) NOT NULL DEFAULT '0',  -- ancestor
  `d` int(11) NOT NULL DEFAULT '0',  -- descendant
  `l` tinyint(3) unsigned NOT NULL,  -- levels between a and d
  PRIMARY KEY (`a`,`d`),
  CONSTRAINT `closure_ibfk_1` FOREIGN KEY (`a`) REFERENCES `longnames` (`tsn`),
  CONSTRAINT `closure_ibfk_2` FOREIGN KEY (`d`) REFERENCES `longnames` (`tsn`)
)

Given the primary key of one node, you can get all its descendants this way: 

SELECT d.*, p.a AS `_parent`
FROM longnames AS a
JOIN closure AS c ON (c.a = a.tsn)
JOIN longnames AS d ON (c.d = d.tsn)
LEFT OUTER JOIN closure AS p ON (p.d = d.tsn AND p.l = 1)
WHERE a.tsn = ? AND c.l <= ?
ORDER BY c.l;

The join to closure AS p is to include each node’s parent id.

The query makes pretty good use of indexes:

+----+-------------+-------+--------+---------------+---------+---------+----------+------+-----------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref      | rows | Extra                       |
+----+-------------+-------+--------+---------------+---------+---------+----------+------+-----------------------------+
|  1 | SIMPLE      | a     | const  | PRIMARY,tsn   | PRIMARY | 4       | const    |    1 | Using index; Using filesort |
|  1 | SIMPLE      | c     | ref    | PRIMARY,d     | PRIMARY | 4       | const    | 5346 | Using where                 |
|  1 | SIMPLE      | d     | eq_ref | PRIMARY,tsn   | PRIMARY | 4       | itis.c.d |    1 |                             |
|  1 | SIMPLE      | p     | ref    | d             | d       | 4       | itis.c.d |    3 |                             |
+----+-------------+-------+--------+---------------+---------+---------+----------+------+-----------------------------+

And given that I have 490,032 rows in longnames and 4,299,883 rows in closure, it runs in pretty good time:

+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| starting           | 0.000257 |
| Opening tables     | 0.000028 |
| System lock        | 0.000009 |
| Table lock         | 0.000013 |
| init               | 0.000048 |
| optimizing         | 0.000032 |
| statistics         | 0.000142 |
| preparing          | 0.000048 |
| executing          | 0.000008 |
| Sorting result     | 0.034102 |
| Sending data       | 0.001300 |
| end                | 0.000018 |
| query end          | 0.000005 |
| freeing items      | 0.012191 |
| logging slow query | 0.000008 |
| cleaning up        | 0.000007 |
+--------------------+----------+

Now I post-process the result of the SQL query above, sorting the rows into subsets according to the hierarchy (pseudocode):

while ($rowData = fetch()) {
  $row = new RowObject($rowData);
  $nodes[$row["tsn"]] = $row;
  if (array_key_exists($row["_parent"], $nodes)) {
    $nodes[$row["_parent"]]->addChildRow($row);
  } else {
    $top = $row;
  }
}
return $top;

I also define classes for Rows and Rowsets. A Rowset is basically an array of rows. A Row contains an associative array of row data, and also contains a Rowset for its children. The children Rowset for a leaf node is empty.

Rows and Rowsets also define methods called toArrayDeep() which dump their data content recursively as a plain array.

Then I can use the whole system together like this:

// Get an instance of the taxonomy table data gateway 
$tax = new Taxonomy();

// query tree starting at Rodentia (id 180130), to a depth of 2
$tree = $tax->fetchTree(180130, 2);

// dump out the array
var_export($tree->toArrayDeep());

The output is as follows:

array (
  'tsn' => '180130',
  'completename' => 'Rodentia',
  '_parent' => '179925',
  '_children' => 
  array (
    0 => 
    array (
      'tsn' => '584569',
      'completename' => 'Hystricognatha',
      '_parent' => '180130',
      '_children' => 
      array (
        0 => 
        array (
          'tsn' => '552299',
          'completename' => 'Hystricognathi',
          '_parent' => '584569',
        ),
      ),
    ),
    1 => 
    array (
      'tsn' => '180134',
      'completename' => 'Sciuromorpha',
      '_parent' => '180130',
      '_children' => 
      array (
        0 => 
        array (
          'tsn' => '180210',
          'completename' => 'Castoridae',
          '_parent' => '180134',
        ),
        1 => 
        array (
          'tsn' => '180135',
          'completename' => 'Sciuridae',
          '_parent' => '180134',
        ),
        2 => 
        array (
          'tsn' => '180131',
          'completename' => 'Aplodontiidae',
          '_parent' => '180134',
        ),
      ),
    ),
    2 => 
    array (
      'tsn' => '573166',
      'completename' => 'Anomaluromorpha',
      '_parent' => '180130',
      '_children' => 
      array (
        0 => 
        array (
          'tsn' => '573168',
          'completename' => 'Anomaluridae',
          '_parent' => '573166',
        ),
        1 => 
        array (
          'tsn' => '573169',
          'completename' => 'Pedetidae',
          '_parent' => '573166',
        ),
      ),
    ),
    3 => 
    array (
      'tsn' => '180273',
      'completename' => 'Myomorpha',
      '_parent' => '180130',
      '_children' => 
      array (
        0 => 
        array (
          'tsn' => '180399',
          'completename' => 'Dipodidae',
          '_parent' => '180273',
        ),
        1 => 
        array (
          'tsn' => '180360',
          'completename' => 'Muridae',
          '_parent' => '180273',
        ),
        2 => 
        array (
          'tsn' => '180231',
          'completename' => 'Heteromyidae',
          '_parent' => '180273',
        ),
        3 => 
        array (
          'tsn' => '180213',
          'completename' => 'Geomyidae',
          '_parent' => '180273',
        ),
        4 => 
        array (
          'tsn' => '584940',
          'completename' => 'Myoxidae',
          '_parent' => '180273',
        ),
      ),
    ),
    4 => 
    array (
      'tsn' => '573167',
      'completename' => 'Sciuravida',
      '_parent' => '180130',
      '_children' => 
      array (
        0 => 
        array (
          'tsn' => '573170',
          'completename' => 'Ctenodactylidae',
          '_parent' => '573167',
        ),
      ),
    ),
  ),
)

Re your comment about calculating depth — or really length of each path.

Assuming you’ve just inserted a new node to your table that holds the actual nodes (longnames in the example above), the id of the new node is returned by LAST_INSERT_ID() in MySQL or else you can get it somehow.

INSERT INTO Closure (a, d, l)
  SELECT a, LAST_INSERT_ID(), l+1 FROM Closure
  WHERE d = 5 -- the intended parent of your new node 
  UNION ALL SELECT LAST_INSERT_ID(), LAST_INSERT_ID(), 0;

More Related Contents:

  1. php / Mysql best tree structure
  2. Convert a series of parent-child relationships into a hierarchical tree?
  3. Achieve hierarchy, Parent/Child Relationship in an effective and easy way
  4. PHP / MySQL build tree menu
  5. Get all child, grandchild etc nodes under parent using php with mysql query results
  6. PHP PDO and Mysql [closed]
  7. Parse error: syntax error, unexpected T_STRING in C:\wamp\www\b_php\project\login_save.php on line 14 [closed]
  8. The requested URL was not found on this server.
  9. Fatal error: Call to a member function bind_param() on boolean [duplicate]
  10. strange character encoding of stored data , old script is showing them fine new one doesn’t
  11. How to loop through a mysql result set
  12. Doctrine 2 and Many-to-many link table with an extra field
  13. Post form and update multiple rows with mysql
  14. MySQL/PHP Error:[2002] Only one usage of each socket address (protocol/network address/port) is normally permitted
  15. How to use multiple databases dynamically for one model in CakePHP
  16. Easy way to export a SQL table without access to the server or phpMyADMIN
  17. How to know if MySQLnd is the active driver?
  18. using nulls in a mysqli prepared statement
  19. How can I count the numbers of rows that a MySQL query returned?
  20. MySQL: Add sequence column based on another field
  21. How to run the PHP script at scheduled time
  22. How to handle error for duplicate entries?
  23. How to handle PDO exceptions [duplicate]
  24. MySql PHP select count of distinct values from comma separated data (tags)
  25. mysql_insert_id() returns 0
  26. php artisan migrate throwing [PDO Exception] Could not find driver – Using Laravel
  27. Change name of Laravel’s created_at and updated_at
  28. How do I get database column names in Laravel?
  29. PDO and MySQL Fulltext searches
  30. How to avoid “Using temporary” in many-to-many queries?

Leave a Comment