Tag Archives: Wordpress custom tables

Home / Posts tagged "Wordpress custom tables"

Managing data custom tables with plugin for WordPress 3

Once you have a custom table created by plugin or phpMyAdmin, you might want to manage it through the Dashboard. So, this tutorial will show you how to create a menu item in the settings menu of the Dashboard, use the WordPress’ wpdb class to manipulate the data of the custom table and create an interface for it which matches the Worpress UI.

Step 1: Create the plugin directory and file
Create a directory called “bor-manage-table” in the wp-content/plugin directory under the WordPress installation directory. In this directory, create a PHP file called “bor-manage-table.php”

Step 2: Create the header file
Open the file “bor-manage-table.php” and add this PHP comment block.

<?php
/*
Plugin Name: Managing data table
Plugin URI: http://blog.boroniatechnologies.com/plugin
Description: Plugin add, update and delete data from a custom table
Author: Lebasca
Version: 1.0
Author URI: http://blog.boroniatechnologies.com/
*/

Step 3: Add the menu item to the Settings Menu
Below the header, add the following code to create the sub-menu item in the Settings menu. The sub-menu item created is called “Bor Plugin”

add_action('admin_menu', 'bor_plugin_menu');
function bor_plugin_menu() {
    if (is_admin()){
        add_options_page('Bor Plugin Options', 'Bor Plugin', 'manage_options', 'bor-plugin', 'bor_plugin_options');
    }
}

Step 4: Get info from the custom table
After the previous code, add the following function codes to get data from “wp_bor_software” table.

/**Get all the data from the tabe wp_bor_software**/
function bor_get_software() {
    global $wpdb;
    $software = $wpdb->get_results("SELECT * FROM ".$wpdb->prefix."bor_software ORDER BY id ASC");
    return $software;
}

/**Get an specific row from the table wp_bor_software**/
function bor_get_softwarerow($id) {
    global $wpdb;
    $the_software = $wpdb->get_results("SELECT * FROM " .$wpdb->prefix ."bor_software WHERE id='".$id."'");
    if(!empty($the_software[0])) {
        return $the_software[0];
    }
    return;
}

Step 5: Add content to the dashboard
Below the previous functions, add the following function code to add the fields of the custom table into the Administration Dashboard.

function bor_software_meta_box() {
    global $edit_software;
?>
    <p>Software: <input type="text" name="bor_software" value="<?php if(isset($edit_software)) echo $edit_software->software;?>" /></p>
    <p>Developer: <input type="text" name="bor_developer" value="<?php if(isset($edit_software)) echo $edit_software->developer;?>" /></p>
    <p>Type: <input type="text" name="bor_type" value="<?php if(isset($edit_software)) echo $edit_software->type;?>" /></p>
    <p>License: <input type="text" name="bor_license" value="<?php if(isset($edit_software)) echo $edit_software->license;?>" /></p>
<?php
}

Step 6: Call functions to create the interface and manipulate the data
After the previous code, add the following function code to call the functions that will create the interface and manipulate the data.

function bor_plugin_options(){
    /**Manipulate data of the custom table**/
    bor_action();
    if (empty($_GET['edit'])) {
      /**Display the data into the Dashboard**/
        bor_manage_software();
    } else {
      /**Display a form to add or update the data**/
        bor_add_software();  
    }
}

Step 7: Manipulate the data
Below the previous function, add the following function code that manipulates the data according to the action specified.

function bor_action(){
    global $wpdb;

    /**Delete the data if the variable "delete" is set**/
    if(isset($_GET['delete'])) {
        $_GET['delete'] = absint($_GET['delete']);
        $wpdb->query("DELETE FROM " .$wpdb->prefix ."bor_software WHERE id='" .$_GET['delete']."'");
    }

    /**Process the changes in the custom table**/
    if(isset($_POST['bor_add_software']) and isset($_POST['bor_software']) and isset($_POST['bor_developer']) and isset($_POST['bor_type']) and isset($_POST['bor_license']) ) {   
        /**Add new row in the custom table**/
        $software = $_POST['bor_software'];
        $developer = $_POST['bor_developer'];
        $type = $_POST['bor_type'];
        $license = $_POST['bor_license'];

        if(empty($_POST['bor_software_id'])) {
            $wpdb->query("INSERT INTO " .$wpdb->prefix ."bor_software(software,developer,type,license) VALUES('" .$software ."','" .$developer."','" .$type."','" .$license ."');");
        } else {
        /**Update the data**/
            $software_id = $_POST['bor_software_id'];
            $wpdb->query("UPDATE " .$wpdb->prefix. "bor_software SET software='" .$software ."', developer='" .$developer ."', type='" .$type ."', license='" .$license ."' WHERE id='" .$software_id ."'");
        }
    }  
}

Step 8: Display the interface to add a new row
After the previous code, add the following function code to create the meta box.

function bor_add_software(){
    $software_id =0;
    if($_GET['id']) $software_id = $_GET['id'];

    /**Get an specific row from the table wp_bor_software**/
    global $edit_software;
    if ($software_id) $edit_software = bor_get_softwarerow($software_id);  

    /**create meta box**/
    add_meta_box('bor-meta', __('Software Info'), 'bor_software_meta_box', 'bor', 'normal', 'core' );
?>

    /**Display the form to add a new row**/
    <div class="wrap">
      <div id="faq-wrapper">
        <form method="post" action="?page=bor-plugin">
          <h2>
          <?php if( $software_id == 0 ) {
                $tf_title = __('Add software');
          }else {
                $tf_title = __('Edit software');
          }
          echo $tf_title;
          ?>
          </h2>
          <div id="poststuff" class="metabox-holder">
            <?php do_meta_boxes('bor', 'normal','low'); ?>
          </div>
          <input type="hidden" name="bor_software_id" value="<?php echo $software_id?>" />
          <input type="submit" value="<?php echo $tf_title;?>" name="bor_add_software" id="bor_add_software" class="button-secondary">
        </form>
      </div>
    </div>
<?php
}

Step 9: Display the data of the custom table and allow to manipulate it
Below the previous function, add the following function code to add a form which display the data from the custom table and manipulate it.

function bor_manage_software(){
?>
<div class="wrap">
  <div class="icon32" id="icon-edit"><br></div>
  <h2><?php _e('Bor Plugin Options') ?></h2>
  <form method="post" action="?page=bor-plugin" id="bor_form_action">
    <p>
        <select name="bor_action">
            <option value="actions"><?php _e('Actions')?></option>
            <option value="delete"><?php _e('Delete')?></option>
      </select>
      <input type="submit" name="bor_form_action_changes" class="button-secondary" value="<?php _e('Apply')?>" />
        <input type="button" class="button-secondary" value="<?php _e('Add a new software')?>" onclick="window.location='?page=bor-plugin&amp;edit=true'" />
    </p>
    <table class="widefat page fixed" cellpadding="0">
      <thead>
        <tr>
        <th id="cb" class="manage-column column-cb check-column" style="" scope="col">
          <input type="checkbox"/>
        </th>
          <th class="manage-column"><?php _e('Software')?></th>
          <th class="manage-column"><?php _e('Developer')?></th>
          <th class="manage-column"><?php _e('Type')?></th>
          <th class="manage-column"><?php _e('License')?></th>
        </tr>
      </thead>
      <tfoot>
        <tr>
        <th id="cb" class="manage-column column-cb check-column" style="" scope="col">
          <input type="checkbox"/>
        </th>
          <th class="manage-column"><?php _e('Software')?></th>
          <th class="manage-column"><?php _e('Developer')?></th>
          <th class="manage-column"><?php _e('Type')?></th>
          <th class="manage-column"><?php _e('License')?></th>
        </tr>
      </tfoot>
      <tbody>
        <?php
          $table = bor_get_software();
          if($table){
           $i=0;
           foreach($table as $software) {
               $i++;
        ?>
      <tr class="<?php echo (ceil($i/2) == ($i/2)) ? "" : "alternate"; ?>">
        <th class="check-column" scope="row">
          <input type="checkbox" value="<?php echo $software->id?>" name="bor_id[]" />
        </th>
          <td>
          <strong><?php echo $software->software?></strong>
          <div class="row-actions-visible">
          <span class="edit"><a href="?page=bor-plugin&amp;id=<?php echo $software->id?>&amp;edit=true">Edit</a> | </span>
          <span class="delete"><a href="?page=bor-plugin&amp;delete=<?php echo $software->id?>" onclick="return confirm('Are you sure you want to delete this software?');">Delete</a></span>
          </div>
          </td>
          <td><?php echo $software->developer?></td>
          <td><?php echo $software->type?></td>
          <td><?php echo $software->license?></td>
        </tr>
        <?php
           }
        }
        else{  
      ?>
        <tr><td colspan="4"><?php _e('There are no data.')?></td></tr>  
        <?php
      }
        ?>  
      </tbody>
    </table>
    <p>
        <select name="bor_action-2">
            <option value="actions"><?php _e('Actions')?></option>
            <option value="delete"><?php _e('Delete')?></option>
        </select>
        <input type="submit" name="bor_form_action_changes-2" class="button-secondary" value="<?php _e('Apply')?>" />
        <input type="button" class="button-secondary" value="<?php _e('Add a new software')?>" onclick="window.location='?page=bor-plugin&amp;edit=true'" />
    </p>

  </form>
</div>
<?php
}
?>

Step 10: Test the plugin
Go to your WordPress Dashboard, click on the Settings menu and find the sub-menu item created “Bor Plugin”. Then, click on this sub-menu item and you should see a screen like the image below.

Summary: This tutorial gave you the basic idea how to manipulate the data of a custom table by creating a user interface into the Administration Dashboard. You learned the functions to add a menu item to an existing menu and the functions to enable you to add data to your existing content. You also learned to use the WordPress styles to match with its user interface.

Creating custom tables WordPress Plugin

One of the essential knowledge required to develop plugins is to create additional tables in the WordPress database. Those custom tables allow developers to store specific information needed for their plugins rather than using the option table.

This tutorial shows you with simple steps how to create your custom table when the plugin is installed and how to delete the custom table when the plugin is deleted.

Step 1: Create the plugin directory and file
Create a directory called “bor-table” in the wp-content/plugin directory under the WordPress installation directory. In this directory, create a PHP file called “bor-table.php”

Step 2: Create the header file
Open the file “bor-table.php” and add this PHP comment block.

<?php
/*
Plugin Name: Create table
Plugin URI: http://blog.boroniatechnologies.com/plugin
Description: Plugin creating tables
Author: Lebasca
Version: 1.0
Author URI: http://blog.boroniatechnologies.com/
*/

Step 3: Activate the plugin
Below the header, add the following code to call the plugin activation function which is triggered when the plugin is activated in WordPress. The activation function calls the function “bor_install” to create the custom table.

register_activation_hook( __FILE__, 'bor_install' );

Step 4: Create the custom table
After the previous code, add the following code to create the custom table. This function uses WordPress’ wpdb class to create the table “wp_bor_software”.

function bor_install()
{
    /**Create an instance of the database class**/
    global $wpdb;

    /**Set the custom table name with the wp prefix "wp_bor_software"**/
    $table_name = $wpdb->prefix . "bor_software";

    /**Execute the sql statement to create or update the custom table**/
    $sql = "CREATE TABLE " . $table_name . " (
            id INT(9) NOT NULL AUTO_INCREMENT,
            software text NOT NULL,
            developer text NOT NULL,
            type text NOT NULL,
            license text NOT NULL,
       UNIQUE KEY id (id)
    );"
;

    require_once(ABSPATH.'wp-admin/includes/upgrade.php');
    dbDelta($sql);

    /**Insert one record in the table by using the array method**/
    $ini_software = "Wordpress";
    $ini_developer = "Wordpress Foundation";
    $ini_type = "Blog";
    $ini_license = "GNU";

    $results = $wpdb->insert( $table_name, array( 'software' => $ini_software, 'developer' => $ini_developer, 'type' => $ini_type, 'license' => $ini_license ) );    

}

Step 5: Deactivate the plugin
Below the previous function, add the following code to call deactivation function which is triggered when the plugin is deactivated in WordPress. The activation function calls the function “bor_uninstall” to delete the custom table.

register_uninstall_hook( __FILE__, 'bor_uninstall' );

Step 6: Delete the custom table
After the previous code, add the following code to delete the custom table “wp_bor_software”>

function bor_uninstall(){

    /**Create an instance of the database class**/
    global $wpdb;

    /**Set the custom table name with the wp prefix "wp_bor_software"**/
    $db_table_name = $wpdb->prefix . "bor_software";

    /**Execute the sql statement to delete the custom table**/
    $sql = 'DROP TABLE IF EXISTS '.$db_table_name;
    $wpdb->query( $sql );
}

Step 7: Close the php syntax
Add the last code in your file “bor-table.php” to end the PHP syntax.

?>

Step 8: Test the creation of the custom table
Go to your WordPress Dashboard, click on the plugin link to see the plugin created “Create table” and click on Activate link to create the custom table. Then, go to your phpAdmin and check for the “wp_bor_software” table.

Step 9: Test the deletion of the custom table
Go to your WordPress Dashboard, click on the plugin link to see the plugin created “Create table”, click on Deactivate link and click on Delete link to delete the plugin files and the custom table. Before doing that, you might want to copy this plugin directory in another directory for future reference. Then, go to your phpAdmin and the “wp_bor_software” table is gone.

Summary: This tutorial gave you the basics on how to create custom tables with a plugin in WordPress. You learned the activate and deactivate functions and the wpdb class to manipulate the database.

About Boronia Technologies

Boronia Technologies is the business name of Catherine Lebastard, an IT professional with extensive experience in web development and database administration. Catherine chose the name of Boronia as she likes flowers and purple color. The logo of her business is inspired on Boronia flower plus digital elements incorated inside the flower.


Why you should use my services?

Because I can guide you to the right path and provide you what you are looking for at the right price according to your budget. Also, I am able to develop or debug an application with any programming language that you request because I have the ability to learn at extremely fast pace and adapt easily to progress in technology.