How can JOIN statements be utilized effectively in PHP to retrieve hierarchical data from a MSSql database?

To retrieve hierarchical data from a MSSql database using JOIN statements in PHP, you can use a recursive function to fetch parent-child relationships. By joining the table with itself and using aliases, you can retrieve data at different levels of the hierarchy. Additionally, you can use the "WITH RECURSIVE" clause in MSSql to simplify the recursive query. 

<?php
// Establish database connection
$serverName = "yourServerName";
$connectionOptions = array(
    "Database" => "yourDatabase",
    "Uid" => "yourUsername",
    "PWD" => "yourPassword"
);
$conn = sqlsrv_connect($serverName, $connectionOptions);

// Recursive function to retrieve hierarchical data
function fetchHierarchy($parentId, $level, $conn) {
    $sql = "WITH RECURSIVE CTE AS (
                SELECT id, name, parent_id, 1 AS level
                FROM yourTable
                WHERE id = $parentId
                UNION ALL
                SELECT t.id, t.name, t.parent_id, c.level + 1
                FROM yourTable t
                JOIN CTE c ON t.parent_id = c.id
            )
            SELECT id, name, parent_id, level
            FROM CTE";
    
    $stmt = sqlsrv_query($conn, $sql);
    while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
        echo str_repeat("-", $level) . $row['name'] . "<br>";
        fetchHierarchy($row['id'], $level + 1, $conn);
    }
}

// Call the function with the root parent id
fetchHierarchy(1, 0, $conn);

// Close the connection
sqlsrv_close($conn);
?>

Keywords

JOIN MSSql hierarchical data PHP database retrieval

Related Questions