Hierarchical query

rashid100

Hi,

 Can any body help me to solve this query. I have four following tables.

tbl_suggestions

s_id, us_id, pj_id, ch_id, desc
1, 2, 1, 1, this is first suggestion
2, 3, 1, 1, this is first suggestion
3, 2 , 1 , 2 , this is second suggestion
4, 2 , 2 , 1 , this is first suggestion

tbl_user

us_id, name
2, John
3, Tony

tbl_projects
pj_id, pj_title
1, English
2, History

tbl_chapters

ch_id, ch_title
1, chapter 1
2, chapter 2

I want to show my data in this format

JOHN(tbl_user)
ENGLISH (tbl_projects)
CHAPTER 1 (tbl_chapters)
this is first suggestion (tbl_suggestions)
CHAPTER 2 (tbl_chapters)
this is second suggestion (tbl_suggestions)

            [B]HISTORY[/B]
                [B]CHAPTER 1 (tbl_chapters)[/B]
                    this is first suggestion (tbl_suggestions)

TONY(tbl_user)
ENGLISH (tbl_projects)
CHAPTER 1 (tbl_chapters)
this is first suggestion (tbl_suggestions)

sneakyimp

Maybe something like this

// one query to get everything all at  once
$sql = "SELECT s.desc, c.ch_title, p.pj_title, u.name FROM tbl_suggestions s,
INNER JOIN tbl_chapters c ON c.ch_id=s.ch_id
INNER JOIN tbl_projects p ON p.pj_id=s.pj_id
INNER JOIN tbl_user u ON u.us_id=s.us_id
ORDER BY u.us_id, p.pj_id, c.ch_id, s.s_id ASC";

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

if ($result = $mysqli->query($sql)) {

// vars to remember the previously encountered value, initialized to NULL
$p_user = NULL;
$p_project = NULL;
$p_chapter = NULL;

while ($row = $result->fetch_assoc()) {
    // as you get each row, take care to note if the user, project, or chapter has changed
    if ($row["name"] != $p_user) {
        echo "<b>" . $row["name"] . "</b></br>";
    }
    if ($row["pj_title"] != $p_project) {
        echo "<b>" . $row["name"] . "</b></br>";
    }
    if ($row["ch_title"] != $p_chapter) {
        echo "<b>" . $row["name"] . "</b></br>";
    }

    // and output the suggestion(s)
    echo $row["desc"] . "</br>";

    // remember the previous user/project/chapter for the next iteration
    $p_user = $row["name"];
    $p_project = $row["pj_title"];
    $p_chapter = $row["ch_title"];
}

/* free result set */
$result->free();
}

/* close connection */
$mysqli->close();

rashid100

Thanks a lot Its works for me.