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/
• https://github.com/WordPress/Learn/issues/1867#issuecomment-1745566702

Automation Code

//lesson

[github-actions[bot]]

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 https://github.com/MichelleBlanchette/learn-wp-content/commit/b90efc544051eb39bcb5087d94c6e13c63ebf4e8

[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: https://github.com/wptrainingteam/learn-wp-content/pull/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.

[Parth-Dodiya]

Tutorial/Lessons Review Checklist

• [x] Learning outcomes/objectives are clear.
• [x] Technical concepts introduced in the content are accurate.
• [x] The speed of demonstrations are easy to follow.
• [x] The narration audio matches what is shown visually.
• [x] Spelling and grammar are correct.
• [x] Sound quality is consistent throughout the video.
• [x] Brand Usage Guidelines and Promotional Guidelines are being followed.
• [x] Media assets are all in the public domain (CC0).

$wpdb is explained in a simple and easy-to-understand manner. The video is highly recommended for anyone who wants to learn about the $wpdb class.

[iconithemes]

Tutorial/Lessons Review Checklist

Please tick all items you've confirmed:

• [x] Learning outcomes/objectives are clear.
• [x] Technical concepts introduced in the content are accurate.
• [x] The speed of demonstrations are easy to follow.
• [x] The narration audio matches what is shown visually.
• [x] Spelling and grammar are correct.
• [x] Sound quality is consistent throughout the video.
• [x] Brand Usage Guidelines and Promotional Guidelines are being followed.
• [x] Media assets are all in the public domain (CC0).

Content of video is good to go.

[dhaval6520]

Tutorial/Lessons Review Checklist

Please tick all items you've confirmed:

• [x] Learning outcomes/objectives are clear.

• [x] Technical concepts introduced in the content are accurate.

• [x] The speed of demonstrations are easy to follow.

• [x] The narration audio matches what is shown visually.

• [x] Spelling and grammar are correct.

• [x] Sound quality is consistent throughout the video.

• [x] Brand Usage Guidelines and Promotional Guidelines are being followed.

• [x] Media assets are all in the public domain (CC0).

I reviewed the sessions, and the best one was 'Securely Developing Plugins and Themes.' It helps in developing plugins and themes according to WordPress security standards.