Drop Shadow

Easy SQL in PHP

PHP is designed to easily embed dynamic content into static web pages. Working with databases strains that to its limit. While some GUI software such as Dreamweaver make it easy to add database content to web pages when that database content is held within a single table, it rapidly becomes complex and hard to follow when data is stored across multiple tables.

This plug-in attempts to make it easier for you to add database content to your pages even when they cross multiple tables.

What do you need?

Each table in your database should have an ID field. Set them up as “ID”, integer, and auto increment. Some of these functions will work even without an ID field, but features such as automatic update won’t work unless there’s an ID field.

Set up the Plugin

The first step is to include the plugin. Somewhere before you need to display the database content, add the following:

<? include_once("/web/includes/sql.phpi"); ?>

Once you’ve included the PHP file, you can connect to your database:

<? $db = new MySQL('username', 'password', 'database', 'table'); ?>

Display data

If your data can be displayed as a sequence of column name and value pairs, you can display as easily as the following line:

<? $db->displayList(); ?>

The data will be displayed in a table, including headers and links to an automatically-created detail page.

If you want to use CSS on the table, the table has a class name equal to the database table’s name. You may also want to include a special style sheet designed for this plug-in. In the HEAD area of your web page, add:

<? $db->HTMLHead(); ?>

Show specific columns

By default, the plug-in will display all columns that “seem appropriate” for a list display. It will not display TEXT columns or BLOB columns, nor will it display VARCHAR columns with a maximum size greater than 120 characters.

If it leaves any columns out, it will display a “more info” link to the full details of the record.

If you want to specify exactly which columns are displayed, create an array of the columns you want to display, and add that between the parentheses:

<? $showFields=array('name', 'age', 'subject'); $db->displayList($showFields); ?>

In this example, only the fields ‘name’, ‘age’, and ‘subject’ will be displayed.

More options

The above three lines—include, new MySQL, and displayList—can handle all of your basic display needs. If you need further control over the display of your data, or if you want to modify data as well as display it, you can do that also:

Multiple TablesThere are a few ways to work with multiple tables in the Easy SQL plug-in, depending on what you want to do with the tables.
Display OptionsYou can change how your rows and your data are displayed, from the order to the format.
Modifying RowsThe Easy SQL plug-in also makes it easy to add, replace, and delete data.
Special Field TypesThe Easy SQL plug-in can handle e-mail addresses, Unix timestamps, and other special data types for you.
Form OptionsThe Easy SQL plug-in can be set to handle your custom forms, either using the custom form information or ignoring it in an intelligent manner.
ExamplesHere are a couple of examples of the SQL plug-in in action, including real-world uses.
Direct AccessIf you need to access your rows directly, you can do so with these special functions. You can also ask for specific entries from any table.
Expert OptionsSome advanced features of the easy SQL plug-in will help you debug more complex pages.