CODEWITHSUNDEEP
phpmysql
Mithu
Mithu

Reputation: 655

Insert unique record in MySQL

I am inserting data to payments table where Ref column needs to be unique. In below code, with $ref=$_POST['PAYMENT_ID']; I am inserting always a new ref no with other column value in each post back. Sometimes it is posting duplicate ref no. I am trying to keep the ref no unique.

For this I have written following code.What I am trying to do is if existing Ref Column value doesn't match with new $ref=$_POST['PAYMENT_ID'] value then it will insert data.But it is not working and inserting ref no yet it is matched with new ref no.

How can I do this?

<?php
$ref=$_POST['PAYMENT_ID'];
$pending = "Pending";
$method = "Uploaded funds";
$today = date('Y-m-d');
$time=$_POST['TIMESTAMPGMT'];
$type = "Pm";

$table_payments = $wpdb->prefix . "payments";
$check=$wpdb->get_results("SELECT Ref FROM $table_payments");
$checkrows=mysqli_num_rows($check);

         if ($checkrows!=$ref) {
                if($hash2==$_POST['V2_HASH']){
                    $wpdb->insert( $table_payments, array(
                        'Method' => $method,  
                        'Today' => $today,
                        'Time' => $time,
                        'Ref' => $ref,
                        'Batch' => $batch,                      
                        'Type'  => $type,
                        'Status' => $pending,
                        'User' => $me
          ));}}
      ?>

Upvotes: 1

Views: 362

Answers (1)

Rajdeep Paul
Rajdeep Paul

Reputation: 16963

The problem is, $Ref is a user's submitted value and $checkrows holds the total number of rows in $table_payments table. And that's why the condition if ($checkrows!=$ref) { ... is failing.

You have to change your SQL query and the subsequent code in the following way,

// your code
$table_payments = $wpdb->prefix . "payments";
$check=$wpdb->get_results("SELECT * FROM $table_payments WHERE Ref = '$ref'");
$checkrows=$wpdb->num_rows;

if ($checkrows == 0) {
    // do INSERT operation
}

Moreover, you should make Ref column of your table unique

Upvotes: 1

Related Questions