speaker Multimusen

WordPress and SQL

October 5th, 2013 by

The purpose of this lecture is to experiment with WordPress CMS and database management through the $wpdb class. During the lecture it’a a good idea to have a running WordPress site.

Agenda

  • Lesson 1 – from ER-diagram to database tables.
  • Break
  • Lesson 2-3 – creating a WP plugin.

Next lesson we might look at:

  • The $wpdb class (if time permits we’ll look at it today).

Online Resources

During the lecture you’ll need

  • A WordPress site where you’re the administrator.
    NB: if you use an administrated site e.g. at WordPress.com there is a good chance that you’re not allowed to add home-hacked plugins.
  • Also you must be able to admin the database of the site e.g. via phpmyadmin.
  • A normalized ER diagram in the third normal form.

As soon as your ER-diagram has been normalized to the third normal form you can create the database tables. Since we’ll work with a WP plugin you should create the tables in the same database that your WP installation uses.

When you’re ready create the tables you need in your WP database via phpmyadmin or similar.

How to use the relations

So how could you find the wives of say king Henry VIII? Construct a suitable SQL sentence? If we asume that King Henry VIII’s Id = 8 then the SQL would look a bit like this:

SELECT * FROM Spouses, Majesties 
WHERE MajestiesID = 8;

In MySQL you can join tables by SQL. Perhaps you could improve the code by the join keyword? Check out joins from W3Schools.

Tutorial: Relations

Tutorial: Phpmyadmin

Tutorial: Join

How to write your own WP plugin

Create a php file along these lines:

Save your file as something like “mojnMojn.php”. Then zip the file (don’t use .rar, .arj, .gz, .tar) so that you’ve got a file called mojn.zip

  1. In the Dashboard / Plugins
  2. Plugins add new
  3. Upload your mojn.zip file.

Then go to the plugin menu, select install new plugin and select the upload option. When the upload has finished remember to activate the plugin. Then you’re ready to test it.

Create a post or page and enter the word in sharp brackets like this:

[mojnMojn]

Save the post or page and see the result.