user3328808 user3328808 - 7 months ago 19
SQL Question

What mysql table and query is used in my student attendance project?

I am student of diploma, I want to create a student attendance project in PHP & MySQL. Here is project feature diagram (blue print)-

frist part of programm

When we select on class on drop down menu and select a class and date, then all of student of that class will we appear in list with roll number and name and a drop down menu of Absent/Present/Leave .

second img

and when we click on save, all attendance of student save successfully. And after this how we access student attendance reports.

Here I do not understand how many tables we create for this project with which column names, and what SQL query we used for completing above operation.

If anyone can help me, then very very thanks in advance.

Answer

Create a table students and fetch the data using SQL queries.

DATABASE TABLE -

id      name   roll_no    class       remark        date
1       amit     21        7th        present      2013-12-28
2       amit     22        7th        present      2013-12-28
3       amit     23        7th        absent       2013-12-28
4       amit     24        8th        leave        2013-12-28


QUERY TO CREATE TABLE -

CREATE TABLE IF NOT EXISTS `users` 
(
   `id` int(20) NOT NULL AUTO_INCREMENT,
   `name` varchar(500) NOT NULL,
   `roll_no` int(11) NOT NULL DEFAULT '',
   `class` varchar(255) NOT NULL,
   `remark` varchar(255) NOT NULL,
   `date` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)    
) ENGINE=InnoDB;


YOUR FORM VIEW (say index.php) -

<form action="attendance.php" method="POST">
    Select class: 
    <select name="class">
        <option value="6th">6th</option>
        <option value="7th">7th</option>
        <option value="8th">8th</option>
        <option value="9th">9th</option>
    <select>

    <br>Select date:<input name="date" type="date" />

    <br><input type="submit" />
</form>


attendance.php

 <?php
 $link = mysqli_connect("localhost", "my_user", "my_password", "world");

 /* check connection */
if (mysqli_connect_errno()) {
   printf("Connect failed: %s\n", mysqli_connect_error());
   exit();
}

$class = $_POST['class'];
$date = strtotime($_POST['date']);

/* create a prepared statement */
if ($stmt = mysqli_prepare($link, "SELECT * FROM students WHERE class=? AND UNIX_TIMESTAMP(date)=?")) {

   /* bind parameters for markers */
   mysqli_stmt_bind_param($stmt, "ss", $class, $date);

   /* execute query */
   mysqli_stmt_execute($stmt);

   /* bind result variables */
   mysqli_stmt_bind_result($stmt, $roll_no, $name, $class);

   /* fetch value */

   echo "<table>";
   echo "<tr>";
   echo "<th>Roll No</th>";
   echo "<th>Name</th>";
   echo "<th>Remark</th>";
   echo "</tr>";

   while($fetch = mysqli_stmt_fetch($stmt))
   {
        echo "<tr>";
        echo "<td>".$fetch['roll_no']."</td>";
        echo "<td>".$fetch['name']."</td>";
        echo "<td>".$fetch['remark']."</td>";
        echo "</tr>";
   }
   echo "</table>";


   /* close statement */
   mysqli_stmt_close($stmt);
 }

/* close connection */
mysqli_close($link);
?>
Comments