How to check if a record is registered or not

Question

I want to check if an id is registered in user table or not in MySQL, if it is registered in user table make an entry of RFID and if not echo invalid id. I have done this so far which is making entry without any checking. This is tbl_attendance while ids are registered in tbl_user. I am getting id from an Arduino.

<?php
include ('connection.php');
$sql_insert = "INSERT INTO tbl_attendance (rfid_uid) VALUES ('".$_GET["rfid_uid"]."')";
if(mysqli_query($con,$sql_insert))
{
mysqli_close($con);
}

?>

you can get last inserted id by using mysqli_insert_id($con) to cross check — Parth Shah, Jun 04 '18 at 10:47
"SELECT rfid_uid FROM tbl_attendance WHERE rfid_uid = '".$id."' " -> mysqli_num_rows() will give you the number of rows that have this id. Keep in mind that you have serious security issues in your code. You need to check first, if the id is valid. — Bernhard, Jun 04 '18 at 10:50
"Keep in mind that you have serious security issues in your code" True he needs to prevent SQL injections https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq= ... "You need to check first, if the id is valid" @Bernhard Just use InnoDB engine with foreign keys "problem" solved — Raymond Nijland, Jun 04 '18 at 11:40

score -1 · Accepted Answer · answered Jun 04 '18 at 12:52

-1

This code should do what you are asking. You should look into using prepared statements (for example PDO) as the mysqli library was deprecated.

<?php

    //Include the mysql connection to the database
    include ('connection.php');

    //Find the existing RFID rows
    $result = mysqli_query($con, "SELECT id FROM tbl_user WHERE rfid_uid = '". $_GET["rfid_uid"] ."'");

    //Count the number of rows
    $count = mysqli_num_rows($result);

    if( $count == 1 ){ //If the user exists in the tbl_usr
        //Build the table insert query
        $sql_insert = "INSERT INTO tbl_attendance (rfid_uid) VALUES ('".$_GET["rfid_uid"]."')";

        //Execute that query
        if( mysqli_query($con, $sql_insert) ){
            echo "Attendance registered successfully!";    //Success message
        } else {
            echo "Attendance failed to register!";         //Failure message
        }
    } else {
        //Display the "Invalid ID Message"
        echo "Invalid id";
    }

    //Close the mysql connection object
    mysqli_close($con);

?>

answered Jun 04 '18 at 12:52

Harvey Fletcher

1,167
1
9
22

@Salman I have tested this code, and it does insert the record into the `tbl_attendance` table. I think some debugging may be required in your code. Place the function `mysqli_error($con)` before and after the `if( $count == 1)` statement and post the output here. – Harvey Fletcher Jun 05 '18 at 09:11
It shows invalid id while I have id registered in tbl_users. mysql> select *from tbl_users;; | user_id | name | mobile | password | rfid_uid | +---------+--------+-----------+----------+----------+------- | 1 | Aslam | | | 85988145 | – Salman Jun 05 '18 at 12:56
OK, this is because you are using the column `user_id` instead of `id`, if you amend the query for finding existing rows to this `$result = mysqli_query($con, "SELECT user_id FROM tbl_user WHERE rfid_uid = '". $_GET["rfid_uid"] ."'");`, you should no longer get that problem. – Harvey Fletcher Jun 05 '18 at 13:31
Yes i just realized half an hour ago now it is working fine thanks alot – Salman Jun 05 '18 at 13:39

How to check if a record is registered or not

1 Answers1