$wpdb->insert produces “ Duplicate entry '0-0&

Question

I'm writing a plugin and trying to insert a new row into the wp_term_relationships table inside of a foreach loop. I know the variables have values because of a var_dump, but for some reason, I'm getting an error consistently. This shows up about 600 times on the show_errors() function:

WordPress database error: [Duplicate entry '0-0' for key 1] INSERT INTO wp_term_relationships (object_id,term_taxonomy_id,term_order) VALUES ('','','')

My Code:

foreach ($cb_t2c_cat_check as $values) {
        global $wpdb;
        $prefix = $wpdb->prefix;

        $table = $prefix . 'term_relationships';
        $object_id = $values->object_id;
        $taxo_id = $values->term_taxonomy_id;
        $num_object_id = (int)$object_id;
        $num_taxo_id = (int)$taxo_id;

        //var_dump($num_object_id); //This produces values, so why are they not getting inserted into the table?
        //var_dump($num_taxo_id); //This produces values, so why are they not getting inserted into the table?

        $wpdb->insert( 
            $table, 
            array( 
                'object_id' => $num_object_id, 
                'term_taxonomy_id' => $num_taxo_id,
                'term_order' => 0
                ), '' 
            ); 

        //$wpdb->show_errors();
        //$wpdb->print_error();
        }

Answer 1

As for why it does not work: do not set third parameter of $wpdb->insert to empty string. It formats every field accordingly..

What it does now is equivalent to:

$wpdb->insert($table, array(
            'object_id' => sprintf('', $num_object_id), 
            'term_taxonomy_id' => sprintf('', $num_taxo_id),
            'term_order' => sprintf('', 0)
));

If you really want to set third parameter you should do:

$wpdb->insert($table, array(
            'object_id' => $num_object_id, 
            'term_taxonomy_id' => $num_taxo_id,
            'term_order' => 0
), array('%d', '%d', '%d'));

---

As for error: wp_term_relationships table has a unique primary key on (object_id, term_taxonomy_id). This means that you cannot have two rows in that table which have both same object_id and term_taxonomy_id.

Though this has happened because by setting third parameter of insert to empty string, you are trying to insert rows with object_id=0 and term_taxonomy_id=0 over and over again.

Answer 2

The answer above was correct in that the database needs to have unique keys and cannot insert a row where the key-value pair already exists, and the format of each new value needs to be set. In addition, specific to Wordpress, there was a problem I wasn't addressing, specifically dealing with the term_taxonomy table and updating the count.

First it's important to note that the plugin was designed to update certain categories for posts in the term_relationships table. This was actually accomplished using the $wpdb-> insert method. However, my test for determining whether the plugin actually inserted new rows in the term_relationships table was not to look at the table directly, but to go to the Wordpress dashboard, select categories, and see if the number of posts with that category was more than before. This didn't work, because the plugin never updated the count in the term_taxonomy table. I only discovered this by clicking 'view' next to a category in the Wordpress dashboard and seeing that there were multiple posts with that category, even though the official Wordpress "count" said there were none.

I confirmed that the term_taxonomy table, the 'count' column, needed to be updated as well by going straight to the database and putting WHERE = 'term_taxonomy_id' in the statement. Sure enough, there were over 1700 results, even though Wordpress thought there were none.

Lesson: Confirm the $wpdb->insert method is working by using PHPMyAdmin, not necessarily relying on the Wordpress dashboard.

With a few modifications, the code now works great. Here's an example:

foreach ($cb_t2c_objects as $values) {
        global $wpdb;
        $prefix = $wpdb->prefix;

        $table = $prefix . 'term_relationships';
        $object_id = $values->object_id;
        $taxo_id = $values->cat_taxo;
        $num_object_id = (int)$object_id;
        $num_taxo_id = (int)$taxo_id;

        //Need to check to see if row exists for each, if not, then insert.
        $cb_t2c_get_row = $wpdb->get_row("
            SELECT * 
            FROM ".$prefix."term_relationships
            WHERE object_id = ".$num_object_id." AND term_taxonomy_id = ".$num_taxo_id."
            GROUP BY object_id, term_taxonomy_id
        ", OBJECT);

        //var_dump($cb_t2c_get_row);

        if ( is_null($cb_t2c_get_row) ) {
            //Insert the new values.
            $wpdb->insert( 
            $table, 
            array( 
                'object_id' => $num_object_id, 
                'term_taxonomy_id' => $num_taxo_id,
                'term_order' => 0
                ), 
            array(
                '%d', 
                '%d', 
                '%d'
                ) 
            );
        }

        //Set the variables for the count update.
        $cb_t2c_term_taxonomy_table = $prefix . 'term_taxonomy';
        $cb_t2c_update_data = $wpdb->get_var("
            SELECT count(term_taxonomy_id) as 'new_count'
            FROM ".$prefix."term_relationships
            WHERE term_taxonomy_id = ".$num_taxo_id."
        ",0,0); //returning NULL

        //var_dump($cb_t2c_update_data);

        //Update the count in the term_taxonomy table.
        $wpdb->query("
            UPDATE ".$prefix."term_taxonomy
            SET count = ".$cb_t2c_update_data."
            WHERE term_taxonomy_id = ".$num_taxo_id."
        ");