preventing duplicate row data entries

Question

I had created a database which named student with ID, name, mat_number, specialty, age, and gender, in a PHP application.

I do not want the name or mat_number be taken in more than once.

I have done the connection to my database in a different page and called it in the add student page.

This following codes is for a faculty database collection


    <?php 
                if(isset($_POST['submit']))  {
                    $name = $_POST['name'];
                    $matNo = $_POST['mat_number'];
                    $age = $_POST['age'];
                    $specialty = $_POST['specialty'];
                    $gender = $_POST['gender'];

                    if(!empty($name) && !empty($matNo) && !empty($age) && 
                            !empty($specialty) && !empty($gender))
                {

                    $sql = "INSERT INTO `student`(`name`, `UB_number`, `age`, 
                                          `sex`, `specialty`)
                    VALUES ('$name', '$matNo', '$age', '$gender', '$specialty')";
                    $conn->query($sql);
                    header("Location: index.php");
                }
                else{
                    echo "Error: Complete all records";
                }          
                }

            ?>

I want to get an error message demanding for a change if the 2 fields already exist in the database.

before insert you have need to check , If student name or UB_number is there do update otherwise insert. — Shivendra Singh, Jul 17 '19 at 03:56
**WARNING: You are vulnerable to SQL injection!** Attackers can easily run arbitrary code against your database. You should *strongly* consider using [prepared statements](https://stackoverflow.com/a/60496/2605758) and parameterized queries. You can do this using either PDO or MySQLi. *Never trust input*, especially input coming from the client side. Even if only trusted users use your system, [there is still the chance of corrupting your database](https://bobby-tables.com/). — Hoppeduppeanut, Jul 17 '19 at 04:38

score 0 · Answer 1 · answered Jul 17 '19 at 03:59

first name to check in database if already exist the record.

if no record run sql insert command.

 if(isset($_POST['submit']))  {
     $name = $_POST['name'];
     $matNo = $_POST['mat_number'];
     $age = $_POST['age'];
     $specialty = $_POST['specialty'];
     $gender = $_POST['gender'];
        $sql = "SELECT * FROM `student` WHERE name = "'.$name.'" and UB_number = '".$matNo."'";
            $conn->query($sql);
            $cnt = $conn->rowCount();
        if($cnt == 0){
             $sql = "INSERT INTO `student`
                  (`name`, `UB_number`, `age`,`sex`, `specialty`)
                  VALUES
                  ('$name', '$matNo', '$age', '$gender', '$specialty')";
            $conn->query($sql);
            header("Location: index.php");
       }else{
            echo "Error: Complete all records";
       }          
}

score 0 · Answer 2 · answered Jul 22 '19 at 21:03

If you would like to insert a new record to DB only if one doesn't exist which has the same name or mat_number then you first need to execute SELECT statement to see if it exists.

Using MySQLi:

<?php

include 'mysqli.php';
$conn = $mysqli;

if (isset($_POST['submit'])) {
    $name = $_POST['name'];
    $matNo = $_POST['mat_number'];
    $age = $_POST['age'];
    $specialty = $_POST['specialty'];
    $gender = $_POST['gender'];

    if ($name && $matNo && $age && $specialty && !$gender) {
        $stmt = $conn->prepare('SELECT 1 FROM student WHERE name=? OR UB_number=?');
        $stmt->bind_param('ss', $name, $matNo);
        $stmt->execute();
        $stmt->bind_result($exists);
        $stmt->fetch();
        if (!$exists) {
            $stmt = $conn->prepare('INSERT INTO `student`(`name`, `UB_number`, `age`, `sex`, `specialty`) VALUES(?,?,?,?,?)');
            $stmt->bind_param('sssss', $name, $matNo, $age, $gender, $specialty);
            $stmt->execute();
            exit(header("Location: index.php"));
        } else {
            echo 'A record with this name or material number already exists!';
        }
    } else {
        echo "Error: Complete all records";
    }
}

Using PDO:

<?php

include 'lib.php';
$conn = $pdo;

if (isset($_POST['submit'])) {
    $name = $_POST['name'];
    $matNo = $_POST['mat_number'];
    $age = $_POST['age'];
    $specialty = $_POST['specialty'];
    $gender = $_POST['gender'];

    if ($name && $matNo && $age && $specialty && !$gender) {
        $stmt = $conn->prepare('SELECT 1 FROM student WHERE name=? OR UB_number=?');
        $stmt->execute([$name, $matNo]);
        $exists = $stmt->fetchColumn();
        if (!$exists) {
            $stmt = $conn->prepare('INSERT INTO `student`(`name`, `UB_number`, `age`, `sex`, `specialty`) VALUES(?,?,?,?,?)')
                ->execute([$name, $matNo, $age, $gender, $specialty]);
            exit(header("Location: index.php"));
        } else {
            echo 'A record with this name or material number already exists!';
        }
    } else {
        echo "Error: Complete all records";
    }
}

score -1 · Accepted Answer · answered Jul 17 '19 at 03:54

hope this may be helpfull to you. In here I asume that you are not using any framework. But if you use a framework there are plenty of easy methods to do this.In here I have checked only name field. You should update code as you wants. Also it it better if you could validate your inputs before check. Like trim(). Thanks

<?php 
            if(isset($_POST['submit']))  {

                $name = $_POST['name'];
                $matNo = $_POST['mat_number'];
                $age = $_POST['age'];
                $specialty = $_POST['specialty'];
                $gender = $_POST['gender'];

                //after user click the submit button 
                $sql_Select_Stundets = "SELECT * FROM student WHERE name = '$name' ";
                 // query the sql with db connection         
                $result_sql_Select_Stundets = mysqli_query($conn,$sql_Select_Stundets);
                //Now check the row count to verify the output if there is any match
                $rowcount=mysqli_num_rows($result);

                //Now write insert inside if condition
                 if( $rowcount >0 ) {

                    if(!empty($name) && !empty($matNo) && !empty($age) && 
                    !empty($specialty) && !empty($gender)) {

                    $sql = "INSERT INTO `student`(`name`, `UB_number`, `age`, 
                                  `sex`, `specialty`)
                    VALUES ('$name', '$matNo', '$age', '$gender', '$specialty')";
                    $conn->query($sql);
                    bheader("Location: index.php");
                }else{

                    echo "Error: Complete all records";
                     }    

                }else{
                       echo "<script>
                       alert('sorry this name is already available');
                        </script>";
                    }

                 }              
        ?>

When i run the code i get and error undefined variable: result and warning mysqli_num_rows() expectes parameter . When i put data that dos not exist in the database i will still get the message saying it already exist i tried changing something but it did not still work perfectly — chenko, Jul 18 '19 at 06:12
@chenko it menas that `$result` is not defined. At which line does error occured — Nipun Tharuksha, Jul 18 '19 at 06:26

preventing duplicate row data entries

3 Answers3