How to avoid duplicates in INSERT INTO wordpress

Question

I am trying to insert record into wordpress tables but the problem is, its inserting duplicate records.

This is my sql query

$qv = $conn->query("INSERT INTO wp_posts (post_title, post_content, post_name, post_date, post_date_gmt, post_author) VALUES('$title[$i]', '$description[$i]', '$url[$i]', '$date[$i]', '$postdate[$i]', '$author[$i]') ON DUPLICATE KEY UPDATE post_name = post_name");

I don't want to insert any duplicate records, how to fix this?

score 3 · Accepted Answer · answered May 02 '16 at 08:56

3

create unique key on column and use insert ignore instead of insert like below

  ("INSERT ignore INTO wp_posts (post_title, post_content, post_name, post_date, post_date_gmt, post_author)
 VALUES('$title[$i]', '$description[$i]', '$url[$i]', '$date[$i]', '$postdate[$i]', '$author[$i]'));

answered May 02 '16 at 08:56

Ankit Agrawal

2,426
1
13
27

I am not able to select unique key in wordpress column I mean its not clickable, it seems like the selection is disabled from wordpress – KenDev May 02 '16 at 09:04
ok I am trying to add unique index but its giving me an error, this is the query I am using `ALTER IGNORE TABLE wp_posts ADD UNIQUE (`post_title`,`post_name`);` – KenDev May 02 '16 at 09:18
Also, never execute a query inside a loop. Build the query inside the loop and execute it afterwards. (And see about prepared statements too, but get this working first) – Strawberry May 02 '16 at 09:18
This is the error I am facing, `#1170 - BLOB/TEXT column 'post_title' used in key specification without a key length` – KenDev May 02 '16 at 09:21
Modifying existing columns will break the built-in functionality of wordpress(e.g. revisions,autosave), because post_title and post_name may not be UNIQUE(when you inspect wp_posts you'll see that there are a lot of duplicate post_name and post_title, created by wordpress....wordpress wouldn't be able to insert these columns with a UNIQUE key) – Dr.Molle May 02 '16 at 09:40

undefined_variable · Answer 2 · 2016-05-02T09:37:09.150

1

wp_posts has id as auto increment primary key and your insert query does not have id, hence your on duplicate constraint will not work. If you want to have unique record by post title then you will have to create unique index on it. Unique constraint can be applied to combination of more than one column, if necessary. Also insert ignore will igonre the duplicate records and not update it. You will have to handle it in your application.

Query to add Unique Constraint in MySQL

ALTER TABLE wp_posts ADD CONSTRAINT unique_post UNIQUE (post_title,post_name);

edited May 02 '16 at 09:37

answered May 02 '16 at 09:08

undefined_variable

6,180
2
22
37

`ALTER TABLE table_name ADD CONSTRAINT somename UNIQUE (columnname)` this is the syntax – undefined_variable May 02 '16 at 09:35
`ALTER TABLE wp_posts ADD CONSTRAINT tvp UNIQUE (post_title)` is not working – KenDev May 02 '16 at 09:38
`#1170 - BLOB/TEXT column 'post_title' used in key specification without a key length` its because I am using wordpress ? – KenDev May 02 '16 at 09:40
MySQL can index only the first N chars of a BLOB or TEXT column. So you will have to change the column type to varchar. http://stackoverflow.com/questions/9527583/how-to-make-a-mysql-field-unique – undefined_variable May 02 '16 at 09:46
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/110811/discussion-between-kendev-and-undefined-variable). – KenDev May 02 '16 at 09:55

score 1 · Answer 3 · answered May 02 '18 at 09:49

Disallow Duplicate Post Using Titles

function disallow_posts_with_same_title($messages) {
    global $post;
    global $wpdb ;
    $title = $post->post_title;
    $post_id = $post->ID ;
    $wtitlequery = "SELECT post_title FROM $wpdb->posts WHERE post_status = 'publish' AND post_type = 'post' AND post_title = '{$title}' AND ID != {$post_id} " ;

    $wresults = $wpdb->get_results( $wtitlequery) ;

    if ( $wresults ) {
        $error_message = 'This title is already used. Please choose another';
        add_settings_error('post_has_links', '', $error_message, 'error');
        settings_errors( 'post_has_links' );
        $post->post_status = 'draft';
        wp_update_post($post);
        return;
    }
    return $messages;

}
add_action('post_updated_messages', 'disallow_posts_with_same_title');

How to avoid duplicates in INSERT INTO wordpress

3 Answers3