WordPress.org

1. g01010
   Member
   Posted 7 months ago #

   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.

2. cklosows
   Member
   Posted 7 months ago #

   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

3. g01010
   Member
   Posted 7 months ago #

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

4. g01010
   Member
   Posted 7 months ago #

   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.

5. cklosows
   Member
   Posted 7 months ago #

   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.

6. g01010
   Member
   Posted 7 months ago #

   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;";

Reply

You must log in to post.