Hello everyone.

I have 4 tables whose Structures are provided with my question as snapshots

Table 1--> Students
Table 2--> Courses
Table 3--> Student_Courses
Table 4--> Users

I want 3 columns in the output which are.

1- Course
2- Classes Attempt
3- Classes Missed

Now :
--Only those courses should be shown which are associated with the login user.
--Classes Attempt & Classes Missed should be filtered out according to the courses associated with the user.

I am trying the following query

SELECT c.course_name, sc.st_classes_attempt, sc.st_classes_missed, st.stdnt_name
FROM courses c, student_attendance sc, students st
WHERE c.course_id = sc.c_id AND st.stdnt_rfid_tag = sc.st_id;

Can some one help me out?
Its showing a syntax error in php.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'c, student_attendance sc, students stWHERE c.course_id = sc.c_id AND st.stdnt_rf' at line 1

Member Avatar for diafol

It often helps to provide the full syntax of the query. Use JOINS instead of placing this in the WHERE clause.

SELECT c.course_name, sc.st_classes_attempt, sc.st_classes_missed, st.stdnt_name
FROM `courses` AS `c`
    INNER JOIN `student_attendance` AS `sc`
        ON c.course_id = sc.c_id 
    INNER JOIN `students` AS `st`
        ON st.stdnt_rfid_tag = sc.st_id;
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" type="text/css" href="style.css" />
<title>Attendance</title>
</head>
<body>
<?php
session_start();
$connection = mysql_connect('localhost','root','');
if(!$connection){
    die("Database Connection Failed". mysql_error());
    }
$select_db = mysql_select_db('hamdard_attendance');
if(!$select_db){
    die("Database Connection Failed" . mysql_error());
    }
?>
<div id="container">
        <div id="header">
            <h1 style="text-align:left">Quality Management<span class="off"> Cell</span></h1>

        </div>   

        <div id="menu">
            <ul>
                <li class="menuitem"><a href="cms.php">Home</a></li>
                <li class="menuitem"><a href="cms-attendance.php">Attendance</a></li>
                <li class="menuitem"><a href="cms-courses.php">Courses</a></li>
                <li class="menuitem"><a href="cms-settings.php">Settings</a></li>

            </ul>
            <a style="text-align:right" href="cms-logout.php">Logout</a>
        </div>

        </div>        
        <div id="content">
        <div id="content_top"></div>
        <div id="content_main">
<?php                               
if (isset($_SESSION['user_id'])){
$query_four = "SELECT c.course_name, sc.st_classes_attempt, sc.st_classes_missed, st.stdnt_name";
$query_four.= "FROM `courses` AS `c`";
$query_four.= "INNER JOIN `student_attendance` AS `sc`";
$query_four.= "ON c.course_id = sc.c_id";
$query_four.= "INNER JOIN `students` AS `st`";
$query_four.= "ON st.stdnt_rfid_tag = sc.st_id";

$resultAttendancePercent = mysql_query($query_four) or die(mysql_error());


echo "<table border='1': bordercolor='silver'>";
echo "<tr>";
echo "<td>"  .  "<h4> ";
"Course" . "    " ."</td>";
echo "<td>"  .  "            " ."</td>";
echo "<td>"  .  "<h4> "."Classes Attended" . "    " . "</td>";
echo "<td>"  .  "            " ."</td>";
echo "<td>"  .  "<h4> "."Classes Missed"   . "    " . "</td>"; 
echo "</tr>";
echo "</table>";

while($row = mysql_fetch_array($resultAttendancePercent)){
echo "<br />";
//echo "<td align='center' width='200'>".$row['st_classes_attempt'] . "</td>";
echo "<table border='1': border-color: silver;'>";
echo "<tr>";
echo "<td align='center' width='200'>".$row['course_name'] . "</td>";
echo "<td align='center' width='200'>".$row['st_classes_attempt'] . "</td>";
//echo "<td align='center' width='200'>".$row['st_classes_attempt'] . "</td>";
echo "</tr>";
echo "</table>";
}
}

?>


        <p>&nbsp;</p>
            <p>&nbsp;</p>

        <div id="content_bottom"></div>


      </div>
   </div>
</body>
</html>

I have entered your query...

Now its showing me the following error again..

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS cINNER JOIN student_attendance AS scON c.course_id = sc.c_idINNER JOIN ' at line 1

There is a space required at the end of each lines. Your SQL Statement should be

$query_four = "SELECT c.course_name, sc.st_classes_attempt, sc.st_classes_missed, st.stdnt_name ";
$query_four.= "FROM `courses` AS `c` ";
$query_four.= "INNER JOIN `student_attendance` AS `sc` ";
$query_four.= "ON c.course_id = sc.c_id ";
$query_four.= "INNER JOIN `students` AS `st` ";
$query_four.= "ON st.stdnt_rfid_tag = sc.st_id";

It works buddy!

But a little problem is occcuring, only 2 columns are being displayed, Classes Missed column is not fetched in my display?

Member Avatar for diafol
$q = "SELECT c.course_name, sc.st_classes_attempt, sc.st_classes_missed, st.stdnt_name
FROM `courses` AS `c`
    INNER JOIN `student_attendance` AS `sc`
        ON c.course_id = sc.c_id 
    INNER JOIN `students` AS `st`
        ON st.stdnt_rfid_tag = sc.st_id;";

Not sure why you feel the need to concatenate. Run this query in phpmyadmin.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.