数据库中的谱系/族谱图 [英] Pedigree/Family tree chart from database

问题描述

我正在尝试从数据库生成谱系（换句话说，家谱:) :)表...

I am trying to generate pedigree (in other words family tree :) ) table from database...

我的架构：

CREATE TABLE `horses` ( 
`horse_id` int(10) NOT NULL AUTO_INCREMENT,
`horse_name` varchar(32) NOT NULL,
`horse_sire` int(10) DEFAULT NULL,
`horse_dam` int(10) DEFAULT NULL, 
PRIMARY KEY (`horse_id`),
KEY `FKsire` (`horse_sire`),
KEY `FKdam` (`horse_dam`),
CONSTRAINT `FKdam` FOREIGN KEY (`horse_dam`) REFERENCES `horses` (`horse_id`),
CONSTRAINT `FKsire` FOREIGN KEY (`horse_sire`) REFERENCES `horses` (`horse_id`)
)

PS 'horse_dam'和'horse_sire'代表父母...
我花了几天时间研究这个问题，搜索，试验......
目前我有这个部分解决方案：

p.s. 'horse_dam' and 'horse_sire' represents parents... I have spent days on this problem, searching, experimenting... And currently I have this partly solution:

display_children($selected_horse_id, 5);

        function display_children($parent, $level) {
            mysql_connect('localhost','root','');
            mysql_select_db('hdb');
            $result = mysql_query('SELECT * FROM horses WHERE horse_id="'.$parent.'";');


            while ($row = mysql_fetch_array($result)) {

                echo "<tr><td>";
                echo $row['horse_name'];
                echo "</td></tr>";

                display_children($row['horse_dam'], $level+1);
                display_children($row['horse_sire'],$level+1);
            }
        }

在一行中生成表:(我想不到实现它的正确方法...
我想要的结果是谱系查询

which generates table in one row :( I cannot think of proper way to implement it... And the result I want is Pedigree Query

任何帮助或提示都非常感谢:)，
提前致谢

Any help or hint is highly appreciated :), Thanks in advance

推荐答案

几个月前我已经解决了问题...我发布了答案，所以这可能对其他开发人员有帮助...
ps该项目是用Yii 1.1编写的。

I have already resolved problem few month ago... I'm posting answer, so this might be helpful for other fellow developers... p.s. The project was written in Yii 1.1

首先，在我的 HorseController 分配的私有数组类型变量中，我要保留谱系：

Firstly, in my HorseController assigned private array type variable where I am going to keep Pedigree:

private $horses = array();

然后，我写了一个函数，它将从数据库中获取所有节点（父节点）：

Then, I wrote function that will get all nodes (parents) from database:

public function getParents($id)
{
    global $horses;
    $horses[] = Horses::model()->findByPk($id)->horse_id;
    if($this->loadModel($id)->horse_sire != null && $this->loadModel($id)->horse_dam != null)
    {
        $this->getParents($this->loadModel($id)->horse_sire);
        $this->getParents($this->loadModel($id)->horse_dam);
    }
    return $horses;
}

然后，我修改了 ActionView 功能，其中所有数据库中的节点将传递给View

Then, I modified ActionView function, where all nodes from database will be passed to View

public function actionView($id)
{
    $this->horses = $this->getParents($id);
    $this->render('view',array(
        'model'=>$this->loadModel($id),
        'parents'=>$this->horses,
    ));
}

最后一些UGLY CODE会显示血统（像这样谱系查询）:)

And finally a bit of UGLY CODE that will show pedigree (like this Pedigree Query) :)

<table>
            <?php
                $reverse_multiplier = (count($parents)+1)/2;
                $last_node_count = 0;
                for($i = 0; $i < count($parents); $i++)
                {
                    if($i == 0 && $last_node_count ==1)
                        echo "<tr>";

                    echo "<td rowspan='$reverse_multiplier'>";

                    echo "<a href=".Yii::app()->baseUrl."/index.php/horses/".Horses::model()->model()->findByPk($parents[$i])->horse_id." >";
                    echo Horses::model()->model()->findByPk($parents[$i])->horse_name;
                    echo "</a>";
                    echo "<br/>";
                    echo Horses::model()->model()->findByPk($parents[$i])->horse_yob;

                    echo "</td>";
                    if($reverse_multiplier == 1 || $reverse_multiplier == 0.5)
                        echo "</tr>";

                    if($reverse_multiplier == 0.5 && $last_node_count <= (count($parents)+1)/4)
                        $reverse_multiplier = (count($parents)+1)/8;
                    else
                        $reverse_multiplier = $reverse_multiplier/2;

                    if($last_node_count == (count($parents)+1)/4)
                    {
                        $reverse_multiplier = (count($parents)+1)/4;
                        $last_node_count=0;
                    }
                    if($reverse_multiplier == 0.5 || $reverse_multiplier == 1)
                        $last_node_count++;
                }
            ?>
        </table>

就是这样:)希望它有用......

And that's it :) Hope it was helpful...

这篇关于数据库中的谱系/族谱图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！