Title: INSERT INTO inserts duplicate entries Last modified: August 20, 2016 --- # INSERT INTO inserts duplicate entries * [g01010](https://wordpress.org/support/users/g01010/) * (@g01010) * [14 years, 8 months ago](https://wordpress.org/support/topic/insert-into-inserts-duplicate-entries/) * I have been playing with this for hours and can’t seem to make any headway. I’ve searched but haven’t found a solution. * I’ve created a new table – wp_trackingdata. Every time I try to insert a new row, I find that two new rows have been inserted. * This is my code: * ``` global $wpdb; $table_name = $wpdb->prefix . "trackingdata"; $sql = $wpdb->prepare( "INSERT INTO 'wp_trackingdata' ( user_id ) values ( %d )", 2 ); $wpdb->query($sql); ``` * Note that where it has apostrophes around wp_trackingdata in the code above, there are actually backticks in my query. There seem to be a couple of anomalies in this area: - Only placing backticks round wp_trackingdata in the query seems to work. If I use a straightforward apostrophe or quote mark, no new row gets inserted - If I replace `wp_trackingdata` in the query with $table_name then no new rows get inserted * My table looks like this: * ``` $sql = "CREATE TABLE " . $table_name . " ( id mediumint(9) NOT NULL AUTO_INCREMENT, user_id bigint(20) NOT NULL, site_id bigint(20) NOT NULL, page_number bigint(20) NOT NULL, extended_data longtext NOT NULL, time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, course_status tinytext NOT NULL, UNIQUE KEY id (id) );"; ``` * If anyone can see where I’m going wrong, I’d be really grateful to hear. Viewing 5 replies - 1 through 5 (of 5 total) * [Chris Klosowski](https://wordpress.org/support/users/cklosows/) * (@cklosows) * [14 years, 8 months ago](https://wordpress.org/support/topic/insert-into-inserts-duplicate-entries/#post-2343335) * You may want to try using the ‘insert’ method from the $wpdb class. It would look something like this: * ``` global $wpdb; $table_name = $wpdb->prefix . "trackingdata"; // Associate array of column_name => value for items to insert $insert_array = array('column_one' => 'string', 'column_two' => '123'); // Non-Associative array corresponding to the // items to insert above, %d for decimal and %s for string $insert_formats = array(%s, %d); $wpdb->insert($table_name, $insert_array, $insert_formats); ``` * You can see the codex section concerning inserting rows here: [http://codex.wordpress.org/Class_Reference/wpdb#INSERT_rows](http://codex.wordpress.org/Class_Reference/wpdb#INSERT_rows) * Thread Starter [g01010](https://wordpress.org/support/users/g01010/) * (@g01010) * [14 years, 8 months ago](https://wordpress.org/support/topic/insert-into-inserts-duplicate-entries/#post-2343456) * Interestingly, I was using the insert method to begin with – but no record was getting inserted at all. Moving to INSERT INTO meant that records were getting inserted twice. * I went back and recreated the table like this: * ``` $sql = "CREATE TABLE " . $table_name . " ( id mediumint(9) NOT NULL AUTO_INCREMENT, blog_id mediumint(9) NOT NULL, user_id mediumint(9) NOT NULL, time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, status tinytext NOT NULL, extended_data text NOT NULL, UNIQUE KEY id (id) );"; ``` * Now it works fine using the ‘insert’ method so it looks like there was a problem with the way I’d set up the table initially. * I’m still interested to know what the pros and cons are of the insert method against INSERT INTO. I have read that using the latter with ‘prepare’ helped protect against injections but I don’t know if this is not also true of ‘insert’. * Thread Starter [g01010](https://wordpress.org/support/users/g01010/) * (@g01010) * [14 years, 8 months ago](https://wordpress.org/support/topic/insert-into-inserts-duplicate-entries/#post-2343471) * I take it back. I’m still getting two records added to the table even though I only run the function once. I’ve got: * ``` function do_insert_data() { $rows_affected = $wpdb->insert( 'wp_table_name', array( 'time' => current_time('mysql') ); } add_action('wp_footer', 'do_insert_data'); ``` * The function isn’t getting called from anywhere else yet every time I load a new page, two identical records get added to the table when I would only expect one. * I also have this for when the plug in is activated: * `register_activation_hook(__FILE__,'do_insert_data');` * This works correctly and only inserts one line of data. I am puzzled. * [Chris Klosowski](https://wordpress.org/support/users/cklosows/) * (@cklosows) * [14 years, 8 months ago](https://wordpress.org/support/topic/insert-into-inserts-duplicate-entries/#post-2343493) * This would lead me to believe that something is calling ‘wp_footer’ (or something like this). What theme are you using? * On the note of the query method vs the insert method, according to the Data Validation article ([http://codex.wordpress.org/Data_Validation#Database](http://codex.wordpress.org/Data_Validation#Database)) the Insert method should be given un-escaped data, as the method will escape it for you. Someone correct me if I am wrong please. * I prefer to use the insert and update methods because it’s easier for me to identify what I’m doing at a quick glance. * Thread Starter [g01010](https://wordpress.org/support/users/g01010/) * (@g01010) * [14 years, 8 months ago](https://wordpress.org/support/topic/insert-into-inserts-duplicate-entries/#post-2343509) * In the end I recreated the table with a primary and unique key. This has prevented any duplication: * ``` $sql = "CREATE TABLE " . $table_name . " ( track_id mediumint(9) unsigned NOT NULL auto_increment, user_id mediumint(9) NOT NULL, blog_id mediumint(9) NOT NULL, page_id mediumint(9) NOT NULL, time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, status tinytext NOT NULL, extended_data text NOT NULL, PRIMARY KEY (track_id), UNIQUE KEY user_id (user_id) ) $charset_collate;"; ``` Viewing 5 replies - 1 through 5 (of 5 total) The topic ‘INSERT INTO inserts duplicate entries’ is closed to new replies. ## Tags * [database](https://wordpress.org/support/topic-tag/database/) * [wpdb](https://wordpress.org/support/topic-tag/wpdb/) * In: [Fixing WordPress](https://wordpress.org/support/forum/how-to-and-troubleshooting/) * 5 replies * 2 participants * Last reply from: [g01010](https://wordpress.org/support/users/g01010/) * Last activity: [14 years, 8 months ago](https://wordpress.org/support/topic/insert-into-inserts-duplicate-entries/#post-2343509) * Status: not resolved ## Topics ### Topics with no replies ### Non-support topics ### Resolved topics ### Unresolved topics ### All topics