Adding custom tables to WordPress

I had something similar recently. The context was a store but the model is fairly typical.

To greatly simplify, there was a table for Products and another for Serial Numbers (distributed when purchase was completed) to be imported or linked.

At first I tried to make Custom Post Types (CPT) fit the model for both. It was fairly easy for Products – I used WooCommerce (Products was the CPT). To get the data into WordPress/WooCommerce I first mucked around with SQL then bought a plugin called WP All Import (plus the WooC addition) that made the process much easier. This plugin has options for importing just about anything into WordPress as an import. Dump your table as XML or CSV, line up the fields and it does the rest.

Advantage of using a custom post type (be it Products or some other type): users can easily “create, read, update and delete any of the rows in the database tables.”

So that would solve your problem, if a CPT worked for all your data. Creating CPTs is easy, and there are many great plugins that make it a lot easier still. You could then import your data using the plugin above (or if you’re a SQL wizard, try that!)

However, I tried the same with my Serial Numbers table – no cigar. In short, it just wasn’t a “Post”. They’re almost like metadata. So then I tried bringing them into WordPress and metadata for Products. But it was just too complex. The Serials needed to be assigned to Products and Orders when purchased. Also, having them in a discrete data set, makes it much easier to manage them if something needs manual or bulk fixing.

So, for the serial numbers table, I created a custom table in WordPress and used that. This works fine. I did it all manually and it was (a) a great learning experience and (b) a complete chore. If I was going to do it again, I’d use a plugin and spend the time doing other things. There are many plugins that can help. If you want to kill two birds with one stone, look at Pods – it can create CPTs and it can create custom tables for you. I’m pretty sure it allows you to allow your users to “create, read, update and delete any of the rows in the database tables” too – so it might be the fastest easiest route.

If you’re going to go the “manually create table” route and build an interface for your users, here are a few starting points:

Creation: http://www.paulund.co.uk/creating-custom-tables-wordpress-plugin-activation

Working with data: http://www.makeuseof.com/tag/working-custom-database-tables-wordpress/

Related Posts:

  1. How to import a custom MySQL DB table into a WordPress custom post type
  2. Create Pages from Database
  3. How do I write this SQL statement for Posts written in last 24 hours?
  4. wpdb custom post_type problem
  5. Assign External Database Queries to Global Variables and Make Them Accessible
  6. Function causing the loop display posting more (empty)
  7. I’m trying to create a custom version of the wp_list_authors function that includes custom post types
  8. when looping through custom post data not appearing
  9. Custom post type and custom taxonomy key lenght & query performance
  10. Populate custom post type/custom fields from an external database
  11. Inconsistent temporary 404s on whole install | PHP error with post-template.php
  12. Search that will look in custom field, post title and post content
  13. Load custom posts with same tag as page
  14. Date archives for custom post type
  15. modify all posts of a category to “no comments allowed”
  16. If featured image doesn’t exist, show post content
  17. How can I add dropdown widget/box to admin post page?
  18. How to Join two tables from separate databases within WordPress
  19. Meta Query “IN” doesn’t work with ACF checkbox filter
  20. How do I redirect all 404 errors of a specific post type to another URL?
  21. Using ‘strtotime’ function to convert a custom-meta-box to a date-stamp
  22. How to save the checked boxes?
  23. How to get only one category of custom post type?
  24. Error after deleting Custom Post Type with a function (no trash used)
  25. Using posts and postmeta table to store custom Address Book Plugin data
  26. Import 10,000 Users into WordPress WITH a specific ID for each user
  27. Append taxonomy terms as class names in markup?
  28. Unusable menus and “Illegal widget setting ID: nav_menu_item[]” error
  29. Change message given when deleting post from custom post type
  30. Using WPAlchemy metabox values in another metabox
  31. Recent comments from my blogs only
  32. Calculating Bayesian average for custom post type
  33. Display custom field of specific post where post title matches variable
  34. GravityForm: Populate Dropdown with custom post type [closed]
  35. How to output wordpress custom tags separated by comma?
  36. Show Different Header on a Specific Post ID
  37. Admin notice not displaying
  38. Using wordpress template tags within an array
  39. Getting the URL of the parent page
  40. get_posts of Custom Post Type AND Custom Taxonomy
  41. If post has custom field then display css-class
  42. CPT + CMB2: data not displaying for only first post in loop
  43. Replace text in post from cvs
  44. Get parent categories of custom post type
  45. Custom Tag Description unable to display just below and outside of the Loop
  46. Database to page routing API?
  47. Can one create multiple Custom Post Types with a for loop?
  48. How to add custom field to view-edit post type page
  49. Static apple-app-site-association file with wordpress
  50. Breaking up CPT output into two columns
  51. Not connecting to database in file with multiple MySQL connections
  52. how to echo meta tag in header using php
  53. PHP question: how to combine syntax?
  54. Add first letter of titles to array, then compare arrays
  55. Replace dynamically content in a custom database table when a custom post is created
  56. Save and user submitted data from a form and display them in the wp backend
  57. How do I add custom HTML to the content of an archive page’s posts?
  58. widget_posts_args not using the number of posts in widget
  59. How To Post WordPress Custom Post Types to Twitter via IFTTT
  60. Undefined $post in wp_query
  61. Update all published posts at once
  62. Importing JSON feed giving Notice: Array to string conversion error
  63. List latest post out of 2 custom post types
  64. New post from database
  65. Ideas to connect external database table to wordpress posts as custom field source
  66. custom post type: own database table structure and custom “new-post” interface
  67. Fetch data from two custom post types and create multidimensional array for output to html table
  68. Create a Dropdown Selector and Redirect for a Custom Taxonomy in WordPress?
  69. Having a repeating custom field in admin custom post type, what I’d go better with, for DB’s sake? ACF repeater or query a different post type?
  70. Create an user checklist system for a course plateform using ACF Pro and ACF Extended
  71. How to get the custom post data using post id in WordPress?
  72. See if the email exists or not
  73. On saving Post My permalink is updated and category is removed automatically which cause the 404
  74. How to add specific terms in a custom post type?
  75. Limit custom post type to the authors only on front-end!
  76. WP_Query – How To Query Only Custom Posts With No Children of Their Own?
  77. programmatically generated custom post type is created more than once
  78. Using wpdb to connect to a different database is not working
  79. Accessing download link from the loop with WP Download Manager Pro
  80. Create posts inside CPT post
  81. Taxonomies are not showing in the category dropdown
  82. Combine multiple separate lists into one
  83. Custom Taxonomy – fields
  84. Insert data from form to database
  85. Print post category name from a custom post type wordpress
  86. CPT post doesnt shows tags and category on backend admin page
  87. How to make sure, that only the selected post is changing?
  88. Automatically convert standard posts with custom fields to custom post types
  89. Create 2-layered dropdown menus for custom taxonomy and custom post type
  90. Get month and day from a Date Picker custom field
  91. two wordpress sites, two themes, one database, same content
  92. Show Custom Post Type taxonomy term that matches custom field
  93. custom post type for custom talbe
  94. Select Menu for Custom post Type does not save
  95. MySQL database migration to WordPress
  96. is_singular won’t call my functions?
  97. Custom query_posts() parameter
  98. Inserting two categories
  99. Comments are not working on Custom Post Type
  100. Display total count of products in orders of a specific order status