Having difficulties with results from a query
Posted: Fri Nov 06, 2009 9:37 am
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:
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:
(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:
While with the user role 1, I get :
Can someone figure it out? All and any help on this topic will be much appreciated 
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;
}
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>";
}
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>
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>