Tutorial - Custom Database Tables

Topic Description

While the default WordPress database schema is perfect for most content-related data types, there may be instances where you need to create custom tables in a WordPress database. One example of this is when a plugin has a specific use case that extends beyond what the custom post-type functionality of WordPress offers. In this tutorial, you will learn how to create and manage custom WordPress database tables, how to interact with these tables, and some maintenance considerations.

Related Resources

Links to related content on Learn, HelpHub, DevHub, GitHub Gutenberg Issues, DevNotes, etc.

Custom tables in WordPress online workshop

Guidelines

Review the team guidelines

Tutorial Development Checklist

You might consider making the title more specific - such as Custom Database tables - so that it is not confused with html tables, the table block in site content or selecting a table-specific plugin. Or direct it toward developers rather than users.

@SierraTR

That's a good point @SierraTR, edited.

WIP Script - https://github.com/jonathanbossenger/wp-content/blob/main/tutorials/fundamentals-02-custom-tables.md

WIP video for review:

Custom.Database.Tables.mp4

In preparing this video for review, I realized there are two fixes I need to make

at 5:06, in the example code to update the record, the ID field should be id
at 6:37 I say plugin install not plugin uninstall

I will make sure to fix those before the video is published.

@jonathanbossenger

Hello @jonathanbossenger ,

I thoroughly enjoyed your tutorial and here is what I noted during the video:

You clearly and concisely met all objectives in this video including Where to find information about creating custom database tables, How to create custom database tables, and How to interact with these custom database tables.
Your example code and the resources mentioned in the video were very helpful in understanding the process of creating custom database tables. I was easily able to follow along with your explanations and points
At the end of the video, you quickly reviewed table updates. I appreciate that you mentioned this process.

Great tutorial and I wouldn't change a thing about it other than the two items you mentioned above.

@jonathanbossenger

Great tutorial @jonathanbossenger my only question is why did the custom table docs not get migrated from codex?

@Zebedeu

Thanks for your review @Zebedeu

It is important to emphasize and explain to the user that certain practices were used only to illustrate a concept, but are not recommended.

I'm just checking, do you feel that this needs to be added, or that this idea is already covered in the tutorial?

Yes, this information is important and should be included in the tutorial. It is essential to emphasize to the user that when creating custom tables in WordPress, it is crucial to version them to control and update the table structure if necessary. Additionally, it is important to note that this technique allows for updates to be applied consistently without compromising the integrity of the stored data in the table. Therefore, versioning is a valuable and indispensable resource for managing custom tables in WordPress.

@Zebedeu

@Zebedeu thanks for your feedback.

As I mentioned to another reviewer in an earlier comment I do think that a follow-up tutorial on custom tables would be good here:

So I do want to dive more into custom tables in the future, including something about how one might manage the actual upgrade routine, what to do if queries fail, etc.

The problem exists in balancing the limitation of creating a tutorial video 5-10 minutes long, keeping the content engaging and easier to follow for viewers, without trying to rush through all the available information about a specific topic.

Quite honestly, the topic of custom tables could be a series of tutorials, even a short course on the subject.

So I just want to make sure I understand your specific review feedback here, as I think there are two action items:

explain to the user that certain practices were used only to illustrate a concept, but are not recommended.
add details on table versioning

Would you agree that a follow-up tutorial (or tutorials) including things like versioning, what to do if queries fail, and other topics related to custom tables would make sense?

Additionally, if you're interested, this might be content you or another contributor might be interested in working on.

@ryanwelcher

@ryanwelcher for the "if table exists check" on inserting data, would you be happy with using get_var and a SHOW TABLES MySQL statement?

    function insert_record_into_table(){
        global $wpdb;

        $table_name = $wpdb->prefix . 'custom_table';

        $table_exists = $wpdb->get_var( "SHOW TABLES LIKE '$table_name'" );

        if ( $table_exists ) {
            $wpdb->insert(
                $table_name,
                array(
                    'time' => current_time( 'mysql' ),
                    'name' => 'John Doe',
                    'text' => 'Hello World!',
                    'url'  => 'https://wordpress.org'
                )
            );
        }
    }

$table_exists would either be the table name, or NULL if it doesn't exist.

for the "if table exists check" on inserting data, would you be happy with using get_var and a SHOW TABLES MySQL statement?

Explaining the why here is probably more important than the implementation but that looks great to me and demonstrates the steps perfectly.

@ryanwelcher

Thanks @ryanwelcher.

I'm wondering now if this item, as well as the items about when to create tables and when to delete them, should be in the follow-up tutorial.

What I'm thinking is:

This tutorial = the basics of creating, inserting data, updating, and deleting custom tables (aka the theory)

Follow-up tutorial = how you would actually implement this with real-world examples (aka in practice)

Adding tables on plugin activation and removing them only on uninstall
Using the table exists check on inserts and updates. Thinking about this more, running a table exists check every time you insert or update data isn't super performant, maybe an option stored once the table is created is better, although this doesn't solve the problem if the table is deleted some other way. That being said...
Implementing proper checks for all queries, with error output to the user if any of them fail. This would prevent the problem at 2) because then at least it can fail gracefully and report an error.
When, and how, to use prepare with query for a custom insert or delete (although I can't think of a real-world example at this time)
How to implement the upgrade routine, to manage table updates

This would be similar to another two-part series I did recently on plugin vulns:

I'm thinking of this in terms not only of being able to create courses based on this content, where one small step follows the next, but also in terms of the learning pathways project.

Thoughts?

@jonathanbossenger

@jonathanbossenger that sounds like a good approach to me. It would be very easy to overwhelm the user with all of the nuances in a single tutorial and splitting them out mitigates that and allows you to spend more time on each.

@ryanwelcher

Thank you @ryanwelcher I appreciate your experienced feedback on this topic.

https://learn.wordpress.org/tutorial/custom-database-tables/

Update hacktoberfest-2023.md

Update hacktoberfest-2023.md (#1939)

Navigation Menu

Repository navigation

Tutorial - Custom Database Tables #1867

Topic Description

Related Resources

Guidelines

Tutorial Development Checklist

13 remaining items

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Tutorial - Custom Database Tables #1867

Description

Topic Description

Related Resources

Guidelines

Tutorial Development Checklist

Activity

SierraTR commented on Sep 19, 2023

jonathanbossenger commented on Sep 20, 2023

jonathanbossenger commented on Sep 26, 2023

jonathanbossenger commented on Sep 26, 2023

TinaCollier commented on Sep 26, 2023

digitalchild commented on Sep 27, 2023

13 remaining items

jonathanbossenger commented on Oct 3, 2023

Zebedeu commented on Oct 3, 2023

jonathanbossenger commented on Oct 3, 2023

jonathanbossenger commented on Oct 3, 2023

ryanwelcher commented on Oct 3, 2023

jonathanbossenger commented on Oct 3, 2023

ryanwelcher commented on Oct 5, 2023

jonathanbossenger commented on Oct 5, 2023

jonathanbossenger commented on Oct 13, 2023

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions