Member Avatar for engrjd91

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

Student_courses.jpg 26.42 KB Courses.jpg 56.84 KB students.jpg 56.33 KB Users.jpg 85.79 KB
Edited by engrjd91
  • 3 Contributors
  • 5 Replies
  • 199 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by diafol
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;
Member Avatar for engrjd91 
<!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

Member Avatar for Santanu.Das

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";
Edited by Santanu.Das
Member Avatar for engrjd91

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.