php - 使用mysql从特定用户生成带有php的html树

Question

我想根据下表生成一棵树:TBLPROJECTS Id Name mainprojectid(这是parentid，默认为0，除非它是另一个项目的一部分)

TBLUSERPROJECTS 用户项目 ID 用户 ID 项目 ID

TBLUSERS id 名称

我现在有以下代码:

require_once 'php/connectie.php';
    if (isset($_SESSION['klantid'])){
    $klantid = $_SESSION['klantid'];
    function categoryParentChildTree($parent = 0, $spacing = '', $category_tree_array = '') {
        global $connectie;
        global $klantid;
        $parent = $connectie->real_escape_string($parent);
        if (!is_array($category_tree_array))
            $category_tree_array = array();

        $sqlCategory = "SELECT id,name,mainprojectid FROM projects WHERE mainprojectid = $parent ORDER BY id ASC";
        $resCategory=$connectie->query($sqlCategory);

        if ($resCategory->num_rows > 0) {
            while($rowCategories = $resCategory->fetch_assoc()) {
                $category_tree_array[] = array("id" => $rowCategories['id'], "name" => $spacing . $rowCategories['name']);
                $category_tree_array = categoryParentChildTree($rowCategories['id'], '    '.$spacing . '- ', $category_tree_array);
            }
        }
        return $category_tree_array;
    }

    $categoryList = categoryParentChildTree();
    foreach($categoryList as $key => $value){
        $name= $value['name'];
        $id= $value['id'];
        echo ' 
            <form action="" method="post">
                <p> '.$name.' <button  value="'.$id.'" name="addproject"  type="submit">+</button>  <button value="'.$id.'" name="checkproject" type="submit">Tijdregistratie</button>  </p>
            </form>  <br> ';
    }}

这会生成所有项目的树，但不是来自一个特定用户。当我尝试这个 SQL 时:

SELECT id,name,mainprojectid FROM userproject JOIN projects ON projects.id = userproject.project_id WHERE user_id = 3 

它只是进入了一个无限循环...所以我现在有点卡住了。

Answer 1

尝试左连接

SELECT id,name,mainprojectid FROM userproject left JOIN projects ON   projects.id = userproject.project_id WHERE user_id = 3