How to show a person is present or absent in database!!

Hi all!!! I am planning to create RFID attendance system. Now I have completed the part upto “rfid tag being sent to php” but am in dilemma!! How can I show the person with tag x is present or absent and upload it to database!!! Hope you all got my question!!

So it’s the PHP part you’re struggling with? If so, show the code you’re using on the arduino and I’ll probably be able to help with the PHP script.

#include <Ethernet.h>           
#include <SPI.h>
#include <Client.h>             
#include<SoftwareSerial.h>

SoftwareSerial RFID(2,3);
int rfid[14],i;


byte mac[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };  
byte subnet[] = {255,255,255,0};

IPAddress gateway(192,168,1,1);
//IPAddress DBserver(192, 168, 1, 2);
IPAddress server(192,168,1,4);                  
//char server[] = "localhost/te"; 
IPAddress ip(192,168,1,5);



   EthernetClient client;
void setup()
{
  Serial.begin(9600);
  RFID.begin(9600);
  
  delay (1000); 
  Serial.print("*********Initializing Ethernet*********\n");   
  
  if (Ethernet.begin(mac) == 0) {
      Serial.println("Failed to configure Ethernet using DHCP");
      Ethernet.begin(mac, ip);
     }
  
  delay(1000);
  Serial.println("connecting...");
  Serial.print("IP Address:");
  Serial.println(Ethernet.localIP());
  Serial.print("Subnet Mask:");
  Serial.println(Ethernet.subnetMask());
  Serial.print("Default Gateway IP:");
  Serial.println(Ethernet.gatewayIP());
  Serial.print("DNS Server IP:");
  Serial.println(Ethernet.dnsServerIP());  
}

void loop()
{

    if (client.connect(server, 80))
    {
          Serial.print("Connected to server:");
          Serial.println(Ethernet.dnsServerIP());
             Serial.println("reading rfid tags....");
while(1)
{
    if (RFID.available()>0)
    { 
      
       for (i=0; i<14; i++)
       {
      rfid[i]=RFID.read();
      Serial.print(rfid[i],DEC);
       }
      Serial.println("Sending to Server...");
     
      client.print( "GET /te/add_data.php?");
    client.print("serial=");
     for (i=0; i<14; i++)
      {
      client.print(rfid[i]);
       }
    
    client.print("&&");
    client.print("temperature=");
    client.print( "0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED" );
    client.println( " HTTP/1.1");
    client.print( "Host: " );
    client.println(server);
    client.println( "Connection: close" );
    client.println();
    client.println();
    client.stop(); 
    
      Serial.println("data sent to server");
      RFID.flush();
    
     }}
   
}
}

}

OK Do you have the database, user account, and table setup on your server?

Oh and which database server are you using?

I have and I am using xampp!!
my database contains:
Rfid id
Name
Module
Attendance

“dbconnect.php”

<?php
$MyUsername = "root";  // enter your username for mysql
$MyPassword = "";  // enter your password for mysql
$MyHostname = "localhost";      // this is usually "localhost" unless your database resides on a different server

$dbh = mysql_pconnect($MyHostname , $MyUsername, $MyPassword);
$selected = mysql_select_db("test",$dbh);
?>

review_data.php

<?php 
    // Start MySQL Connection
    include('dbconnect.php'); 
?>

<html>
<head>
    <title>Arduino Log</title>
    <style type="text/css">
        .table_titles, .table_cells_odd, .table_cells_even {
                padding-right: 20px;
                padding-left: 20px;
                color: #000;
        }
        .table_titles {
            color: #FFF;
            background-color: #666;
        }
        .table_cells_odd {
            background-color: #CCC;
        }
        .table_cells_even {
            background-color: #FAFAFA;
        }
        table {
            border: 2px solid #333;
        }
        body { font-family: "Trebuchet MS", Arial; }
    </style>
</head>

    <body>
        <h1>Arduino Log</h1>
    <table border="0" cellspacing="0" cellpadding="4">
      <tr>
            <td class="table_titles">ID</td>
            <td class="table_titles">Date and Time</td>
            <td class="table_titles">Module</td>
            <td class="table_titles">Attendance in Celsius</td>
          </tr>
<?php
    // Retrieve all records and display them
    $result = mysql_query("SELECT * FROM FYP ORDER BY id ASC");

    // Used for row color toggle
    $oddrow = true;

    // process every record
    while( $row = mysql_fetch_array($result) )
    {
        if ($oddrow) 
        { 
            $css_class=' class="table_cells_odd"'; 
        }
        else
        { 
            $css_class=' class="table_cells_even"'; 
        }

        $oddrow = !$oddrow;

        echo '<tr>';
        echo '   <td'.$css_class.'>'.$row["id"].'</td>';
        echo '   <td'.$css_class.'>'.$row["Name"].'</td>';
        echo '   <td'.$css_class.'>'.$row["Module"].'</td>';
        echo '   <td'.$css_class.'>'.$row["Attendance"].'</td>';
        echo '</tr>';
    }
?>
    </table>
    </body>
</html>

add_data.php

<?php
    // Connect to MySQL
    include("dbconnect.php");

    // Prepare the SQL statement
    $SQL = "INSERT INTO test.FYP (sensor ) VALUES ('".$_GET["serial"]."', '".$_GET["Attendance"]."')";     

    // Execute SQL statement
    mysql_query($SQL);

    // Go to the review_data.php (optional)
    header("Location: review_data.php");
?>

OK you say your database has: Rfid id Name Module Attendance

Just to clarify is this a table named "Rfid" with the field names of "id","Name","Module" and "Attendance"?

Also looking at the PHP you have so far, can you confirm that your database name really is "test"?

But you are only sending two fields from your arduino. the first is named t0 (which actually contains the RFID) and the second is named mac (which appears to be a hardware mac address).

Where is your php expected to pick up the other fields from (ie name and attendance?) I'm guessing that the name could be looked up from a separate table, (if so it's not needed in this table anyhow) and attendance may be the current time stamp.

You've also given the content of your file review_data.php. Can you confirm that this, at least is working?

This is a fundamentally difficult problem:

"Put your hand up if you're not here."

The database needs to know who was supposed to attend before you can work out who didn't attend.

KenF sorry I had given you the wrong file, the new file is(also the arduino code has been changed)
add_data.php

<?php
    // Connect to MySQL
    include("dbconnect.php");

    // Prepare the SQL statement
    $SQL = "INSERT INTO test.temperature (sensor ,celsius) VALUES ('".$_GET["serial"]."', '".$_GET["temperature"]."')";     

    // Execute SQL statement
    mysql_query($SQL);

    // Go to the review_data.php (optional)
    header("Location: review_data.php");
?>

review_data.php

<?php 
    // Start MySQL Connection
    include('dbconnect.php'); 
?>

<html>
<head>
    <title>Attendance Log</title>
    <style type="text/css">
        .table_titles, .table_cells_odd, .table_cells_even {
                padding-right: 20px;
                padding-left: 20px;
                color: #000;
        }
        .table_titles {
            color: #FFF;
            background-color: #666;
        }
        .table_cells_odd {
            background-color: #CCC;
        }
        .table_cells_even {
            background-color: #FAFAFA;
        }
        table {
            border: 2px solid #333;
        }
        body { font-family: "Trebuchet MS", Arial; }
    </style>
</head>

    <body>
        <h1>Attendace Log</h1>
    <table border="0" cellspacing="0" cellpadding="4">
      <tr>
            <td class="table_titles">ID</td>
            <td class="table_titles">Date and Time</td>
            <td class="table_titles">RFID Tag</td>
            <td class="table_titles">MAC Address</td>
          </tr>
<?php
    // Retrieve all records and display them
    $result = mysql_query("SELECT * FROM temperature ORDER BY id ASC");

    // Used for row color toggle
    $oddrow = true;

    // process every record
    while( $row = mysql_fetch_array($result) )
    {
        if ($oddrow) 
        { 
            $css_class=' class="table_cells_odd"'; 
        }
        else
        { 
            $css_class=' class="table_cells_even"'; 
        }

        $oddrow = !$oddrow;

        echo '<tr>';
        echo '   <td'.$css_class.'>'.$row["id"].'</td>';
        echo '   <td'.$css_class.'>'.$row["event"].'</td>';
        echo '   <td'.$css_class.'>'.$row["sensor"].'</td>';
        echo '   <td'.$css_class.'>'.$row["celsius"].'</td>';
        echo '</tr>';
    }
?>
    </table>
    </body>
</html>

dbconnect.php

<?php
$MyUsername = "root";  // enter your username for mysql
$MyPassword = "oranje";  // enter your password for mysql
$MyHostname = "localhost";      // this is usually "localhost" unless your database resides on a different server

$dbh = mysql_pconnect($MyHostname , $MyUsername, $MyPassword);
$selected = mysql_select_db("test",$dbh);
?>

OK Now your add_data.php is attempting to insert a new row in the table named "temperature". Furthermore it's taking the field name of the new row from the value passed with the name "serial" and updating this mystery field with the value passed as "temperature".

This makes no sense at all.

First of all I'd question why you'd call your table "temperature" if it's a record of something else entirely

Secondly, If you're adding a row to a table you should KNOW which fields need to be defined (not have them given to you by the GET string)

You don't show your new arduino code, so I'm at a loss as to what you're trying to do.

I took the code from arduino temperature system so that is why there is temperature in the code. Furthermore, I have updated the code above for the arduino(int the 3rd reply to be exact)

Your table name and field names are all over the place. There’s no consistency here.

Try saving this on your server with the name add.php.
Open it in your browser (using the address localhost/add.php )
Then fill in some values and submit.

With a bit of luck it will not report any errors. Don’t waste your time with the arduino code until you can get this much working.

<?php

$MyUsername = "root";  // enter your username for mysql
$MyPassword = "oranje";
$MyHostname = "localhost";      // this is usually "localhost" unless your database resides on a different server
$db="test";
?>

<html>
<head>
<title>Test for uploading data</title>
</head>
<body>

<?php
if(isset($_GET['id']))
{
  $id = $_GET['id'];
  $name = $_GET['name'];
  $module = $_GET['module'];
  $attendance = $_GET['attendance'];
  $error=0;

  $link=mysqli_connect($MyHostname, $MyUsername, $MyPassword );
  if(!$link)
    {echo mysqli_error ( $link );
     echo "

";
     $error++;
    }
  
  if(!mysqli_select_db($link,$db))
    {echo mysqli_error ( $link );
     echo "

";
     $error++;
    }


  $query = "INSERT INTO Rfid(`id`,`Name`,`Module`,`Attendance`) VALUES ('$id','$name','$module','$attendance')";

  if(!mysqli_query($link,$query))
    {echo mysqli_error ( $link );
     echo "

";
     $error++;
    }

  mysqli_close($link);

if ($error==0)
  echo "1 record inserted

";
else
  echo "Errors encountered attempting to add records";

}

?>

<form action="add.php" method="GET">
value for id

<input type="text" name="id">


value for name

<input type="text" name="name">


value for "module"

<input type="text" name="module">


value for attendance

<input type="text" name="attendance">


<input type="submit">

</form>
</body>
</html>