Having difficulties with results from a query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
stebox
Forum Newbie
Posts: 2
Joined: Fri Nov 06, 2009 8:40 am

Having difficulties with results from a query

Post by stebox »

Hi,

I have been working for days on a CMS and have difficulty making a navigation with the results from a query. (the links are not added yet but I see no point to add it yet until it works...)

I have some relations with users and they have different rights, i.e. user/teacher/admin role = 1/2/3 in the db.
Based on this I am basically showing a user the content that he has registered for via the plan I made in my E/R diagram.
The admin has the full content of the relations to their disposal to CRUD as they wish.

The following is the problem I encounter, my navigation menus makes a query to the database to display the course content for a normal user, i.e. 1
so the query for getting their content is rather complicated:

Code: Select all

 
function fetch_course_content_for_nav($nav_id) {
        global $connection;
        if ($_SESSION['role'] == 1){
            $query = "SELECT course_id, nav_id, name, period, credits, room, date, grade ";
            $query .= "FROM courses c NATURAL JOIN registration r ";
            $query .= "WHERE r.user_id = {$_SESSION['user_id']} AND nav_id = {$nav_id}";
        } else if ($_SESSION['role'] == 2) {
            $query = "SELECT * ";
            $query .= "FROM courses ";
            $query .= "WHERE course_id IN ( ";
            $query .= "SELECT course_id ";
            $query .= "FROM teachers t NATURAL JOIN crs_taught_by ctb ";
            $query .= "WHERE t.teacher_id = {$_SESSION['user_id']})";
        } else {
            $query = "SELECT * "; 
            $query .= "FROM courses 
            WHERE nav_id = {$nav_id}";
        }
        $course_set = mysql_query($query, $connection);
        confirm_query($course_set);
        return $course_set;
    }
 
if which the result is for the role = 1:

course_id, nav_id, name, period, credits, room, date, grade Where: course id is the id of the courses the user is registered for, nav_id is 1 in the case of all courses, 2 in the case of events, 3 in the case of literature and 4 in the case of the teacher's details who teaches the course and the rest of the info is related to the respective courses.

and for the role = 3 (else clause)

course_id, nav_id, name, period, credits, room, id, menu_name, position, visible Where: the difference in my opinion is trivial.

The problem comes in when I try to display the results with:

Code: Select all

 
$nav_set = display_nav_subjects($public = false);
        while ($nav = mysql_fetch_array($nav_set)) {
            echo "<li>{$nav['menu_name']}</li>". $nav['id'];
            $course_set = fetch_course_content_for_nav($nav['id']);
            echo "<ul class=\"courses\">";
            while ($course = mysql_fetch_array($course_set)) {
                echo "<li>{$course['name']}</li>". $nav['id'];
            }
            echo "</ul>";
            echo $nav['id'];
            echo "<ul class=\"events\">";
            $events_set = fetch_all_events($nav['id']);
            while ($event = mysql_fetch_array($events_set)) {
                echo "<li>{$event['event_id']}</li>". $nav['id'];
            }
            echo "</ul>";
}
 
(Dont worry about the parsing, it works fine if I made some mistakes in the copying, also I intentionally left out some of the code but those who know what they are talking about can see how it will continue to get the html pages I present below.)
When I display the results for the admin user with the more generalized query i.e. else above, I get a navigation that looks like this:

Code: Select all

 
<html>
    <li>Courses</li>1
    <ul class="courses">
        <li>Course 1</li>1
        <li>Course 2</li>1
    </ul>1
    <ul class="events"></ul>
    <li>Events</li>2
    <ul class="courses"></ul>2
    <ul class="events">
        <li>2</li>2
        <li>1</li>2
        <li>3</li>2
        <li>4</li>2
    </ul>
    <li>Literature</li>3
    <ul class="courses"></ul>3
    <ul class="events"></ul>
    <li>Teachers</li>4
    <ul class="courses"></ul>4
    <ul class="events"></ul>
    </ul>
 
</html>
 
While with the user role 1, I get :

Code: Select all

 
<html>
    <li>Courses</li>1
    <ul class="courses">
        <li>Course 1</li>1
        <li>Course 2</li>1
    </ul>1
    <ul class="events">
        <li>1</li>1
        <li>2</li>1
    </ul>
    <li>Events</li>2
    <ul class="courses">
    </ul>2
    <ul class="events">
    <li>1</li>2
    <li>2</li>2
    </ul>
    <li>Literature</li>3
    <ul class="courses"></ul>3
    <ul class="events">
    <li>1</li>3
    <li>2</li>3
    </ul>
    <li>Teachers</li>4
    <ul class="courses"></ul>4
    <ul class="events">
    <li>1</li>4
    <li>2</li>4
    </ul>
    </ul>
</html>
 
Can someone figure it out? All and any help on this topic will be much appreciated :banghead:
stebox
Forum Newbie
Posts: 2
Joined: Fri Nov 06, 2009 8:40 am

Re: Having difficulties with results from a query

Post by stebox »

Man o Man I figured it out myself! (in a Hiro Nakamura voice) YATAAA!
Post Reply