8

I am inserting a row into a custom table in a wordpress database. this is the code:

$wpdb->insert( 'wp_pl_my_parts', 
                    array( 
                    'user_ID' => $user_ID, 
                    'PL_part_ID' => $PL_part_ID, 
                    'part_save_date' => $part_save_date ), array( '%d', '%d', '%s' ) );

How do I make sure I do not insert a duplicate entry. Ie. I don't want it to insert if the user_ID and the PL_part_ID are the same as an existing record?

Date doesn't matter and shouldn't be checked.

9

Let's say the primary key of the table is my_part_ID. So we will check if there is any primary key value for the combination of user_ID and PL_part_ID as below

$my_part_ID = $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT my_part_ID FROM " . $wpdb->prefix . "pl_my_parts
                    WHERE user_ID = %d AND PL_part_ID = %d LIMIT 1",
                    $user_ID, $PL_part_ID
                )
            );

if ( $my_part_ID > 0 )
    // exists
else
    // does not exist
  • If you checking by received $rows you need to count($rows) to make if properly work - so if ( count($rows) > 0 ) – pbaranski Jul 25 '18 at 7:36
4

Just create your database table with a UNIQUE index (or indexes) to prevent duplicates, see MySQL: CREATE INDEX Syntax for starters. For the WordPress specifics take a look at Codex: Creating Tables with Plugins - Create Database Tables. Below example code is taken from there:

global $wpdb;
$sql = "CREATE TABLE $table_name (
  id mediumint(9) NOT NULL AUTO_INCREMENT,
  time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  name tinytext NOT NULL,
  text text NOT NULL,
  url VARCHAR(55) DEFAULT '' NOT NULL,
  UNIQUE KEY id (id)
);";

require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql );

The premise of this answer is, to prevent the problem the OP is experiencing beforehand. Meaning by designing, planing and setting it up in a way that the problem can't occur in the first place.

  • 1
    The user asks for a record, not a table AND this answer really isn't the right one for the question asked by the OP. – Mike Kormendy Feb 20 '17 at 23:24
  • @MikeKormendy Well, true, the OP asked for the record. My answer is still valid, because if you create the table with setting the ID or some IDs set to unique, then it's made sure that those are unique. Which ergo leads to solving the OPs problem, which is preventing with entries with duplicate indixes. So your assessment here is not completely correct. – Nicolai Feb 21 '17 at 11:44
  • my assessment is that your answer is vague and not specific enough to be the most appropriate answer. 1. the OP never asked for a unique index regardless of that possible outcome remotely solving the question. 2. the OP was asking for two unique fields, your answer in specificity, does not address that question exactly. Feel free to change your answer to the question directly, specifically. – Mike Kormendy Feb 21 '17 at 16:32
  • The OP would also have to refactor their code to adjust for unique entries by index instead of checking for the unique values themselves. You have not provided that code or resource links for doing that. – Mike Kormendy Feb 21 '17 at 16:39
  • @MikeKormendy The insert method of $wpdb is respecting the uniqueness. Besides answers do not have to give complete solutions, but have to be helpful in solving a problem - my answer meets that. I don't mind if you disagree, but then you shouldn't mind if I disagree with you. – Nicolai Feb 21 '17 at 18:04
3

I have found the best way to check it a record exists or not with WPDB is to check using the WPDB update function first. An example of this could be:

    if(!$wpdb->update($wpdb->prefix.'table_name',$data,array('id'=>$dbRowId),array('%s'),array('%d'))){
        $wpdb->insert($wpdb->prefix.'table_name',$data,array('%s'));
        return $wpdb->insert_id;
    }else{
        return $dbRowId;
    }

It's a little bit bigger than you may want in terms as an overall function but if you set it up as a callable method in a model or DB class in your plugin or theme, you can have it handle to WPDB functionality.

  • That sounds interesting, looking at my original code how would that work. The $dbRowId I think I am using, if I understand what you have written, is my_part_ID. Where you have put database_name should that not be the table name? – M1 Creative Developer Jun 24 '14 at 8:59
  • @Caleuanhopkins when inserting the record, how someone will get $dbRowId or possibly the primary key value in this case? – Chittaranjan Jun 24 '14 at 10:19
  • @Chittaranjan the variable $dbRowId is just an example variable, to get the id of an inserted row, the $wpdb->insert_id; will return the id of the row just inserted into the DB – Caleuanhopkins Jun 24 '14 at 15:11
  • @M1CreativeDeveloper Yes, I have also updated the code and changed the database name to the table name. Sorry for the confusion. – Caleuanhopkins Jun 24 '14 at 15:19
  • @Caleuanhopkins that variable is absolutely fine. The question says before inserting so how can you have $dbRowId in the update query? – Chittaranjan Jun 24 '14 at 20:33
0
global $wpdb;
$table_user = $wpdb->prefix . 'user';
$PL_part_ID= $_POST['PL_part_ID'];
// first check if data exists with select query
$datum = $wpdb->get_results("SELECT * FROM $table_user WHERE PL_part_ID= '".$PL_part_ID."'");
 if($wpdb->num_rows > 0) {
 echo "result exists";
 }
// if not exist in the database then insert it
else{
$result = $wpdb->insert( 
$table_token, 
array( 
'Firstname' => $_POST["value1"],
'Surname' => $_POST["value2"],
'Month' => $_POST["value3"],
'Day' => $_POST["value4"],
'Email Address' => $_POST["value5"]
)
);
}
  • Please prefer ` $wpdb->prepare()` method. – bravokeyl Sep 23 '16 at 11:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.