Adding Database Tables
Adding Database Tables
06/21/08 (Edited 03/10/14)

Suppose you want to add a new table of data into WSN. For purposes of illustration, we'll say it's a list of widgets. You want to be able to display a list of widgits with a toplist and {WIDGETFIELDNAME} template variables, and you (perhaps) want to allow users to submit, edit and delete widgets through WSN. If so, read on. First, create your database table using phpmyadmin or whatever method you prefer. Use the same prefix for it that you use for the rest of the tables in that installation -- we'll say wsnlinks_widgets for our example.

Please follow these guidelines in making your table:
(1) The first column should be named 'id' and be an integer with a 'unique' index set to auto_incriment
(2) If you want to record the time the widget is added, include an integer field 'time'
(3) If you want to record the time last edited, include an integer field named 'lastedit'
(4) If it'll belong to a category, include an integer field named catid
(5) If it'll belong to a member, include an integer field name ownerid

After you've created the table, visit run yourinstalllocation/upgrade.php to get the table list cache updated. Then check the toplist generator and you'll find widgets available once you create the table. You'll use the template variables {WIDGETFIELDNAME} -- { + capitalized version of the table name with the prefix and any trailing 's' removed + capitalized version of any database field name + }. Displaying stuff isn't much use until we can create stuff, though... in our example we want to be able to create and delete widgets through the script, as well as possibly add other template functions. In order to enable these possibilities we need to create a widget class. Take the existing classes/search.php for a simple example. Copy that to widget.php, open it in a text editor. Change both instances of "search" to "widget" and change "searchestable" to "widgetstable" (this must be the name of your database table without the prefix, with the 'table' suffix). Note that as in our example the class name must be same as the file name (without the .php). Now that we have a class, we can create templates for adding and editing widgets. Our 'add widget' page will use a custom template named widget_add (that's what you can type on the manage templates page to create it) a.k.a. templates/yourset/custom/widget_add.tpl (if you prefer the filesystem). It will always be the name of the class followed by an underscore and an action type. The three available actions are add, view and edit: widget_add, widget_view, widget_edit templates. Our widget_add template needs to contain somewhere in it a form like this:

<form action="generics.php?action=add&amp;classname=widget" method="POST">
Field 1: <input type="text" name="field1">
Field 2: <input type="text" name="field2">
<button type="submit">Submit</button>

Replace "widget" in the above with your applicable class and {WIDGET with your appropriate template variable prefix. Replace field1 and field2 with the names of fields in your database table, and add as many more as are applicable for you. Of course, if applicable for the field type, use selectors or checkboxes or radio buttons or textareas. In order to let people submit their widgets, we'll put a link somewhere to generics.php?action=add&classname=widget . For displaying a list of widgets, of course, you'll use a toplist. The toplist can link to a 'view individual widget' page (widget_view) like this: generics.php?action=view&classname=widget&id={WIDGETID}

For editing widgets, you'll link to generics.php?action=edit&classname=widget&id={WIDGETID} . The widget_edit template's form will be the same as widget_add except for replacing action=add with action=edit in the form action line. To allow people to delete widgets, you can provide a link (or separate form) to generics.php?filled=1&classname=widget&action=delete&id={WIDGETID} In order to have the redirect messages after adding/editing/etc display the right text, you'll need to add new language items: redirect_widgetadded, redirect_widgetdeleted and redirect_widgetupdated. These get added automatically the first time they're needed.

If you want to require validation of widgets, use a 'validated' field which is a tinyint of length 1 with a default value of 0. As of WSN 8.1.0 this will automatically show up in the validation tabs (note you'll need to have added the classes/widget.php file mentioned earlier for it to work)... with older versions you'll need to create a custom admin template and copy the existing validation pages customizing to your new table's info. Set which usergroups need validation by adding a 'validatewidgets' usergroup field on the add fields page, then editing each usergroup as desired.

If you need to trigger "incomplete, please fix x" prompts on submissions and edits, use the pluggable function checkincompletes. If you want to allow searching of the new table, copy the existing search boxes but change whichtype=links (for example) to whichtype=widgets. Change all the fields specified to fields that exist in your widgets table. You'll need to make a template to display the search results for it as well -- name that widgets_search.tpl and copy the "search members" (searchmembers.tpl) into it. Then change to and to . Finally, change all the fields and template variables to ones that apply for widgets. To set the order of search results you can pass orderfield and orderascdesc in the form or url (for example, orderfield=votes&orderascdesc=desc).

You now have a complete widget system at your disposal. If you're a programmer you may wish to go further, though. You can edit the class to create a new template variable -- any method of a class is available as {WIDGETMETHODNAME} or {WIDGETMETHODNAME[parameter1 <,> parameter2]}. You can override the generic (generic.php) class' methods by putting new versions in your child class. Additional note: if you're going to associate a switch with a table, the switch name should be the table name without the prefix.

Advanced Programmer Notes

The name of the table is stored in $widgetstable, and the fields are in $settings->widgetsfields. You can use if (!$widgetstable) { $db->query("CREATE TABLE..."); } in a /modifications/ directory file to automatically create the table without the need for any setup process. Example useful bits of code:

$q = $db->select('all', 'widgetstable', 'ownerid=5', 'ORDER BY time DESC'. 'LIMIT 0,10');
$n = $db->numrows($q);
$bit = $template->extract('<!-- BEGIN WIDGET -->', '<!-- END WIDGET -->');
for($x=0; $x<$n; $x++)
$widget = new widget('row', $db->row($q)); // fetches next widget from query
$all .= $widget->replacements($bit); // replaces all {WIDGET template vars with correct values
$template->replace($bit, $all);
$widget = new widget('id', 5); // fetches widget #5
$text = $widget->replacements($text); // replaces all {WIDGET template vars with correct values

Description Drop in a new table and display and manage its data via WSN.
Views 3258 views. Averaging 2 views per day.

Previous Article | Next Article