How to prevent duplicate data in sql databse

Question

I have a form where i save students login data to a database. The form includes the "admission_number", "username" and "password" fields. i want to show an error if the admission number is already existing and a user tries to add it again. Here's my php code for inserting the record.

<?php
        if(isset($_POST['submit']))
        {
        $server     = 'localhost';
        $username   = 'root';
        $password   = '';

        $course_code=$_POST['course_code'];
        $course_title=$_POST['course_title'];
        $course_units=$_POST['course_units'];
        $course_semester=$_POST['course_semester'];


        $con=($GLOBALS["___mysqli_ston"] = mysqli_connect($server,  $username,   $password));
        if(!$con)
        {
        exit('Error: could not establish connection to the server');
        }
        else
        {
        $con_db=((bool)mysqli_query($con, "USE esther"));
        if(!$con_db)
        {
        exit('Error: Failed to connect to the database');
        }
        else
        {
        if(!empty($course_code) && !empty($course_title) && !empty($course_units) && !empty($course_semester))
    {
        $insert="INSERT INTO `course_table` VALUES('', '".$course_code."' ,'".$course_title."','".$course_units."','".$course_semester."')";
        $query=mysqli_query($GLOBALS["___mysqli_ston"], $insert);   
        $dup_admission_number = mysql_query("SELECT admission_number FROM users_table WHERE admission_number = $admission_number");
    }
    if (@mysql_query($dup_admission_number)) {
        echo 'Your admission number is already in our database.';
    exit;
    }

        if($query)
        {
                echo 'course added successfully!';
                header("location:add_course.php");      
        }
        else { echo 'Error while adding Course.'; }
    }
    else
    {
        echo '*** fields cannot be blank ***.';
    }
}
}
?>

possible duplicate of [Can I mix MySQL APIs in PHP?](http://stackoverflow.com/questions/17498216/can-i-mix-mysql-apis-in-php) — Funk Forty Niner, Apr 02 '16 at 14:03
Fourth parameter of `mysqli_connect` is the database. Don't need `$con_db=((bool)mysqli_query($con, "USE esther"));`. You also are open to SQL injections. You can use a `unique` constraint to require columns have unique values; http://dev.mysql.com/doc/refman/5.7/en/constraint-primary-key.html. `$admission_number` also appears to never be assigned.. — chris85, Apr 02 '16 at 14:05

score 0 · Answer 1 · answered Apr 02 '16 at 14:13

0

To check admission number is unique or not you have to execute bellow query

$sql: "select id from student where admission_number = <> LIMIT 0,1";

if this query show result then you current form's admission number is not unique.

this process you can do using ajax request or you can check it before insert query being process.

or you can manage it in mysql by giving unique key constraint to admission number.

answered Apr 02 '16 at 14:13

Hirendrasinh S. Rathod

824
6
11

Did you not read any of the comments left under their question? – Funk Forty Niner Apr 02 '16 at 14:23

score 0 · Answer 2 · answered Apr 02 '16 at 14:48

0

This is the Mysql Query

    INSERT INTO sometable (data1, data2, data13) 
  SELECT 'username' FROM sometable
WHERE NOT EXISTS 
  (SELECT username FROM sometable WHERE login='someusername');

answered Apr 02 '16 at 14:48

Stack learner

1,726
2
11
21

How to prevent duplicate data in sql databse

2 Answers2