php查询MySQL结果转化为数组_php – 将数据库结果转换为数组

好的,我编写了扩展Zend Framework DB表,行和行集类的PHP类.无论如何,我一直在开发这个,因为我在几个星期后在

PHP Tek-X讲关于分层数据模型.

我不想将我的所有代码发布到Stack Overflow,因为如果我这样做,他们会隐式获得Creative Commons的许可.更新：我将我的代码提交到Zend Framework extras incubator,我的演示文稿是Models for Hierarchical Data with SQL and PHP在slideshare.

我将用伪代码描述解决方案.我使用动物分类学作为测试数据,从ITIS.gov下载.该表是长名称：

CREATE TABLE `longnames` (

`tsn` int(11) NOT NULL,

`completename` varchar(164) NOT NULL,

PRIMARY KEY (`tsn`),

KEY `tsn` (`tsn`,`completename`)

)

我已经为分类法层次结构中的路径创建了一个闭包表：

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`)

)

给定一个节点的主键,您可以通过这种方式获取其所有后代：

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;

连接到闭包AS p是包括每个节点的父ID.

该查询很好地利用了索引：

+----+-------------+-------+--------+---------------+---------+---------+----------+------+-----------------------------+

| 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 | |

+----+-------------+-------+--------+---------------+---------+---------+----------+------+-----------------------------+

鉴于我在长名中有490,032行,在封闭中有4,299,883行,它运行得非常好：

+--------------------+----------+

| 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 |

+--------------------+----------+

现在我对上面的SQL查询的结果进行后处理,根据层次结构(伪代码)将行排序为子集：

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;

我还为行和行集定义了类. Rowset基本上是一个行数组. A Row包含行数据的关联数组,并且还包含其子项的Rowset.叶节点的子行Rowset为空.

行和行集还定义了名为toArrayDeep()的方法,这些方法以递归方式将其数据内容转储为普通数组.

然后我可以像这样一起使用整个系统：

// 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());

输出如下：

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',

),

),

),

),

)

重新评论有关计算深度 – 或每条路径的实际长度.

假设您刚刚在表中插入了一个包含实际节点的新节点(上例中的longname),新节点的id由MySQL中的LAST_INSERT_ID()返回,否则您可以以某种方式获取它.

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;