Check if record already exists before creating a new record

Question

I have the following code which posts from the form to the database by saving what is typed into the form. This works but i would like to know how can i be able to check if the record i am entering already exists before adding it as a dublicate and coursing an error. In this case it should be checking if the student_id already exists. If it exists it should echo (record already exists)

$error1='Add New Intern ';
$error0='No error';

    if(isset($_POST['btnaddint']))
{
    $student_id = trim($_POST['student_id']);
    $comp_name = trim($_POST['comp_name']);
    $comp_supervisor = trim($_POST['comp_supervisor']);
    $comp_tel = trim($_POST['comp_tel']);
    $comp_address = trim($_POST['comp_address']);
    $comp_city = trim($_POST['comp_city']);
    $intake_date = trim($_POST['intake_date']);
    $ass_status = trim($_POST['ass_status']);

    if($student_id == '' || $comp_name == '' || $comp_supervisor == '' || $comp_tel == '' || $comp_address == '' || $comp_city == '' || $intake_date == '' || $ass_status == '')
    {
    $error1=" ERROR - Please make sure all required fields are filled ";

    }
else
    {
    require("server/db.php");
    $tbl_name="int_company"; // Table name 
    // Connect to server and select database.
    mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
    mysql_select_db("$db_name")or die("cannot select DB");

    $student = mysql_query("INSERT INTO $tbl_name (student_id, comp_name, comp_supervisor, comp_tel, comp_address, comp_city, intake_date, ass_status) VALUES('".$student_id."','".$comp_name."','".$comp_supervisor."','".$comp_tel."','".$comp_address."','".$comp_city."','".$intake_date."','".$ass_status."')") or die("Query failed:4 ".mysql_error());
    $error1=" Record has been added... ";
    }
}

This is not an answer to your question, but your code uses server side validation and is not an efficient way to validate a form. It requires a round trip to the server and back. Much better to use client side validation via javascript. — Steve, Apr 21 '14 at 10:38
@Steve It's not "much better". And it shouldn't be used instead of server-side validation. — xzag, Apr 21 '14 at 11:29
You should have a unique/primary key on student_id. After that use `INSERT ... ON DUPLCATE KEY UPDATE` query — xzag, Apr 21 '14 at 11:31

score 1 · Answer 1 · edited May 23 '17 at 10:27

1

This question has been already asked in stackoverflow, you can view this one here Logic for already exist record check but only in case of updated form values and using conditional logic : check if record exists; if it does, update it, if not, create it. These above link will help you to get answer. I guess this will help you

edited May 23 '17 at 10:27

Community

1
1

answered Apr 21 '14 at 11:47

Sabir Abdul Gafoor Shaikh

164
10

score 0 · Answer 2 · answered Apr 21 '14 at 10:40

If the student id field is a UNIQUE index then trying to add the same id will fail.

You can then trap the error and if it is due to an existing record, display whatever you want.

However if you are learning PHP programming, you should not be using mysql_ as it is deprecated - either use mysqli or PDO

score 0 · Accepted Answer · answered Apr 21 '14 at 11:20

try this

 <?php
    $error1='Add New Intern ';
    $error0='No error';

    if(isset($_POST['btnaddint']))
    {
        $student_id = trim($_POST['student_id']);
        $comp_name = trim($_POST['comp_name']);
        $comp_supervisor = trim($_POST['comp_supervisor']);
        $comp_tel = trim($_POST['comp_tel']);
        $comp_address = trim($_POST['comp_address']);
        $comp_city = trim($_POST['comp_city']);
        $intake_date = trim($_POST['intake_date']);
        $ass_status = trim($_POST['ass_status']);

        if($student_id == '' || $comp_name == '' || $comp_supervisor == '' || $comp_tel == '' || $comp_address == '' || $comp_city == '' || $intake_date == '' || $ass_status == '')
        {
            $error1=" ERROR - Please make sure all required fields are filled ";

        }
        else
        {
            require("server/db.php");
            $tbl_name="int_company"; // Table name 
            // Connect to server and select database.
            mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
            mysql_select_db("$db_name")or die("cannot select DB");

            $res_student = mysql_query("SELECT student_id FROM $tbl_name WHERE student_id='$student_id' LIMIT 1 ") or die(mysql_error());
            if($row_student = mysql_fetch_assoc($res_student))
            {
                $error1 = "Record is already exists ... ";
            }
            else
            {
                $student = mysql_query("INSERT INTO $tbl_name (student_id, comp_name, comp_supervisor, comp_tel, comp_address, comp_city, intake_date, ass_status) VALUES('".$student_id."','".$comp_name."','".$comp_supervisor."','".$comp_tel."','".$comp_address."','".$comp_city."','".$intake_date."','".$ass_status."')") or die("Query failed:4 ".mysql_error());
                $error1=" Record has been added... ";
            }   
        }
    }

?>

score 0 · Answer 4 · answered Apr 21 '14 at 12:03

 $student_id =mysql_real_escape_string( trim($_POST['student_id']));
$res = mysql_query('select count(*) from $tbl_name where student_id= ' .$student_id) or die();
$row = mysql_fetch_row($res);
if ($row[0] > 0)
{
    //student_id exists
}
else
{
    //It doesn't
}

Check if record already exists before creating a new record

4 Answers4