Support » Fixing WordPress » 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)
  • 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

    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’.

    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.

    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) 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.

    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.