how to prevent same record to be inserted twice in mysql using php

Question

The following is my code that I have written for not inserting same data I would like if the record exist in mysql then it should show me error message that the record already exist the else part should insert record to database but it not working can any one help me plz the help would be highly appreciated

function addcontact()
    {
        if(isset($_POST['addContact']))
        {
            $officeName = strip_tags($_POST['office_name']);
            $contactName = strip_tags($_POST['contactName']);
            $contactNo = strip_tags($_POST['contactNo']);
            $digitalNo = strip_tags($_POST['digitalNo']);
        $mobileNo = strip_tags($_POST['mobileNo']);

        $check="SELECT * FROM contacts WHERE office_name = '$officeName'";
        if(mysql_num_rows($check) != 0)
         {
            echo "Already in Exists<br/>";
        }else
                     {          
                        $sql = mysql_query("INSERT INTO contacts (office_name, contact_no, 
                          digital_no, mobile_no) VALUES 
                         ('$contactName','$contactNo','$digitalNo','$mobileNo')") or die(mysql_error());
        if($sql)
        {
            header("Location: index.php?admin&done");  exit;
        }
        else
        {
            header("Location: index.php?admin&failed");  exit;  
        }
    }
}
}

"*but it not working*" is not an acceptable problem description — , Dec 16 '13 at 07:08
[**Please don't use mysql_*; it is deprecated.**](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php?rq=1) Also, your code is wide open to SQL injection. — elixenide, Dec 16 '13 at 07:09
you have not executed first query '$check="SELECT * FROM contacts WHERE office_name = '$officeName'";', only checking num rows!! — Suleman Ahmad, Dec 16 '13 at 07:15

himanshu bhardiya · Answer 1 · 2013-12-16T07:24:07.693

1

you did mistake here.

$check="SELECT * FROM contacts WHERE office_name = '$officeName'";
    if(mysql_num_rows($check) != 0)
     {
        echo "Already in Exists<br/>";
    }

just add mysql_query like

$check=mysql_query("SELECT * FROM contacts WHERE office_name = '$officeName'");
    if(mysql_num_rows($check) != 0)
     {
        echo "Already in Exists<br/>";
    }

or you can also use like

    $name=$_POST['username'];

$q="select * from login where name='$name' ";
$rs=mysql_query($q);
if(mysql_fetch_row($rs)>0)
{
    echo "already exist";
}
else
{
    $msg="done";
}

edited Dec 16 '13 at 07:24

answered Dec 16 '13 at 07:11

himanshu bhardiya

81
1
9

any one tell me why it is negative rated? – himanshu bhardiya Dec 16 '13 at 07:23
because this way you are doing an extra query(SELECT) and another to actually INSERT. – Gabriel C. Troia Dec 16 '13 at 07:30
1

i showed mistake that @Abdulkarim Dorman do. he missed mysql_query, so i show that and posted. m i do any thing wrong? – himanshu bhardiya Dec 16 '13 at 07:32
i just realize user that he did mistake there. nothing else. thanks @GabrielC.Troia – himanshu bhardiya Dec 16 '13 at 07:35
dear (himanshu bhardiya) let me know how to redirect when I submit the same record to : index.php?addcontacts page I have written the following code in process.php page: – Abdulkarim Dorman Dec 16 '13 at 10:36
header("Location: index.php"); – himanshu bhardiya Dec 16 '13 at 11:25

Gabriel C. Troia · Answer 2 · 2013-12-16T07:29:04.937

Add the ON Duplicate KEY Update. This way you don't need to check if the record already exists, which means you don't need an extra select query just to check. If it exists, nothing happens.

INSERT INTO contacts (office_name, contact_no, digital_no, mobile_no) 
VALUES ('$contactName','$contactNo','$digitalNo','$mobileNo')
ON DUPLICATE KEY UPDATE office_name = office_name

And set the office_name to be the primary key or a unique index.

score 0 · Answer 3 · answered Dec 16 '13 at 07:17

There is missing one step, your first query is not executed, please try this:-

function addcontact()
{
    if(isset($_POST['addContact']))
    {
        $officeName = strip_tags($_POST['office_name']);
        $contactName = strip_tags($_POST['contactName']);
        $contactNo = strip_tags($_POST['contactNo']);
        $digitalNo = strip_tags($_POST['digitalNo']);
    $mobileNo = strip_tags($_POST['mobileNo']);

    $check= mysql_query("SELECT * FROM contacts WHERE office_name = '{$officeName}'");
    if(mysql_num_rows($check) != 0)
     {
        echo "Already in Exists<br/>";
    }else
                 {          
                    $sql = mysql_query("INSERT INTO contacts (office_name, contact_no, 
                      digital_no, mobile_no) VALUES 
                     ('$contactName','$contactNo','$digitalNo','$mobileNo')") or die(mysql_error());
    if($sql)
    {
        header("Location: index.php?admin&done");  exit;
    }
    else
    {
        header("Location: index.php?admin&failed");  exit;  
    }
}

} }

score 0 · Answer 4 · answered Dec 16 '13 at 09:04

you can handle it from database side. write a stored procedure such a way that first check weather the record is in database or not if exist then ignore it and get back the text "Record already exist", if not exist then insert it to table. use conditional statements in mysql.

how to prevent same record to be inserted twice in mysql using php

4 Answers4