How can i have wordpress create "virtual" pages (or probably better suited: a different custom post type) taken from a DB Query?

The table in question could map out one resource (events in my case) per row. The entities are fed and managed by a legacy application and changes are made every now and then (too often to duplicate the data manually, they would be updated by a script).

I still want to be able to access each resource by a unique permalink and would like to be able to iterate over them (creating archive pages and so on).

I think pods try to achieve something like that with "Advanced Content Types" (http://pods.io/docs/learn/what-are-advanced-content-types/) but this seems to be an overhead.

One could also create just a single page with a given parameter (events/show?slug=myevent1) but this is not SEO-friendly enough for my taste and throws away all the benefits the WP modeling gives us - with archives, metadata and stuff.

My question is not about the "techniques" like custom tables, SQL queries and stuff, rather how to model this behaviour exploiting all the WP stuff already there.


I think you already give the answer: use custom post types: define a custom post type per type coming in.

Some notes for 2 way syncing:

  • metadata property changes dont change the modification date, which might be handy to verify while syncing. Luckily you can add some callback for this e.g.:

Example metadata fields changing > modified date

add_action( "updated_post_meta", array($this, 'updateModifiedDate'), 10 ,4);
                add_action( "added_post_meta", array($this, 'updateModifiedDate'), 10 ,4);
    function updateModifiedDate($post_id, $key, $value) {
                $array = array (
                        'ID' => $post_id,
                        'post_modified' => current_time('mysql',false),
                        'post_modified_gmt' => current_time('mysql',true)
  • It's nice to store the last time you synced in a cache with "unique-key" - "modified" - " some other attributes". This will give you the ones that changed since the last time and new ones on both sides. Apart from this do a realtime match against both to their shared unique key. This information combined gives you the information on new ones on either side and/or CRUDN operations on either side (this gives you 75 different combinations you can check for, of which most result in the same action). (CRUDN) x (CRUDN) x (side1|side2|both)

In this way you can support 2 way syncing. Most can be done automagically, in some cases merges will need manual intervention or choices.

Generic Advice

  • When batch processing in complexer environments: use staging environment: tables that will contain the data to load, possible already transformed for easy loading. Then schedule loads to transform data, map data, etc.. to the production environment e.g. wp custom posts+meta+tax
  • Always log everything in sync logs, so you can always find what happened where, especially when syncs run automagically, put some timers there so you can see what the bottlenecks are, especially with large jobs that take hours (or weeks) to load. Bottlenecks are e.g. "sanitizing" the data which could take 1/3th of the total time.
  • Thanks edelwater. You suggest creating "standard" custom post type posts (instead of using a different table or even database), which will be visible and editable via standard WP toolchain. Fair enough, although I'd prefer having the data in a different table (or even db). Also, I do not have need for two-way-sync (but who knows that might change). Great that you included some tips about that! – Felix May 18 '16 at 17:37
  • If you use the standard custom post (with e.g. custom metadata and/or custom taxonomy) you get a lot of things out of the box e.g. the table list view in the admin screen. Otherwise you will need to create all that stuff yourself. In addition you have to take care of table creation, delta's when columns change etc, caching etc.. etc.. I sync two-way between dropbox and my site currently (written as .ini files) and works like a charm. hierarchical categories translate to dropbox directories, so i can change stuff either in dropbox files with editor or via the gui in the web front end. – edelwater May 18 '16 at 18:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.