How to Restrict Duplicate Entries in Database?

Have you tried using the UNIQUE keyword as Tobias Schulz suggested?

As far as i know, i can only make one column in database as unique. But i need two things to be unique. I have built a form which accepts name,phone no. and message. It stores all the information in a database. I want that if a user enters same name and phone no. multiple times it echo "You are already registered".

Here's an example of doing a unique on multiple columns:

ALTER TABLE table_name_here
ADD CONSTRAINT unique_constraint_name_phone UNIQUE (name,phone)

Thanks Andrew. That worked perfectly fine. But my need is a bit different. I want that when a person enters his information for example : he enter name=andrew and phone=12345 then second time name=andrew and phone=54321 then the entry should be saved in table. But when i make both the columns unique then this is not possible. I want combination of two things to be unique. Hope you understand.

I don't know how you'd do it at the database level. You probably could do it at the application level depending on the error message you get back.

My need is a bit different. I want that when a person enters his information for example : he enter name=andrew and phone=12345 then second time name=andrew and phone=54321 then the entry should be saved in table. But when i make both the columns unique then this is not possible. I want combination of two things to be unique. Hope you understand.

Try to do a SELECT Query with the name and phone. If you getting a result, this pair of data already exists. If not you can do your insert.

Following is my code. please tell me what i am doing wrong. It is always showing "you are already registered". I think we had to check the no. of rows returned. Please Help.

<?php

if ($_SERVER["REQUEST_METHOD"] == "POST") { $name = $_POST["name"]; $email = $_POST["email"]; $message = $_POST["message"]; $email_body = ""; $email_body = $email_body . "Name: " . $name . "\n"; $email_body = $email_body . "Email: " . $email . "\n"; $email_body = $email_body . "Message: " . $message;

$db = new PDO("mysql:host=localhost;dbname=connection","root","");

$query1="SELECT * FROM connect WHERE 'name'='$name' AND 'email'='$email'";

$res = $db->query($query1);

if ($res)

{
    echo "you are already registered";
}
else {  

$query = "INSERT INTO connect(name,email,message)VALUES('$name','$email','$message')";

$result = $db->query($query);

if($result) { echo "Successfully updated database"; } else {

echo"Not Updated";
  }

 }

} ?>

   <h1>Contact</h1>



   <p>I&rsquo;d love to hear from you! Complete the form to send me an email.</p>

   <form method="post" action="contact.php">

    <table>
        <tr>
                        <th>
                            <label for="name">Name</label>
                        </th>
                        <td>
                            <input type="text" name="name" id="name">
                        </td>
                    </tr>
                    <tr>
                        <th>
                            <label for="email">Email</label>
                        </th>
                        <td>
                            <input type="text" name="email" id="email">
                        </td>
                    </tr>
                    <tr>
                        <th>
                            <label for="message">Message</label>
                        </th>
                   <td>
                            <textarea name="message" id="message"></textarea>
                  </td>
                </tr>                    
            </table>
        <input type="submit" value="Send">

       </form>

Try a different SELECT QUERY:

SELECT COUNT(id) FROM mytable WHERE name='name' AND phone='phone'.

In your SQL answer you will find a field with the name COUNT. Check that number.

In your SQL answer you will find a field with the name COUNT. Check that number ?????

I don't get that. Can you please provide me with an example.

Try this with your select query and "count(*)"

$nRows = $pdo->query('select count(*) from blah')->fetchColumn(); echo $nRows;

tell us what you get returned by "echo $nRows;"

I get my no. of rows i.e 2.

Nice.

<?php

if($nRows >= 1) { echo "Already Registered"; } else { //insert here }

?>

And you´re done

$nRows = $db->query("select count(*) from connect where name=$name AND email=$email")->fetchColumn();

Is this correct? It is returning 0 rows always

This should be correct with PDO.

Thanks a lot Tobias Schulz for your help. It is finally working as i wanted. Thanks a ton.