JAM Plugin Example - MySql Table

The MySql Table example shows how to list a table of rows from a MySql table on the server and allow the user to update the rows. This is a good example to help developers write their own plugin. It is also an easy template for any MySql table on the server. By simply changing the member variables of the class, a user can change the table and columns that are accessible from the plugin.

The code for this example can be downloaded here: JAM Plugin Example - MySql Table.

MySql Plugin Class Member Variables

In the case of the MySql Plugin, we have provided member variables to allow users to easily change the table the plugin is accessing.  The variables below tell the plugin which table to access, which columns to show on the table screen, which rows to return on the table screen and how to sort the rows, and which columns to show on the detail page and which columns the user can modify.  Note that the first column in tableColumns and detailColumns must be the key column used to access an individual row in the table.

/* Changing these member variables will allow users to access and update other tables on their site.  No code changes below should be required. */
static $displayName = "Modules";
static $tableName = "#__modules";
static $tableColumns = array(
        "id",   
        "module",
        "title",
        "position",
        "ordering",
        "published",
);      
static $tableWhere = "WHERE `client_id` = 0";
static $tableOrderBy = "ORDER BY `position`, `ordering`";
static $detailColumns = array(
        array("id", self::READONLY),
        array("module", self::READONLY),
        array("title", self::READWRITE),
        array("position", self::READWRITE),
        array("ordering", self::READWRITE),
        array("published", self::READWRITE),
);      

Initial Call - onGetAvailablePlugins

As always, the first class to all plugins is the onGetAvailablePlugins method.  This method returns display information to the application along with the class method to call when the user clicks the row for that plugin.  In the case of the MySql Table plugin, the first method to call is getMySqlTable.

function onGetAvailablePlugins()
{
        return array(
                "display_name" => "MySql: ".self::$displayName,
                "description" => "Example MySql Table JAM Plugin.",
                "class_name" => get_class(),
                "method_name" => "getMySqlTable",
                "data_type" => "html",
        );
}

Primary Method Call - getMySqlTable

The getMySqlTable method executes a SQL statement on the MySql database to get back the requested rows.  It then creates the HTML response with these rows to display to the user.  Using the getMySqlTableDetail javascript function, this page includes links to access individual rows.  When the user clicks the key value for a row, the app calls back to the getMySqlTableDetail plugin method passing the key value.

// This method is called first and returns a table with rows from the MySql table specified and a link to edit an individual row.
function getMySqlTable()
{
        $db = &JFactory::getDBO();

        // Create our query and execute it to get back an array of rows.
        $query = "SELECT ".self::quoteAndImplode(self::$tableColumns)." FROM ".self::$tableName." ".self::$tableWhere." ".self::$tableOrderBy;
        $db->setQuery($query);
        $rows = $db->loadRowList();

        // If we get a database error, send it back to the user.
        if($db->getErrorNum() != 0) {
                return "DB ERROR: ".$db->getErrorMsg();
        }

        // This includes the required jamCallPluginMethod javascript function.
        $data = JoomlaAdminMobileHelper::getJamJavascriptInclude();

        // Table header.
        $data .= "<table border=\"1\">\n";
        $data .= "<tr>";
        foreach(self::$tableColumns as $tableColumn) {
                $data .= "<th>".$tableColumn."</th>";
        }
        $data .= "</tr>\n";

        // Table data.
        foreach($rows as $row)
        {
                $data .= "<tr>";
                for($i = 0; $i < count(self::$tableColumns); $i++) {
                        if($i == 0) {
                                // This is the table key column with a link to edit the detail.
                                $data .= "<td><a href=\"#\" onclick=\"javascript:jamCallPluginMethod('".get_class()."', 'getMySqlTableDetail', 'ID: ".$row[0]."', 'html', [ ".$row[0]." ]); return false;\">".$row[0]."</a></td>";
                        } else {
                                $data .= "<td>".$row[$i]."</td>";
                        }
                }
                $data .= "</tr>\n";
        }

        $data .= "</table>\n";

        return $data;
}

Subsequent Call - getMySqlTableDetail

The getMySqlTableDetail is called for an individual row in the table.  First it retrieves the row from the database.  It then creates an HTML response with a table of column name/value pairs.  If a column is marked as READWRITE, the user will be provided with a text input that will allow them to change the value.  When the user wants to save the details for this row, they click the Save button and the jamCallPluginMethod javascript function will alert the application to call back to the server.  It will call the saveMySqlTableDetail method passing all the column names and values (including the key value).

// This method is called when the user clicks a row's key value to edit the detail of an individual row.
function getMySqlTableDetail($id)
{
        $db = &JFactory::getDBO();

        // Create our query and execute it to get back the requested row.
        $query = "SELECT ".self::quoteAndImplode(self::$detailColumns)." FROM ".self::$tableName." WHERE ".self::$detailColumns[0][0]." = ".$db->quote($id);
        $db->setQuery($query);
        $rows = $db->loadRowList();

        // If we get a database error, send it back to the user.
        if($db->getErrorNum() != 0) {
                return "DB ERROR: ".$db->getErrorMsg();
        }

        // This includes the required jamCallPluginMethod javascript function.
        $data = JoomlaAdminMobileHelper::getJamJavascriptInclude();

                // Javascript function to call saveMySqlTableDetail on the server and table header.
        $className = get_class();
        $data .= <<<EOT
<script language="javascript">
        function save() {
                var saveForm = document.forms[0]
                var params = new Array();
                var element;

                var j = 0;
                for(var i = 0; i < saveForm.length; i++) {
                        element = saveForm.elements[i];
                        if(element.type == "text") {
                                params[j++] = element.name;
                                params[j++] = element.value;
                        }
                }
                
                jamCallPluginMethod('{$className}', 'saveMySqlTableDetail', 'Saving: ' + {$id}, 'html', params);
        }
</script>

<form name="saveForm">
<table border="1">
<tr><th>Column</th><th>Value</th></tr>
EOT;

        // Table data.
        if(count($rows) > 0) {
                for($i = 0; $i < count(self::$detailColumns); $i++) {
                        $detailColumn = self::$detailColumns[$i];
                        $data .= "<tr><td>".$detailColumn[0]."</td><td>";
                        $data .= "<input name=\"".$detailColumn[0]."\" value=\"".$rows[0][$i]."\" ".($detailColumn[1] == self::READONLY ? "readonly=\"readonly\" " : "")."/>\n";;
                        $data .= "</td></tr>\n";
                }
        }

        $data .= <<<EOT
</table><br />
<input type="button" onclick="javascript:save(); return false;" value="Save" />
</form>
EOT;

        return $data;
}

Subsequent Call - saveMySqlTableDetail

The saveMySqlTableDetail method retrieves the column names and values from the input parameters.  It then creates an object used to update the MySql table using the updateObject method.  Lastly, it notifies the user if the save was successful.

// This method is called when the user clicks the Save button to save the detail of an individual row.
function saveMySqlTableDetail() {
        // Load the function arguments into an array map that will be used to update the table.
        $args = func_get_args();
        $fieldValues = array();
        for($i = 0; $i < count($args)/2; $i++) {
                $fieldValues[$args[2*$i]] = $args[2*$i + 1];
        }

        $db = &JFactory::getDBO();

        // Call updateObject with a PHP object created using our array map.
        $object = (object)$fieldValues;
        $key = self::$detailColumns[0][0];
        $success = $db->updateObject(self::$tableName, $object, $key);

        // Return a success/fail message to the user.
        $data = "ID: ".$fieldValues[$key]." - ".($success ? "successfully saved" : "could not be saved.")."\n";

        return $data;
}

Although not very complicated, this example is extremely powerful.  Using this template, a user can easily create a plugin that will allow them to access any MySql table on their Joomla! site.  The flexibility of the JAM plugin framework actually allows the user to access any MySql table on their server, even if it is not a Joomla! table.  This is helpful for users with bridged functionality to 3rd party shopping carts, bulletin boards, Wordpress or Drupal sites, and much more.  The possibilities are endless.