Custom relational database in a WordPress installation

  • hamstair_toilichte

    (@hamstair_toilichte)


    8 years, 9 months ago

    Hello all

    This isn’t so much a ‘how to’, as a request to be pointed to roads to go down to get to my destination. In brief, my query is: how can I create a relational database in the WP database, and use forms to populate and query it?

    In more detail:

    I’m after creating a links ‘repository’ for subjects (eg healthcare, languages), which would be a multi-table relational database. For instance, I developed the Global Health repository as a custom web app, using PHP, MySQL and Codeigniter at the back end. Contributors add a web resource by URL, name, keywords, topic and a few other criteria, and this data goes into a MySQL database with around ten tables, linked by foreign keys or junction tables.

    What I’d like to do is write a plugin to enable such a repository in WordPress. An example of a repo I’d like to transfer into WP is a healthcare resources database. I’d like to do this for the plugin writing exercise, to contribute a little back to the community, and to provide a simple tool for the user to create their own link repo in whatever subject they want. The end user should be able to search/browse for resources in that subject via a simple form.

    So I may want to add custom tables to the WP database and relate them as I want. I’m not sure, though, about the best way of approaching this. I’m thinking that I could maybe use the excellent Advanced Custom Fields plugin with some Custom Post Types, and use the dbDelta() function to create tables on plugin activation as advised in this article.

    Alternatively, perhaps I shouldn’t mess about with the WP database, but rather create a separate DB and run queries on that from within WP.

    There could be a number of roads to go down, but I’d prefer to avoid those that may lead to dead ends, hence this rather fluffy query to the WP community. How would youse go about doing this sort of thing? Am I duplicating the work of an existing plugin? (I did search the plugin repo under various keywords but got 00s of hits.) Am I barking up the wrong tree completely?

    If this post should go into the Plugins forum, let me know and I’ll repost it there.

Viewing 8 replies - 1 through 8 (of 8 total)
  • RossMitchell

    (@rossmitchell)

    8 years, 9 months ago

    This is quite a suitable forum for this type of query.

    I have several applications which do what you propose. One is a Genealogy Wiki. Another administers contests with judges entering scores and comments, entrants receiving reports. Yet another runs self-assesment surveys.
    There are several ways of structuring these applications, they cover the range of plugins, child theme customisations and custom page templates.

    Inevitably there will be some impact upon your theme as you will need css customisations to display your pages, so use a child theme and keep your css customisations in one place.
    You may need to add some customisations to your theme’s “functions.php” file, another reason why you should use a child theme.

    While it is possible to access another database, it is simpler to put your custom table into the same database as runs the WordPress, I use a different table prefix to keep the demarcation clear.

    I put most of the code into a plugin, it registers shortcodes so that I can activate custom functions in my pages and posts. Other times it is best to put page specific code into the custom page template, it can also call functions in the plugin. Again page templates live in the theme.
    Docs on shortcodes here:
    http://codex.wordpress.org/Shortcode_API

    Keep an eye on mixing your custom functionality into plugins and theme space, if discipline along these lines is not maintained it can get messy.

    Accessing the WordPress database is exceedingly easy, be aware that best practice requires you to be defensive in your database interactions when you apply user data into updates, protect yourself and use “prepare” at every instance. Database details here:
    http://codex.wordpress.org/Class_Reference/wpdb
    You could use it to create another connection to your separate database, only do this if you really need to.

    “Advanced Custom Forms” could well find a place in your solution, suggest that for the first round of development you hardwire your forms and only add the flexibility of ACF when it really adds value.

    Have fun.

    Thread Starter hamstair_toilichte

    (@hamstair_toilichte)

    8 years, 9 months ago

    Thanks, Ross, that’s very helpful and much appreciated. I’ll take your excellent advice on board. Is your Genealogy Wiki in the WP Plugins Directory? I wouldn’t mind seeing how you’ve coded it as that might save me a bit of time coding from scratch, as I’m pretty new to plugin development.

    RossMitchell

    (@rossmitchell)

    8 years, 9 months ago

    Is your Genealogy Wiki in the WP Plugins Directory?

    I am afraid not.
    When I started I loaded some plugins that did forms and database access.
    This was one of them: wp-csv-to-db plugin by Ruhul Amin
    Also this will be useful:
    http://codex.wordpress.org/Class_Reference/wpdb
    And please to protect yourself learn how to use the “prepare” function to scrub your user input.

    And remember “Google is your friend”

    jblumenthal

    (@jblumenthal)

    8 years, 5 months ago

    I’ve heard about the PODS plugin but haven’t looked at it. On the surface it appears to be a way of creating a relational database, but I’d have to check it out before making a statement.

    Moderator bcworkz

    (@bcworkz)

    8 years, 5 months ago

    @jblumenthal – thanks for the thought about PODS. You are correct, it can be used to create a relational database. It’s nice because it allows non-coders to do so through a familiar admin interface.

    However, I’m leery of installing many plugins just because I can. For a coder, creating a relational database is rather trivial. Properly designing one is much more involved, but coding it is straight forward. Since this sub-forum’s main intended audience are coders of all levels, I’m not convinced PODS is really necessary.

    There also remains the issue of user interface, IMO PODS shortcodes leave much to be desired for data output. They are basically a coding language of their own. A custom template is not that big a stretch for anyone that codes.

    PODS is great for those that cannot code or cannot be bothered, but from the sounds of the OP’s need and apparent skill level, a bespoke plugin will be far more satisfactory and efficient than cobbling together several plugins.

    Still, PODS is a good thought and will be useful for someone.

    jblumenthal

    (@jblumenthal)

    8 years, 5 months ago

    Thanks for the info, and I’m glad to see you use the word “properly” in database design. When a system is screwed up it is more likely than not in the design and everything goes downhill after that.

    Our local WordPress Group (Chicago) is having a PODS session at our next meeting so I’ll have a chance to see and hear a bit more. The ability to extend WordPress is an interesting topic and I’d like to learn more about how the pros like yourself approach it.

    BTW: I’m 75-years old and have been coding stuff for a very long time. I expect my tombstone to read, “He knew FORTRAN.”

    celeste21

    (@celeste21)

    8 years, 2 months ago

    @jblumenthal I would be interested in any follow up from your session on PODS.
    I am a non-coder wanting to set up wordpress to reflect my relational database.

    Many thanks!

    jblumenthal

    (@jblumenthal)

    8 years, 2 months ago

    It was a good discussion with the general feeling that PODS was pretty much a work in progress. The hard core database coders all stuck to their hard coding, and I would have to admit that they did make a case based on efficiency, etc., especially if the database design is fixed.

    Experience however, has taught me that the real task in database applications is in the design stage and rigorous prototyping pays off. If you are indeed a non-coder, then PODS may be a perfectly good tool for doing your development. I took a look at the internals for MySQL in php and it doesn’t look all that foreboding. My recommendation would be to go ahead and try PODS for your prototype. If performance becomes an issue you can address it later.

Viewing 8 replies - 1 through 8 (of 8 total)
  • The topic ‘Custom relational database in a WordPress installation’ is closed to new replies.