If record exists when using $wpdb->insert, don't insert and don't throw failure

Question

What am I trying to do I want to be able to insert records into my custom Wordpress table, but only able to have one row for each unique company name.

If a second record with the same company name is attempted to be inputted, I want the script to bow out gracefully (i.e. not return an error).

What I've done

I am using Wordpress's $wpdb->insert function to insert new rows into a very basic 3-column table which looks a bit like this:

id | company | name

I am using this code:

foreach ( $this->tlds_to_insert as $tld_to_insert ) {

            $wpdb->insert(
            'wp2_bnfo_allowedtlds',
                array(
                    'company'=> $this->member_company,
                    'name' => $tld_to_insert,
                ),
                array(
                    '%s',
                    '%s'
                )
            );

    }

Table set up

I've set up so both the ID (primary key) and the company as unique indexes because I only want one unique record per company.

What I am struggling with

The issue I am facing is that if I try and input a duplicate entry, it does fail (that's good) but I get an error notice saying that is a duplicate and I want to avoid this failure notice.

Research I have read a number of StackOverflow Posts but these tend to relate to people writing custom SQL outside of Wordpress and I am not sure if / how I can utilise this code.

My best guess so far: I could create a $wpdb->get_results query to see if the value exists, and then only run the $wpdb->insert query if it does not. This seems like high overhead.

Any thoughts much appreciated.

score 3 · Accepted Answer · answered Jan 27 '18 at 12:57

3

Checking if a record already exists is correct behavior, but unless you lock your table, it's open to race conditions (e.g. two scripts both checking at the same time, seeing it doesn't exist, and then inserting).

$wpdb offers a replace method, which should help with that, but will change the name if the company is already in there, I don't know if you're fine with that.

You could also write your own query with INSERT ... ON DUPLICATE KEY UPDATE and use raw SQL, as suggested in this answer.

answered Jan 27 '18 at 12:57

janh

2,885
2
22
21

Thanks janh! As a fairly low traffic site / function I am going to use the $wpdb functionality to check as you suggest and the unique setting as a second-layer on the unlikely occurrence (for this site) of race conditions. – LauraTheExplorer Jan 27 '18 at 13:04

score 2 · Answer 2 · answered Jan 27 '18 at 13:03

You have to check if the name is already existing in table before inserting so the below code may help you to check

<?php
function checkExist($name)
    {
        global $wpdb;
        $rowcount = $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->wp2_bnfo_allowedtlds WHERE name = '$name' ");
        return $rowcount;
    }

    foreach ( $this->tlds_to_insert as $tld_to_insert ) {

                if(checkExist($tld_to_insert) > 0)
                {
                continue;
                }
                else
                {
                    $wpdb->insert(
                    'wp2_bnfo_allowedtlds',
                        array(
                            'company'=> $this->member_company,
                            'name' => $tld_to_insert,
                        ),
                        array(
                            '%s',
                            '%s'
                        )
                    );
                }

        }
?>

If record exists when using $wpdb->insert, don't insert and don't throw failure

2 Answers2