小贝子编程

使用php从多个mysql表中提取数据显示4级层次结构



首先，我不是一个程序员，我玩php和mysql为我自己的娱乐，但在这一点上，我有一个问题，我不知道如何解决。

我试图在php中显示一个分类树，基本上是

属亚属物种亚种

我设法达到这一点，我使用截断的样本从数据库，以保持其可读性。

属:Agonum Bonelli, 1810种类:黄豆，1882年种类:黄豆草，1878年种类:Agonum tropicum Motschulsky, 1864种类:双色Agonum bicolor(德让，1828年)种:沙狸草(Herbst, 1784)物种:青豆(gonum piceolum, LeConte, 1879)种类:凤尾草，1838种类:Agonum canadense Goulet, 1969种类:conconconconconconconum (Gyllenhal, 1810)种:铜绿凤头草，1828年种类:仿射柯比，1837年种类:德让白蟹，1828年物种:Agonum anthracinum Dejean, 1831

或者如果有亚种

属:Apotomus Illiger, 1807种属:angusticollis G. mller, 1943种类:aptomus annulaticornis psamriguey, 1896亚种:Apotomus annulaticornis annulaticornis pringueyy, 1896亚种:Jeannel, aptomus annulaticornis perrieri 1946种:Apotomus atripennis Motschulsky, 1858种类:Castelnau, 1867年种类:沃拉斯顿Apotomus chaudoirii Wollaston, 1860种:石竹，1943亚种:耶德利卡，1961亚种:石竹(Apotomus clypeonitens)， 1943亚种:水蜜桃，1992种属:珍奈尔香足蝇，1946物种:Apotomus flavescens Apetz, 1854

问题是，在第一个样本中应该有一个亚属像这样炫耀

属:Agonum Bonelli, 1810种类:黄豆，1882年种类:黄豆草，1878年种类:Agonum tropicum Motschulsky, 1864亚属:Agonum (Agonum) Bonelli, 1810种类:双色Agonum bicolor(德让，1828年)种:沙狸草(Herbst, 1784)物种:青豆(gonum piceolum, LeConte, 1879)亚属:凤头草属(Europhilus) Chaudoir, 1859年种类:凤尾草，1838种类:Agonum canadense Goulet, 1969种类:conconconconconconconum (Gyllenhal, 1810)亚属:凤头草(Olisares) Motschulsky, 1865种:铜绿凤头草，1828年种类:仿射柯比，1837年种类:德让白蟹，1828年物种:Agonum anthracinum Dejean, 1831

我这辈子都搞不懂如何提取数据并将其插入到树中应该在的位置。

在数据库中，我为每个分类单元Genus, Subgenus, Species, Subspecies以及Genus, Family, Order等上面的所有分类单元都有一个表，它们都有一个ID，即genus_id，和一个名称genus_name，格式相同。

这是整个查询(减去数据库连接)

`$sql2 = "SELECT * FROM Genus ORDER BY genus_name";`
`$res2 = mysqli_query($conn, $sql2);`
while($g = mysqli_fetch_array($res2, MYSQLI_ASSOC))

{      
$genus_name = "".$g['genus_name']."";

echo "<b>Genus: $genus_name<br></b>";



$sql = "SELECT * FROM Species WHERE genus_name =  '$genus_name'";
$res = mysqli_query($conn, $sql);

while($d = mysqli_fetch_array($res, MYSQLI_ASSOC))

{
$kingdom_name = "".$d['kingdom_name']."";
$phylum_name = "".$d['phylum_name']."";
$subphylum_name = "".$d['subphylum_name']."";
$class_name = "".$d['class_name']."";
$subclass_name = "".$d['subclass_name']."";
$infraclass_name = "".$d['infraclass_name']."";
$superorder_name = "".$d['superorder_name']."";
$order_name = "".$d['order_name']."";
$suborder_name = "".$d['suborder_name']."";
$family_name   = "".$d['family_name']."";
$subfamily_name    = "".$d['subfamily_name']."";
$tribe_name = "".$d['tribe_name']."";
$genus_name = "".$d['genus_name']."";
$subgenus_name = "".$d['subgenus_name']."";
$species_name = "".$d['species_name']."";
$subspecies_name = "".$d['subspecies_name']."";



if($species_name != '')
{
echo "&nbsp;&nbsp;&nbsp;Species: $species_name<br>";
}
if($subspecies_name != '')
{
echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Subspecies: $subspecies_name<br>";
}
}

}

欢迎指教。

理想情况下，对于这类东西，我喜欢将问题抽象为数据结构。第二步是将数据结构转换为输出，这是一个单独的问题。对于这类数据，虽然可以使用数组，甚至不需要抽象出问题，但最好使用类似dom的树，以便始终可以获得节点的父节点。理想情况下，有一个节点类，它可以容纳同样是节点的子节点。虽然您可以使用PHP内置的DOM操作，但您也可以为此构建自己的类，因为它相对简单:

<?php
class TreeNode
{
protected $depth,$type,$text,$children,$parent;
public function __construct(string $type = 'root', string $text = '')
{
$this->type = $type;
$this->text = $text;
$this->depth = 0;
$this->children = [];
}
public function setParent(TreeNode $parent)
{
$this->parent = $parent;
}
public function addChildNode(TreeNode $child)
{
$child->setParent($this);
$child->setDepth($this->depth+1);
$this->children[] = $child;
return $child;
}
public function addChild(string $type, string $text)
{
return $this->addChildNode(new TreeNode($type,$text));
}
public function setDepth(int $depth)
{
$this->depth = $depth;
}
public function getDepth()
{
return $this->depth;
}
public function getText(){
return $this->text;
}
public function getType()
{
return $this->type;
}
public function getChildren()
{
return $this->children;
}
public function getParent()
{
return $this->parent;
}
public function nodeHtml()
{
if($this->type === 'root'){ return ''; }
return str_repeat('&nbsp;',$this->depth*4).$this->type.': '.htmlentities($this->text);
}
public function treeHtml()
{
$out = $this->nodeHTML();
if($this->type !== 'root'){ $out .= '<br>'; }
foreach($this->children as $child)
{
$out .= $child->treeHtml();
}
return $out;
}
}
$root = new TreeNode();
$pointer = $root->addChild('Genus', 'Agonum Bonelli, 1810');
$pointer->addChild('Species', 'Agonum patinale Bates, 1882');
$pointer->addChild('Species', 'Agonum scutiferum Bates, 1878');
$pointer = $pointer->addChild('Species', 'Agonum tropicum Motschulsky, 1864');
$pointer = $pointer->addChild('Subgenus','Agonum (Agonum) Bonelli, 1810');
$pointer->addChild('Species','Agonum bicolor (Dejean, 1828)');
$pointer->addChild('Species','Agonum muelleri (Herbst, 1784)');
$pointer->addChild('Species','Agonum piceolum (LeConte, 1879)');
$pointer = $pointer->getParent();
$pointer = $pointer->addChild('Subgenus','Agonum (Europhilus) Chaudoir, 1859');
$pointer->addChild('Species','Agonum anchomenoides Randall, 1838');
$pointer->addChild('Species','Agonum canadense Goulet, 1969');
$pointer->addChild('Species','Agonum consimile (Gyllenhal, 1810)');
$pointer = $pointer->getParent();
$pointer = $pointer->addChild('Subgenus','Agonum (Olisares) Motschulsky, 1865');
$pointer->addChild('Species','Agonum aeruginosum Dejean, 1828');
$pointer->addChild('Species','Agonum affine Kirby, 1837');
$pointer->addChild('Species','Agonum albicrus Dejean, 1828');
$pointer->addChild('Species','Agonum anthracinum Dejean, 1831');
echo $root->treeHtml();

它的输出将是:

Genus: Agonum Bonelli, 1810
Species: Agonum patinale Bates, 1882
Species: Agonum scutiferum Bates, 1878
Species: Agonum tropicum Motschulsky, 1864
Subgenus: Agonum (Agonum) Bonelli, 1810
Species: Agonum bicolor (Dejean, 1828)
Species: Agonum muelleri (Herbst, 1784)
Species: Agonum piceolum (LeConte, 1879)
Subgenus: Agonum (Europhilus) Chaudoir, 1859
Species: Agonum anchomenoides Randall, 1838
Species: Agonum canadense Goulet, 1969
Species: Agonum consimile (Gyllenhal, 1810)
Subgenus: Agonum (Olisares) Motschulsky, 1865
Species: Agonum aeruginosum Dejean, 1828
Species: Agonum affine Kirby, 1837
Species: Agonum albicrus Dejean, 1828
Species: Agonum anthracinum Dejean, 1831

它应该很容易修改以满足您的需要。基本上，循环应该是这样的:在任何循环之前将$指针设置为根节点。执行外部循环:

$pointer = $root->addChild('Genus', 'Agonum Bonelli, 1810');

如果有任何内部循环，指针将被设置为刚刚插入的子对象。一旦你完成了内部循环，作为外部循环的最后一行，你可以这样做:

$pointer = $pointer->getParent();

这个逻辑可以在任何嵌套中重复，当你需要在树中插入一个更高的节点时，你所需要的只是引用指针的父节点。您还可以存储对特定节点的引用，并且如果需要无序插入内容，将保留引用。

我相信你能把它移到你的循环中。

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium