問題描述

我正在嘗試從數據庫生成譜系(換句話說，家譜:))表...

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

附言'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

推薦答案

我幾個月前已經解決了問題...我正在發布答案，所以這可能對其他開發人員有所幫助...附言該項目是用 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,
    ));
}

最后是一些顯示系譜的丑陋代碼(例如系譜查詢):)

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

這篇關于來自數據庫的系譜/家譜圖的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！