Hotaru Docs

 [həʊdɒks]

Working Directly with the Database

Hotaru CMS is intended for use with MySQL, although it uses a modified version of Justin Vincent's excellent ezSQL, which makes working with the database much easier. Before we get started, here are two links you should bookmark for reference:

  1. ezSQL Introduction - examples.
  2. Wordpress' Interfacing with the Database - how to protect queries against SQL injection attacks.

Rather than repeating the content of those excellent resources, let's look at some real-life Hotaru examples:

1. Get the name of the site administrator

$sql = "SELECT user_username FROM " . TABLE_USERS . " WHERE user_role = %s";
$admin_name = $h->db->get_var($h->db->prepare($sql, 'administrator'));

get_var gets a single value, in this case user_username. Prepare is used to prevent SQL injection attacks. Athough not necessary when retrieving data from the database, it's good practice to use it anyway, if only because it saves you needing to quote your strings.

   Originally Posted by Wordpress 
   Notice that you do not have to worry about quoting strings. Instead of passing the variables directly into the SQL query, use a %s placeholder for strings and a %d placeholder for integers. You can pass as many values as you like, each as a new parameter in the prepare() method.

2. Get all info about the site administrator

$sql = "SELECT * FROM " . TABLE_USERS  . " WHERE user_role = %s";
$admin_info = $h->db->get_row($h->db->prepare($sql, 'admin'));

As above, but this time we use get_row and then access each value like this:

echo $admin_info->user_username;

3. Get the names of all the members:

$sql = "SELECT user_username FROM " . TABLE_USERS  . " WHERE user_role = %s";
$users = $h->db->get_results($h->db->prepare($sql, 'member'));

We can access them with a foreach loop:

if ($users) // make sure we have a result.
     foreach($users as $user) {
          echo $user->user_username;
     }
}

Now that you've seen some examples of using ezSQL commands combined with the prepare() function, use the ezSQL Introduction to learn more.

Creating custom tables

If your plugin will be storing a lot of data, it may be worth creating a dedicated table in the database for it, especially because anything you store in the pluginsettings table is deleted if your plugin is uninstalled. Custom tables, however, remain forever...

First, to create a table when the user installs your plugin, use the install_plugin hook. Here's an example from the Users plugin:

public function install_plugin($h)
{
     // Create a new empty table called "usermeta"
    $exists = $h->db->table_exists('usermeta');
    if (!$exists) {
        //echo "table doesn't exist. Stopping before creation."; exit;
        $sql = "CREATE TABLE `" . DB_PREFIX . "usermeta` (
          `usermeta_id` int(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
          `usermeta_userid` int(20) NOT NULL DEFAULT 0,
          `usermeta_key` varchar(255) NULL,
          `usermeta_value` text NULL,
          `usermeta_updatedts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
          `usermeta_updateby` int(20) NOT NULL DEFAULT 0, 
          INDEX  (`usermeta_userid`)
        ) ENGINE=" . DB_ENGINE . " DEFAULT CHARSET=" . DB_CHARSET . " COLLATE=" . DB_COLLATE . " COMMENT='User Meta';";
        $h->db->query($sql); 
    }
}

Note: The table_exists function is a custom Hotaru function. There's also a column_exists function. Read more here.

Optionally, define a shorthand name for your table using the theme_index_top hook and equivalent function, e.g.

public function theme_index_top($h)
{
    ...
    define("TABLE_USERMETA", DB_PREFIX . 'usermeta');
    ...
}

That should set you on your way to working with databases in Hotaru.

Getting StartedDesign and LayoutPlugin DevelopmentAdvanced TopicsFunction ReferenceTroubleshooting