PDO array if record already existed

Question

I am inserting multiple rows from one submit button by the usage of array. Insertion of records is working smoothly. Now I want to stop insertion of data if record is already existed. My syntax for single record updating is works to prevent multi-insertion of same record. But I am confused and can't get idea while using array. I've tried a lot, but every method display error.

This code is working.

if(isset($_POST['submit'])){
    $number = $_POST['number'];
    $letter = $_POST['letter'];

    $sql = "INSERT INTO class(number, letter) VALUES(:number, :letter)";
    $query = $con->prepare($sql);

    foreach($number AS $key => $n){
        $query->bindParam(':number', $number[$key]);
        $query->bindParam(':letter', $letter[$key]);
        $query->execute();
    }
}

My intention is to stop insertion of data if it is already inserted. So I tried like this. I am sure it is wrong because array variable can't pass to first syntax**(sql1)** and $query is not being accessed to foreach clause. I have no idea, So I used like this. Please provide me any idea to stop already inserted record.

I want to change in this code

<?php
if(isset($_POST['submit'])){
    $number = $_POST['number'];
    $letter = $_POST['letter'];

    $sql1 = 'SELECT COUNT(*) FROM class WHERE number = :number';
    $stmt = $con->prepare($sql1);
    $stmt->bindParam(':number', $number[$key]);
    $stmt->execute();

    if($stmt->fetchColumn()){
        echo"<script>alert('Class is already existed')</script>";
    }
    else{
        $sql = "INSERT INTO class(number, letter) VALUES(:number, :letter)";
        $query = $con->prepare($sql);
    }
    foreach($number AS $key => $n){
        $query->bindParam(':number', $number[$key]);
        $query->bindParam(':letter', $letter[$key]);
        $query->execute();
    }
}

?>

This code displays following error

Notice: Undefined variable: key in C:\xampp\htdocs\marksheet\class.php on line 53
Notice: Undefined variable: query in C:\xampp\htdocs\marksheet\class.php on line 57
Fatal error: Uncaught Error: Call to a member function bindParam() on null in C:\xampp\htdocs\marksheet\class.php:57 Stack trace: #0 {main} thrown in C:\xampp\htdocs\marksheet\class.php on line 57

In line `$stmt->bindParam(':number', $number[$key]);` `$key` is undefined. — Daniyal Nasir, Jan 30 '18 at 11:44
Am working on your problem, do you still want the solution ? — Daniyal Nasir, Jan 30 '18 at 12:39

Daniyal Nasir · Accepted Answer · 2018-01-30T13:47:52.420

This should work:

if (isset($_POST['submit'])) {
    if(!empty($_POST['number']) && !empty($_POST['letter']) )
    {
        $number = $_POST['number'];
        $letter = $_POST['letter'];
        // Assuming both $number and $letter 1 dimensional array with equal number of indexes
        $notInsertedKey = [];
        foreach ($number AS $key => $item) {               

            /* Checking Existence*/
            $sql1 = 'SELECT COUNT(*) as counted FROM class WHERE number = :number';
            $stmt = $con->prepare($sql1);
            $stmt->bindParam(':number', $number[$key]);
            $stmt->execute();
            $data = $stmt->fetch(PDO::FETCH_ASSOC);

            if ($data['counted'] < 1) {
                // echo "Not Exist";

                $sql = "INSERT INTO class(number, letter) VALUES (:number, :letter)";
                $query = $con->prepare($sql);
                $query->bindParam(':number', $number[$key]);
                $query->bindParam(':letter', $letter[$key]);
                $query->execute();

            } else {
                $notInsertedKey[] = $key;
                // Incase you want to know the failed indexes.
                echo "<script>alert('Class is already existed')</script>";
            }
        }
    }
}

UPDATE

Solution suggested by @AlivetoDie works well as shorthand way to insert unique data and drop the duplicated one. However, it doesn't have any means to track the failed data indexes since the query always returns TRUE.

Thank you very much. I had lost whole day trying different way. Finally it works. — AnandaLC, Jan 30 '18 at 14:04

score 1 · Answer 2 · answered Jan 30 '18 at 11:46

I would like to recommend following way. First of all you need a dynamic function which should check record in the relevant table

function isExist($columnName, $columnValue, $tableName){
        $columnName     = (!empty($columnName)) ? $columnName : 'empty';
        $columnValue    = (!empty($columnValue)) ? $columnValue : 'empty';
        $tableName  = (!empty($tableName)) ? $tableName : 'empty';
        $exist = 0;
        if($columnName != 'empty' && $columnValue != 'empty' && $tableName != 'empty'){
            $sql = "select * from `".$tableName."` where `".$columnName."` = '".$columnValue."'";
            $isExist = mysqli_query($this->connection,$sql);
            if(mysqli_num_rows($isExist) > 0){
                $exist = 1;

            }
        }
      return $exist;    
    }

Then use it in your function by following way

if(isset($_POST['submit'])){
    $error = 0; 
    $response = array();
    $number = $_POST['number'];
    $letter = $_POST['letter'];

    $number_name_exist = $this->isExist('number', $number, 'class');
    if($number_name_exist == 1){
       $error = 1;
        $response['error'] =  $number . ' is already exists';
    }

    if($error == 0){

        $sql = "INSERT INTO class(number, letter) VALUES(:number, :letter)";
        $query = $con->prepare($sql);

        foreach($number AS $key => $n){
            $query->bindParam(':number', $number[$key]);
            $query->bindParam(':letter', $letter[$key]);
            $query->execute();
        }

    }
}

In the above example you can see there $error variable and $response array which will be make your script more dynamic and you can prevent duplicate insertion and also you can check multiple columns.

PDO array if record already existed

2 Answers2

UPDATE