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.

Comments

    masterwoo

    I want this plugin to work with my custom table having fields

    1) pname
    2) pid
    3) price
    4) priceason

    Need your help to modify this plugin. Thanks

      lebasca

      Follow the tutorial until step 3. In step 4, replace the words that contain the example table (bor_software) with your table name. In step 5, replace the example label fields (Software, Developer, Type and License) with your label fields as well as the input name. Also,replace the example table fields ($edit_software->software, $edit_software->developer, $edit_software->type and $edit_software->license) with your table fields ($edit_software->pname, $edit_software->pid, $edit_software->price and $edit_software->pricereason). Follow the step 6 as it is and replace the example table name with your custom table in step 7. After the line /**Process the changes in the custom table, replace bor_software, bor_developer, bor_type and bor_license with the new input name given for your table fields. After the line if(empty($_POST[‘bor_software_id’])) {, replace the example table name and the example table fields with your table name and fields for the INSERT and UPDATE SQL statement. Follow the step 9 as it is and you could change the labels such as ‘Bor Plugin Options’, ‘Software’ ,’Developer’, ‘Type’, ‘License’, etc. Then, follow step 10 to test the plugin. Hope this help you!

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.