Interacting with custom tables

[jonathanbossenger]

Details

• Content type (Online Workshop, Lesson, Course, Tutorial, or Lesson Plan): Lesson
• Content title: Interacting with custom tables
• Topic description: Querying against custom a custom table, including proper usage of wpdb, sanitizing queries for inserts and updates, etc.
• Audience (User, Developer, Designer, Contributor, etc.): Developer
• Experience Level (Beginner, Intermediate, Advanced, Any): Intermediate

Prerequisites

It is assumed that the learner has already completed the following lessons:

• Beginner WordPress developer
• Modules 1 - 6

Learning Objectives

• Describe how and when to add tables to the database and remove them from the database
• Explain how to query the custom table (select, insert, update, delete)
• Demonstrate proper methods to prepare queries, as well as checks in case the data/table doesn't exist

Related Resources and Other Notes

• https://developer.wordpress.org/plugins/creating-tables-with-plugins/
• Tutorial - Custom Database Tables #1867 (comment)

Automation Code

//lesson

[github-actions]

Lesson Development Checklist

• Gather any relevant links to Support, Docs, or related material
• Description and Objectives finalized
• Lesson created and announced to the team for review
• Lesson reviewed
• Lesson video submitted and published to WPTV
• Lesson created on Learn.WordPress.org
• Lesson video published to YouTube
• Lesson on Learn.WordPress.org updated with YouTube video
• Lesson published to Learn.WordPress.org

[MichelleBlanchette]

@jonathanbossenger Can you please assign this to me? I hope to start working on this script's draft sometime later this week or weekend. 🙂

[MichelleBlanchette]

Quick update from this weekend

I have started writing this lesson, Module 07: Lesson 02. It's taking me more time than I anticipated because it seems like such a simple lesson, I'm struggling to find words to say. HA! Also resisting the urge to just copy sections from wpdb's documentation. 🫠 However, I looked around a bit more and realized I could spend a good amount of time discussing SQL query security and best practices.

I also chose to introduce the wpdb class and global instance over in Lesson 01 instead. That's helping this lesson now have a better flow.

My branch with the working draft is in my fork here: https://github.com/MichelleBlanchette/learn-wp-content/blob/module-07-custom-database-tables--lesson-02/plugin-developer-learning-pathway/07-Custom-Database-Tables/02-Interacting-with-custom-tables.md

I'm planning to then explain the simple acts of inserting, updating, deleting, and querying data within the custom table. I expect to have a full draft for this lesson ready for review by the end of next weekend. 🙂

[MichelleBlanchette]

This Weekend's Update

I changed the language style and code examples per feedback on Lesson 1.

I then also wrote the next sections about Inserting Data and Querying Data: https://github.com/MichelleBlanchette/learn-wp-content/blob/module-07-custom-database-tables--lesson-02/plugin-developer-learning-pathway/07-Custom-Database-Tables/02-Interacting-with-custom-tables.md

Remaining:

• Updating Data
• Deleting Rows
• Generic Queries
  • Particularly for advanced custom query statements which use things like DROP TABLE or INSERT INTO ... ON DUPLICATE KEY UPDATE or even START TRANSACTION; with SELECT ... FOR UPDATE and ROLLBACK;

[jonathanbossenger]

Thank you for your updates @MichelleBlanchette it makes it a lot easier to draft the weekly content production update.

[MichelleBlanchette]

Added the Updating Data section this evening. Commit MichelleBlanchette/learn-wp-content@b90efc5

[MichelleBlanchette]

No tangible updates this weekend. Started drafting the final two sections: Deleting Data and Generic Queries.

Had some personal responsibilities that I needed to take care of, but I hope to have this script ready for peer review by the end of this week. It's a big'n! 😃

[MichelleBlanchette]

Phew! It's way too late in the evening for me, but this lesson's script is now ready for review, @jonathanbossenger !

Please see the Pull Request: wptrainingteam/learn-wp-content#16

[jonathanbossenger]

@MichelleBlanchette, the PR has been merged; please feel free to go ahead and create the video whenever it's a suitable time for you.

Once you've created this video, feel free to go ahead and share the link in a comment on this issue for review, update the project status to "Ready for review," and share that there's new video for review in the #training channel in Slack (example here)

[MichelleBlanchette]

Sounds good, @jonathanbossenger !

For this weekend's update, I'm happy to say I've fully recorded the audio and have finished editing 7 minutes of the total ~17 minutes of the video. It's going very well, and I'm expecting to finish it tomorrow night. 😀

Wishing everyone a Happy New Year! A new quarter-century milestone!! Woohoo!! 🎇 🎆

[MichelleBlanchette]

The video is ready for review, and has a total duration of 16:55

https://drive.google.com/file/d/1vvQODPxpnq068JLDY12Q4f97ZL0DZQ1O/view?usp=sharing

**For all my code walk-throughs, I only used an arrow graphic overlay. This is not recommended when talking about code that spans multiple lines, but my editing program doesn't make that easy/possible for me. I looked into the alternative softwares mentioned in the Training Handbook, but they all either required payment or also didn't have a feature to draw shape overlays.