This article on creating a CRUD system in WordPress was published back in 2014. However we noticed that this topic became even more actual, so we decided to give it a fresh look.
What is a CRUD system, how to integrate a CRUD system for MySQL in your WordPress site, what are the pros and cons of using different approaches?
Table of contents
What is a CRUD system?
Why would you need a CRUD system in WordPress?
How to integrate a CRUD system in WordPress site?
– The easiest solution – try to find a suitable plugin
– Start a new WordPress plugin
– Create editor back-end (PHP classes)
– Create a front-end interface (HTML, JS, PHP)
– Connect front-end with back-end with AJAX calls (JS)
– Test, refine and debug
What is a CRUD system?
The abbreviation CRUD comes from Create, Read, Update, Delete. Which, in other words, means a data management system. On the web, it would most probably mean a piece of software that manages entries in your database. Usually, it refers to MySQL, PostgreSQL, MS SQL, or other DB engines.
A great, and, maybe, the most popular example of a CRUD system is phpMyAdmin. PHPMyAdmin is a tool that is used by almost every web developer to manage MySQL data online. It became so widely spread so currently it can be called a standard tool for managing MySQL databases. Nowadays it is pre-installed almost on any CPanel tool at hosting providers.
There are thousands of standard use cases for CRUD systems. For example – almost any catalog, entry log, statistical information. Basically, anything related to storing information would require adding, modifying, and removing information bits. That’s when CRUD systems come into play.
Why would you need a CRUD system in WordPress?
WordPress itself is a powerful CMS (Content Management System), which is a special case of CRUD system as well. As you know WordPress site users can add posts and pages, edit or delete them. But sometimes you encounter a situation when you need to manage some database data directly from the WordPress front-end which is generally not posted or page-related and doesn’t fit nicely in WordPress taxonomies. Among others it can be one of these situations when you would need an independent CRUD system in WordPress:
- You would like to have a bug tracker, entry log, or some other data entry tool on your WordPress site;
- Or for example, you want to allow some of the users to edit some business-related data from your site front-end without providing them access to the WordPress site admin panel, or maybe even without disclosing the fact that the site runs on WordPress;
- Another example is a requirement to allow some users to edit a DB table. For example, modify a placed order, edit some personal data, etc.
And similar requests.
How to integrate a CRUD system in a WordPress site?
The easiest solution – try to find a suitable plugin
First – make sure you really need one. Many of the tasks are not unique and you can probably find a plugin that would suit your needs. E.g. if you need a bug tracker, as mentioned above, you can check and download this or this one.
And if you need something more generic, like editing different MySQL tables from the WordPress front-end, try our wpDataTables plugin which is basically a CRUD system in WordPress. You can see the list of features and examples of how it works here. Or even try a free Lite version in the WordPress plugin repository.
It is worth noting that wpDataTables does not impose any limitations on data structure, amount of columns or rows, supports a lot of data types, and different editor input types. For example some specific types like attachments or DateTime. Also, it allows editing data through a popup form editor, with an inline editor or even with a spreadsheet Excel-like editor.
If you still feel you need to build a custom CRUD system in WordPress, you should prepare yourself for some serious coding to get it done.
Start a new WordPress plugin
If you think that no plugin fits your needs, create your own one! It might be not as scary as it seems at first sight. Start by reading this nice tutorial on creating a WordPress plugin from scratch. And also of course this one in WordPress Codex.
First initial step when you create any plugin – including a CRUD system in a WordPress plugin – is to structure the files properly.
The usual approach is to place the main entry CRUD point file in the plugin’s root directory, and to prepare several subfolders:
- Source – folder for the “core” PHP classes that will perform all the CRUD functionality in the back-end;
- Lib – folder for any third-party components you may want to use in your CRUD system;
- Templates – folder for HTML templates that are going to be the user interface for your CRUD system.
There can be more (controllers, shortcode handlers, and others) – but that’s a minimum to start with.
Create editor back-end (PHP classes)
First of all, you’d need the back-end part: a PHP script that would actually do the CRUD jobs. For this, you’d need to connect it with WordPress DB (global $wpdb object). You can read a nice tutorial here about using the WordPress database and $wpdb object in your plugins.
Your task on this step is to prepare a set of classes and method that will accept data from the front-end in some expected form, validate and sanitize it (sanitizing all input is a very important security measure for all CRUD systems), and perform the INSERT, UPDATE and DELETE actions on your database.
As described in the previous step, these “core” files would belong to the “source” folder of your new CRUD WordPress plugin.
Create a front-end interface (HTML, JS, PHP)
When the database management classes and methods are prepared, your plugin would need a front-end interface for the user to with your new CRUD system in WordPress. The best solution would be to prepare a set of templates in your new plugin files and output it wherever you need with a shortcode.
It makes sense to always keep the HTML templates separated from the code (MVC approach), and to prepare logically separated template files, e.g.: “edit.tpl.php”, “delete.tpl.php”, “list.tpl.php”, etc. – a template for each CRUD page or action.
Here you can read a great codex article on WordPress Shortcode API.
Connect front-end with back-end with AJAX calls (JS)
Of course, you can do it “old-school style”, with simple form submits and page reloading. However, nowadays it is not a common approach anymore. Using AJAX is a standard approach instead, either through jQuery, or other libraries like Angular. So our recommendation is to take some time and look into using AJAX in your WordPress plugins, here is a great Codex article on how to use AJAX in your plugins – both admin and front-end side.
You can put the JS code in the “assets” folder that you prepared in the first step.
Test, refine, and debug
When the implementation part is done – take some time and test your new CRUD system in WordPress. You can’t spot all bugs from the start, but repeating CRUD routine actions multiple times with different examples (preferable “edge” cases – e.g. very large amounts of data, clicking multiple times on the same button, etc.) will always help you to catch most of the bugs – and these always happen when you implement something new.
CRUD vs REST: What’s the difference
REST is an architectural style of building networked applications based on a client-server, stateless, cacheable communication protocol, i.e., the HTTP protocol. CRUD is an acronym for CREATE, READ, UPDATE, and DELETE, the basic functions of persistent storage in programming.
CRUD operations, i.e., CREATE, READ, UPDATE, DELETE, are similar to REST basic commands, i.e., GET, PUT, POST, DELETE, which leads to confusion between the two. What is CRUD? What is REST? In CRUD vs REST, what is the CRUD definition? Is REST a mere carbon copy of CRUD?
These are all very pertinent questions that this article intends to answer in detail!
How does REST work?
You can’t understand terms such as REST API, REST services, CRUD matrix, or CRUD database, or REST programming if you don’t understand the difference between the two processes as far as the way they work. The confusion will disappear once you know this difference.
You can work REST on any resource, be it a media file, document, website, etc. There are no restrictions as to what you can work REST on; you can use HTML only as the communication protocol to point out the resources. REST stands for Representational State Transfer.
REST indicates that there each distinctive URL represents some object, which you can get to via an HTTP GET, as well as modify and delete it via HTTP POST, PUT, or DELETE.
How Does CRUD work?
You can only apply CRUD on database records, and you cannot create CRUD APIs like you create REST APIs. CRUD application is restricted to databases, which is why CRUD, unlike REST, is not an architecture style, but a cycle. Apps and websites all contain different CRUD cycles.
E.g., a visitor on an e-commerce website can CREATE an account, UPDATE the account, READ the info, and DELETE the account. That’s a full CRUD cycle that includes every CRUD operation.
E.g., on the same e-commerce website, a visitor can CREATE an item in the e-cart, and then complete the full CRUD cycle by READING, UPDATING, and even DELETING the item.
The Foundation and Principles of REST
The basic commands of Representational State Transfer – the REST acronym – revolve around an object or a resource, which can be described as anything that you can reveal using HTTP protocol. Examples of REST resources: image, website, document, service. The sky is the limit.
REST is an Application Programming Interface, or API, or an architecture intended for distributed multimedia. An API is a web service that follows the principles of REST architecture. Thus, REST calls each API via one of the HTTP request methods, GET, PUT, POST, and DELETE.
The Six Guiding Principles of RESTful Architecture
The client-server mandate emphasizes the fact that REST represents a distributed method that relies on the nature of client-server separation. A REST service entails several capabilities and attends to requests. The client makes the requests, and the server accepts or denies them.
Statelessness restricts the type of requests that can be submitted between consumer and server. In effect, it is the request that initiates client-service communication, where the request holds every piece of info needed for the server to answer back.
The purpose of caching a request is never having to submit the same request twice because caching instructs the server to label responses as cacheable or not. As a result, caching reduces the constraints or restrictions generated by statelessness.
Uniform Contract excludes the use of several independent interfaces inside an Application Programming Interface or API. REST sticks to the principles of a Uniform Contract. Therefore, one REST interface is shared via hypermedia connections.
A Layered System employs several independent layers to develop and extend the interface. As layers cannot see into each other, new requests and middleware can be inserted that will not have an impact on the initial commands and the client-server functioning.
While Client-Server, Caching, Statelessness, Uniform Contract, and Layered System are must-haves for RESTful apps, Code-On-Demand is not mandatory. However, Code-on-Demand allows for logic within clients to remain independent and thus updated separately from server logic.
Ending thoughts on CRUD vs REST
CRUD entails the essential operations performed in static data storage or databases, such as handling passive records or objects. CRUD essentially manipulates basic data.
REST relies on representing resources via unique URLs, where resources are object abstractions, where the comment of a user can be a resource, for example.
As such, REST entails more than a record in a Comment Table. REST is about the record’s relationship with the user resource and the post/comment that it is attached to. REST is a very high-level API style that interacts with a complex system.
Of course, this article was not a complete tutorial – since it would take twenty pages to write one. But do let us know if you have some questions, or would like to see a specific step-by-step example, and we will create one for you.
We hope it was helpful in some way.
Thank you for reading!
Picture source: http://www.tyseo.net