Introduction

Display a table, a record or just a value. Create a form. Send an email. Click here to download the plugin. This plugin is used in the two database assignments in Richard Halverson's section of the BUS 311 course at the University of Hawaii Shidler College of Business.

Shortcode Specification

The ABASE shortcode accesses a MySQL database without having to code PHP. Over 30 optional attribute values specify whether a shortcode on a page is replaced with an HTML input form or an SQL query output. Forms to search, insert, update or delete records are easy to specify. Output can be a formatted value, table, or email. ABASE supports file uploads, images, record passwords and foreign keys.

Shortcode

The ABASE shortcode with a list of all the optional attributes is shown next. Each attribute is described in detail below.

[abase ack="" alink="" center="" cols="" columns="" database="" db="" echo="" elements="" emailbcc="" emailcc="" emailfrom="" emailorigin="" emailsubject="" emailto="" fields="" files="" form="" from="" group="" images="" insert="" left="" limit="" notable="" notitle="" or="" order="" password="" required="" right="" rlink="" rownum="" search="" select="" sql="" style="" table="" update="" where=""]

Within the quotes of each attribute can be a parameter value or a list of values separated by commas (,). Empty fields need not be specified. An unrecognized attribute is ignored. Using the shortcode with no (recognizable) attributes ([abase]) lists the database user, database, tables with record counts for the current database. Otherwise, either the sql, from, or table attribute should be used to specify part or all of a database operation, or an email sent, using at least an emailto attribute. The attribute names must be lower case.

ABASE shortcodes can also be of the form [abase ...]<content>[/abase]. In this form, <content> will be displayed above what [abase ...] produces. The shortcode name "abase" can be all lower-case or all upper-case but not mixed case. Each functions identically but are treated separately. Note that when including a closing shortcode, it must be case identical. <content> can include ABASE tags as long as the alternate case is used. Embedding shortcodes in content is useful when using ABASE to send emails from your website.

Databases

ABASE can connect to up to 3 databases, numbered 1, 2 and 3. (In Settings->ABASE for MySQL. Expand to full settings.) ABASE shortcodes default to using database 1. A db="1", db="2" or db="3" attribute in a shortcode will change the default database for this and the remaining shortcodes on the page. Therefore if the page uses database 2, you only need to specify [abase db="2"] in the first shortcode, then not specify a db attribute in any of the remaining shortcodes on that page.

In addition to [abase] and [ABASE] shortcodes, two more are available. Shortcode [abase2] will use the database 2 regardless of the default, and will not change the default. Shortcode [abase3] will use database 3 and not change the default.

Attributes

  • ack - ack=" ( <verbosity> ) ( , ) ( <url> ) ( , ) ( <color> )" - for acknowledging a record insert, update or search.
    • <verbosity>="1" or "2" or "3" or "4"
    • <url>=path-file of an update form for a record insert or update
    • <color>=HTML color of acknowledgement. If color is in all caps, acknowlegement will display in bold
  • alink - alink=" <column> ( , <url> ( , <append> ( , <target> ) ) )"
    • <column> - column name whose content will become a link to a <url> that is appended with the contents of column <append>.
    • <target> specifies the target window for the link (e.g., target="_blank" will open a new browser window.
  • center - center=" <column> ( , <column> )" specifies one or more table columns to center when displaying. Note: center="*" indicates all columns.
  • cols - cols=" <field_spec> ( , <field_spec> )" - Specifies the column names to be displayed as a table. Two or more records to be displayed are required. Designed to be used in conjunction with fields specification. See <field_spec> below under fields. Note: cols="*" indicates all columns.
  • columns - columns=" <field_spec> ( , <field_spec> )" - Specifies the columns to be displayed in a table. Zero or more records will be displayed. See <field_spec> below under fields. Note: columns="*" indicates all columns.
  • database - database="" Override default database specified in Settings. Sticky. (Specify once on page and override applies to remaining short codes until new db="" specification.)
  • db - db="" Change default database. Choose database 1, 2 or 3 as specified in Settings. Sticky. (Specify once on page and override applies to remaining short codes.)
  • echo - echo="<color>" will display (i.e., echo) the shortcode in the specified HTML <color> (e.g., echo="red").
  • elements - elements=" <column> ( , <column> )" specifies one or more fields in a form with form elements for either searching, adding to or updating the database.
  • emailbcc - emailbcc="" specify blind copy email address(es). Either a specific email address or a column name that contains an email address. emailto must be specified to send a email.
  • emailcc - emailcc="" specify copy email address(es). Either a specific email address or a column name that contains an email address. emailto must be specified to send a email.
  • emailfrom - emailfrom="" specify from email address. Either a specific email address or a column name that contains an email address. emailto must be specified to send a email.
  • emailorigin - emailorigin="" specifies origin information to add to the tail of every email. emailorigin="p" will display the page URL, emailorigin="r" will display the remote IP address of the browser, emailorigin="n" specifies nothing is to be displayed. When emailorigin="" is not specified then both the page URL and the remote IP address are displayed.
  • emailsubject - emailsubject="" specify email subject. emailto must be specified to send a email.
  • emailto - emailto="" specify email recipient. Either a specific email address or a column name that contains an email address. Only one email can be sent per shortcode execution.
  • fields - fields=" <field_spec> ( , <field_spec> )" - Specifies the column names to be displayed in record view. Record view is a two column table with the field names in the first column and the values in the second column.
    • Meta language: terms in parentheses are optional, vertical bar (|) - OR operator. (Exception - vertical bar (|) preceded <foreign_column>.)
    • <field_spec> ::= ( <column_title>^ ) <column_name> ( |<foreign_column> ) ( ! ( '( <element_type> <space> ) <element_style>' ) ) ( [>|>=|=|<=|<|!=] ( % ) <operand> ) ( % ) ( $ ( <button_value> ) )
    • <operand> ::= <surrogate> | <integer> | ' <constant> '
    • <column_title> = optionally precedes <column_name> using a carrot (^) character. It replaces <column_name> as column title in table view and field name in record view.
    • <column_name> = name of a column returned from the SELECT
    • <foreign_column> = follows <column_name> after a vertical-bar (|) character, is the name of the column in the foreign table containing the value to display instead of the foreign key contained in this column.
    • <button_value> = text displayed on the submit button.
    • <element_type> = form element type (e.g., button, checkbox, email, file, hidden, image, number, password, radio, reset, submit, tel, text, textarea, time or url)
    • <element_style> = can be either (a) one to three integer values separated with semicolons (;), (b) a style attribute for a form element or image, or (c) date and time format. A single integer value is interpreted as the width of the textbox in pixels. The default height is 18 pixels. A pair of integer values separated by a semicolon is interpreted as the width of the element in pixels followed by the height in pixels. A third integer value specifies the vertical alignment. Therefore, '250;25;-5' would be interpreted as style="width:250px;height:15px;vertical-align:-5px;" in the HTML element tag. Date and time formats are PHP defined, e.g., 'l, F j, Y g:ia e' would format Friday, July 19, 2013 8:33pm UTC at that time. Note that date and time inputs are translated to Unix time using the PHP strtotime function, accepting virtually any format for entering the date and time (e.g., "today" translates to today's date.)
    • <surrogate> = name of a form element to test against the current field value in a search.
    • <constant> = value field of a checkbox.
    • <integer> = digits consisting of 0-9.
    • <space> = blank space.
    Note: fields"*" indicates all fields.
  • files - files=" <column> ( , <column> )" specifies one or more columns that will contain URLs of uploaded files. The insert and update form elements are file selection boxes with browse buttons. Inserting or updating values in files fields involves file uploading to the File Upload Directory (specified in Settings). The uploaded file will be stored in the directory and the directory path and file name will be stored in the database table cell. A files column field type should be varchar(255).
  • form - form="( <form_type> , ) <tag_code> ( , <form_action> )" Beginning and/or end of HTML form.
    • tag_code="1" specifies this shortcode as an entire form. A <form ...> tag will appear at the beginning and a </form> tag will appear at the end.
    • tag_code="2" specifies this shortcode as the beginning of a form. A <form ...> tag will appear at the beginning.
    • tag_code="3" specifies this shortcode as the end of a form. A </form> tag will appear at the end.
    • tag_code="4" specifies this shortcode as an entire form to delete a record.
    • form_type="search" specifies this is a form to search the database.
    • form_type="insert" specifies this is a form to insert a new record into the database.
    • form_type="update" specifies this is a form to update a record in the database.
    • form_action - specifies a URL action for the form. If not specified, the current URL is used.
  • from - Specifies the table_references clause in the MySQL SELECT statement. Default is table specified.
  • group - Specifies the GROUP BY clause and can include a HAVING part.
  • images - images=" <column_spec> ( , <column_spec> )" specifies one or more columns that will contain URLs of uploaded images. Similar to files except when an images column is displayed, it is displayed as an HTML image tag with the cell content defining the image source.
    <column_spec> ::= ( <site_url>^ ) <column_name>, where <site_url> replaces the default site_url in the image "src" field.
    The insert and update form elements are file selection boxes with browse buttons. Inserting or updating values in images fields involves file uploading to the File Upload Directory (specified in Settings). The uploaded file will be stored in the directory and the directory path and file name will be stored in the database table cell. An images column field type should be varchar(255).
  • insert - insert="" (depreciated. Use form="insert".)
  • left - left=" <column> ( , <column> )" specifies one or more columns to left justify. Note: left="*" indicates all columns.
  • limit - Specifies a LIMIT clause.
  • notable - notable="1" will cause the table in a cols="" or columns="" specification to display without the beginning (<table>) or ending table tag (</table>).
  • notitle - notitle="1" will cause the table in a cols="" or columns="" specification to display without the column titles.
  • or - or=" <column_set> ( , <column_set> )" specifies sets of columns in a search form that are compated to the same operand in the WHERE clause.
    • <column_set> ::= <column> ( | <column> )
  • order - Specifies the ORDER BY clause.
  • password - password="" specify password field. Password entry and match is required to update or delete table record.
  • required - required=" <column> ( , <column> )" specifies one or more columns in an insert or update form that must have a filled in value or the submit will not be accepted. An error alert message will appear.
  • right - right=" <column> ( , <column> )" specifies one or more columns to right justify. Note: right="*" indicates all columns.
  • rlink - rlink=" <column> ( , <url> ( , <target> ) )" Record link. Link which specifies the primary index <name>=<value> pair in query string. Used to display a single record in the database.
    • <column> column that will be a link.
    • <url> if specified, the URL of the page to display the record. If not specified, link will be to the current page.
    • <target> specifies the target window for the link (e.g., target="_blank" will open a new browser window.
  • rownum - rownum="1" will add line numbers to the display tables.
  • search - search="" (depreciated. Use form="search".)
  • select - Specifies the select_expr clause in the MySQL SELECT statement. Default is * (all columns).
  • sql - Specifies a complete MySQL statement to be executed.
  • style - This will specify a style="" attribute for the table that encloses the shortcode display.
  • table - Specifies the database table to be searched or updated.
  • update - update="" (depreciated. Use form="update".)
  • where - Specifies the where_condition clause in the MySQL SELECT statement. If it begins with "AND " or "OR " it will be concatenated to (instead of overridding) any other where expression.

Displaying Fields

  • Use cols="", columns="" or fields=""
  • Use cols="<field_spec> ( , <field_spec> )" to display 2 or more records in table view.
  • Use columns="<field_spec> ( , <field_spec> )" to display any number of records in table view.
  • Use fields="<field_spec> ( , <field_spec> )" to display one record in record view.
  • cols="" and fields="" can be used in the same shortcode.

Frequently Asked Questions

What is the ABASE shortcode?

[abase]

Putting "[abase]" on a page will display the current ABASE version, the default database, default user and a list of all the accessible database tables. Add attributes to access MySQL databases, create forms, display tables, and create Internet database applications in WordPress.

What are all these attributes? Where's a ABASE reference guide?

From the Details screen before you install ABASE, complete shortcode and attribute documentation can be found in the Description section. Once installed, from the WordPress Admin section, click Settings on the left, and then "ABASE for MySQL." This is also where you set the database and user. Below that is the most up-to-date reference guide for ABASE.

How do I know that ABASE is installed properly?

In the WordPress Admin section, clicking Settings on the left, then "ABASE for MySQL" will show the configuration settings. This confirms the installation.

Another way is to place the shortcode [abase] in a page or post and it will display the current user and database settings. If the database contains any tables, their names and record counts are listed.

How many ABASE shortcodes can I use on the same page?

There is no limit. Note that a shortcode [abase] is different than [ABASE]. This is important if you embed an ABASE shortcode within another, e.g., [ABASE] ... [abase /] ... [/ABASE] works.

Where do I set the database and user?

In the WordPress Admin menu, click Settings on the left, and the ABASE for MySQL.

How do I create a new database?

This step is performed outside ABASE. If you have cPanel, you can create a new database using the MySQL Databases or MySQL Database Wizard cPanel applications. Otherwise you can use the default WordPress database and create your own tables using the SQL CREATE TABLE statement.

I've created a new database but [abase] connects to a different database. Where do I change this?

ABASE can connect to up to 3 databases. You change or add a database in the WordPress dashboard by clicking Settings on the left, then clicking on ABASE for MySQL. ABASE defaults to the first database. If no database is specified, it defaults to the WordPress database. If no user is specified it defaults to the WordPress user. It is recommended that you create your own database not use the WordPress database. You can create your database and give the WordPress user access using the MySQL Create Database application. To connect a second or third database you may need to Expand to full settings first.

ABASE shortcodes default to using database 1. A db="1", db="2" or db="3" attribute in a shortcode will specify a specific database to use AND change the default database for the remaining shortcodes on the page. Therefore if the page uses database 2, you only need to specify [abase db="2"] in the first shortcode. Shortcodes [abase2 ...] will use the database 2 regardless of the default, and will not change the default. Shortcode [abase3 ...] will use database 3 and not change the default.

How do I create a new table?

You can use phpMyAdmin, which is available in cPanel. You can also use the CREATE TABLE SQL statement in an ABASE shortcode using the sql="?" attribute.

For example, shortcode:

[abase sql="CREATE TABLE employees (`employee_id` int(11) NOT NULL AUTO_INCREMENT, `first_name` varchar(100) NOT NULL, `last_name` varchar(100) NOT NULL, PRIMARY KEY (`employee_id`))"]

will create a table named 'employees' in the current database, with a primary key ID field named 'employee_id' and a first name field named 'first_name' and a last name field named 'last_name'. The primary key is autoincrement and the first and last names can each be up to 100 characters long.

How do I display a table?

There are different ways, depending on what you want to display.

[abase table="employees"] will display all the columns and all records in the employees table. If there are any foreign keys columns from those tables will also be displayed.

[abase table="employees" columns="first_name,last_name" ORDER="last_name,first_name"] will display only the first and last name columns in alphabetical order by last name.

[abase sql="SHOW COLUMNS FROM employees"] will display the structure of the employees table.

[abase sql="SHOW CREATE TABLE employees"] will display the SQL CREATE statement to create the table.

How do I add a Title field that is a foreign key into a Titles table?

To make the Title come from a separate Titles table, create your Titles table so it uses the InnoDB storage engine.

[abase sql="CREATE TABLE titles (title_id int(11) NOT NULL AUTO_INCREMENT, title_name varchar(50) DEFAULT NULL, PRIMARY KEY (title_id)) ENGINE=InnoDB"]

Make your employees table InnoDB also and add the column that will contain the foreign key.

[abase sql="ALTER TABLE employees ENGINE = INNODB"][abase sql="ALTER TABLE employees ADD title_key INT NOT NULL, ADD INDEX ( title_key )"]

Finally, set the foreign key relationship.

[abase sql="ALTER TABLE employees ADD FOREIGN KEY ( title_key ) REFERENCES titles (title_id) ON DELETE RESTRICT ON UPDATE RESTRICT"]

How do I create a form to add a record to a table?

[abase form="1,insert" table="employees" columns="first_name,last_name$Add Employee" elements="first_name,last_name" ack="red"]

The form attribute specifies a form to insert records, and the "1" indicates this form consists of only 1 shortcode (this one). The elements attribute specifies which fields are form input elements and the columns attribute specifies which fields are displayed and their formatting. The "$Add" specifies the submit button following the color field, with "Add" being the button text. The elements attribute specifies the first_name and last_name fields will be form input elements. The ack attribute requests an acknowledgement be displayed in red when data is inserted.

How do I change the sizes and types of the input elements in a form?

Formatting takes place in either the columns, fields or cols attribute. Changing the title of a column precedes the name with a carrot (^) character. Element formatting follows the name with an exclamation point (!) in single quotes ('). Within the single quotes can begin optionally with the type of form element followed by a space, then either (a) 1, 2 or 3 integers separated by semicolons, or (b) contents of an HTML style attribute. 1 to 3 integers correspond to width, height and vertical-align values in pixels. An HTML style attribute can specify width, height, vertical-align, font-size, font-family, and anything else. For example,

[abase form="1,insert" table="employees" columns="first_name!'width:100px;font-size:12pt;',Password^last_name!'password 100;15'$Add Employee" elements="first_name,last_name" ack="red"]

The format of the first name field will be a text box 100 pixels wide and the font size will be 12 point. The height defaults to 18 pixels. The last name field will be renamed on the form "Password" and the form element will be a password type (so what is typed on the screen is hidden). The password form element will be 100 pixels wide and 15 pixels in height.

How do I create a search form?

To specify a search form using ABASE, you will use at least the table="?", columns="?", elements="?" and form="?" attributes. If you would like your search results to appear on the same page, you do not need to specify the URL page of the search results in the form="" attribute.

[abase form="1,search" table="employees" columns="first_name,last_name$Search Employees" elements="first_name,last_name"]

In this example, if the search results were to appear on a different page, the form="" attribute will be form="1,search,/search-results/" where /search-results/ addresses the search results page.

On the searh results page, place the following shortcode to display a table with the results of the search. This example shows the use of the optional where="" attribute to restrict the search to records with `status`='1'. With the optional attribute echo="0" the user can view the page source and see the complete SQL statement for the search in the HTML comments.

[abase table="employees" columns="first_name,last_name" where="AND `status`='1'" echo="0" ack="green"]

How do I create a form to update a record in a table?

First you need a way to identify the record you want to update. To do so, we can use the rlink attribute to specify the first name as a "record" link.

[abase table="employees" columns="first_name,last_name" rlink="first_name" echo="blue"]

The first name will appear as a link. Clicking on a link first name will re-display the page with the primary key and value in the query string. To create the form, place the following shortcode on the same page.

[abase form="1,update" table="employees" elements="first_name,last_name" fields="first_name,last_name$Update Employee" ack="brown" echo="blue"]

When a record identified by its primary key is in the query string, an update form for that record will be produced.

How do I send an email?

The execution results from a shortcode execution is sent as an email if there is a valid emailto="<email_address>" attribute is present. <email_address> can be either an actual email address or a column name that contains valid email addresses. If an email address is specified as a column, the email will be sent if the shortcode execution results in exactly one record. If the email is sent, the content will not display on the page.

To produce more readable email content, add a <content> field followed by a [/abase] closing shortcode, and <content> will appear before the original shortcode output.

For example, the following ABASE shortcode will send an email to xyz@123.45 that says "Hello Joe, how are you?"

[abase emailto="xyz@123.45"]Hello Joe, how are you?[/abase]

Replace xyz@123.45 with your email address and view the page with the shortcode. You should not see the email content on the page but you should get the email.

ABASE shortcodes can be placed within <content> as long as the ABASE shortcodes within <content> use "ABASE" if the email shortcode uses "abase" (or vice versa). So, for example, the following shortcodes will email the contents of the employee table:

[ABASE emailto="xyz@123.45"]Joe, here is the contents of the employee table:[abase table="employees"]Fred.[/ABASE]

How do I add a field to a table without using phpMyAdmin?

Use an SQL statement. For example, to add an integer field "employee_level" to the employees database, execute the following just once:

[abase sql="ALTER TABLE `employees` ADD `employee_level` INT NOT NULL"]

Once the page containing the shortcode is displayed (and the new field added to the table) then be sure to delete or disable the shortcode so it doesn't execute again.

How do I upload an image or PDF file?

File uploads are associated with a field in a database record. When uploading a file, the file is stored in a directory defined by the table name, column name and primary index. The address of the file (path and file name) is stored in the table cell defined by the table name, column name and primary index. The images or files attribute specify columns that correspond to file uploads.

For example, if we start by creating a table as follows with an employee first name, last name and picture:

[abase sql="CREATE TABLE `employees` ( `employee_id` int(11) NOT NULL AUTO_INCREMENT, `first_name` varchar(100) NOT NULL, `last_name` varchar(100) NOT NULL, `picture` varchar(250) NOT NULL, PRIMARY KEY (`employee_id`) )"]

A form to add an employee with the picture field identified as an image would produce a browser file upload form element for the picture entry:

[abase form="1,insert" table="employees" elements="first_name,last_name,picture" columns="first_name!'100',last_name!'100',picture!100$Add Employee" images="picture" ack="green"]

Displaying the employees table with pictures would be as follows:

[abase table="employees" columns="picture!'width:50px;vertical-align:-25px;',first_name,last_name" images="picture" style="width:500px;"]

The images attribute declares picture as an image. The exclamation point (!) precedes formatting for the image. width:50px will resize the image to 50 pixels wide and vertical-align:-25 will lower it 25 pixels so the adjacent rows will appear in the middle instead of at the bottom of the row.

How can I put individual passwords on table records?

A password on a record will prevent updates or deletions to the record without a password. In the shortcode declaring an update or delete form, include a password attribute identifying the column password. The password input must equal the contents of the password field for the update or delete to take place.