Support » Developing with WordPress » Advice on custom table structure

  • Hi,

    I am currently storing my custom data from my plugin within the user_meta table under one key per user, per post.

    The structure looks like:

    custom.postid ->
    custom1 – > value
    custom2 – > value

    etc, with 6 values in total stored in a serialised array for each post.

    I recently began creating a user-facing dashboard to display this info and it was a nightmare pulling all the separate pieces of information and displaying them.

    I have been looking at creating a custom table to house my data with a separate column for each (current) sub-key.

    It seems like a good option for me as querying the data should be much simpler.

    I do have a question though:

    I want to add a new 7th column called “history”. It will stored an array of a user’s transactions with that post:

    date > value
    transaction > value

    This will be appended to each time the user does more transactions.

    I find lots of documentation on creating the table structure but not on how to store and read such data as column 7. What would be your advice?

    Example code to work with:

    function my_plugin_create_db() {
    
    	global $wpdb;
    	$charset_collate = $wpdb->get_charset_collate();
    	$table_name = $wpdb->prefix . 'my_analysis';
    
    	$sql = "CREATE TABLE $table_name (
    		custom1 mediumint(9) NOT NULL AUTO_INCREMENT,
    		custom2 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
    		custom3 smallint(5) NOT NULL,
    		custom4 smallint(5) NOT NULL,
    		custom5 smallint(5) NOT NULL,
    		custom6 smallint(5) NOT NULL,
    		history smallint(5) NOT NULL,
    		UNIQUE KEY id (id)
    	) $charset_collate;";
    
    	require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    	dbDelta( $sql );
    }
Viewing 4 replies - 1 through 4 (of 4 total)
  • Moderator bcworkz

    (@bcworkz)

    I wouldn’t use dbDelta(). It offers some added features, but those same features can get in the way if you want to do certain things. I simply use the various $wpdb methods to do everything. No need to require other files and no “features” to get in the way. The $wpdb->query() method will work for any SQL query if one of the other methods do not do the job. Be sure to use $wpdb->prepare() unless everything in the query is hardcoded.

    Hi Bcworkz,

    Thanks, it wasn’t the code I was hooked on, it was the question.

    How would I modify such a table-creation code to allow colum 7 (history) to accept an array which can be added to with two values: date and transaction?

    The code was just an example to play with.

    MySQL doesn’t store an array in any sort of native way, so you’d either need two columns (one for the key and one for the value), or save the value as a serialised string and un-serialise it when you retrieve it from the database.

    But.. in your case it’s actually a better idea to break that out into another separate table. So, you’d have something like this:

    CREATE TABLE test_table (
      id bigint(20) UNSIGNED NOT NULL,
      analysis_id mediumint(9) NOT NULL
      update_time datetime NOT NULL,
      transaction_value varchar(255) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    That way you can keep the values set, then record each transaction individually for a proper record of any updates.

    • This reply was modified 8 months, 2 weeks ago by catacaustic.

    Ah that’s something I had not considered.

    So table 1 would house:

    userid, custom1, 2, 3, 4, 5, 6

    table 2 would house:

    userid, date, transaction

    Thanks that is definitely worth considering.

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Advice on custom table structure’ is closed to new replies.