Recent Changes - Search:
Cookbook /

UpdateForm

Summary: Easily create forms to display, add, and update records in a MySQL table.
Version: 1.3
Prerequisites: MySQL database
Status: updated 8/20/2017. Live demo at interdepweb.com
Maintainer: Ben Stallings
Categories: CMS, SystemTools, Forms, PHP72

Questions answered by this recipe

  • How can I create online forms to insert and edit records in a MySQL database without hours of tedious development time?
  • How can I make a <select> drop-down menu in PmWiki that gets its values from a database table?
  • How can I allow my wiki users to update their own contact information for my database-driven mailing list, directory, or event?
  • How can I make PmWiki into a flexible and easy-to-maintain front-end for a database?
  • How can I automatically generate tabindexes for lengthy forms, so that I don't have to type a tabindex for every single form element?

Description

UpdateForm builds on the syntax and functionality of the forms.php script included with PmWiki to allow a form to display a specified record from a database. (The record may be specified by a link from another page, for example by SelectQuery.) If the information is altered and the form submitted, the record will be updated in the database. If no record is specified, a new record will be created when the form is submitted.

No custom code must be written; just create a wiki form for each table you want to work with, and you've got an instant front-end interface for that table.

Better yet, the forms automatically recognize when they're being used to update a user's own information, so the user's ID doesn't need to be specified; it's taken from the session variable instead. The script automatically recognizes whether you're using UserAuth or AuthUser and defines the username accordingly. (Thanks to Helge Larsen for this fix!)

Skip down to the examples

Installation

  1. Put updateform7.phpΔ in your cookbook directory.
  2. Set the $UpdateUserID variable (at the beginning of the script) to match the name of the column (if any) that holds the wiki username. If your users won't be editing their own records, comment out the line.
  3. Define (either in the script or in config.php) the constants DB_SERVER, DB_NAME, DB_USER, and DB_PASS to match your database, like so: 
 define ('DB_SERVER', 'db1.example.com');
 define ('DB_NAME', 'my_database');
 define ('DB_USER', 'my_username');
 define ('DB_PASS', 'my_password');
  1. Include "$FarmD/cookbook/updateform.php" in your config.php for pages that are password protected. Please understand that this script allows anyone with read access to alter your database!

    For example, if the Registration group is password protected, you might type
    if (FmtPageName('$Group',$pagename) == "Registration") include_once "$FarmD/cookbook/updateform.php";

Notes

The syntax is the same as for (:input:) forms, but the word "input" is changed to "update", and some additional parameters are used.

Input and Update tags can be mixed and matched within a single form; for example, you can use an (:update select:) within an input form to create a drop-down menu, or an (:input text:) within an update form if you don't want to retreive that field from the database. (:update end:) produces exactly the same output as (:input end:), namely </form>.

Required Parameters of the (:update form:) tag

  • table: not 'tables' -- only single-table queries are possible as yet.
  • fields: the fields of the table that the form will update. In a future version I hope to be able to infer this information from the rest of the form, but as yet you still have to list all the fields here.
  • where: the column(s) of the table whereby the record will be specified, one of which should ideally be an auto-increment primary-key field.

Optional Parameters of the (:update form:) tag

  • action: by default, a form submits to the page it's on, which is fine most of the time. However, by using two UpdateForms on two different pages, you can take data from one table and submit it to a second table. This can be useful if you want to preview changes before making them public. See "pitfalls" below.
  • method: UpdateForm assumes that GET data tell it which record to display, and that POST data are intended to go into the database. So if you use one form to select the record to be displayed by a second form, be sure to specify method=get.
  • required: I strongly recommend that you specify which fields are required for the form to be accepted. This can prevent bad data from overwriting good data in your database. Example: required=firstname,lastname
  • default: If you specify that a field has a 'default' value in the database, it will not be overwritten by an empty ("") value. Example: default=contact_time means that the contact_time field should be allowed to keep its default value as defined in the database, unless a new value has been entered in the form. This is mainly useful when adding new records.
  • null: Conversely, specifying null=fieldname will cause an empty value in the form to be saved in the database as NULL rather than "". This is very useful for checkboxes, since an unchecked checkbox submits no value and so won't overwrite the existing value unless you tell it to. This is mainly useful when editing existing records.
  • redirect: Send the user to another page if the insert or update is successful (but not if it's unsuccessful). This can save you some time if you want to return to a menu or list every time after you use the form. However, this can be disorienting to the user because there will be no indication of whether the data were saved or not, so consider using an (:include:) statement instead. Remember to provide full path to redirected page, like redirect=Main/BlahPage not redirect=BlahPage.
  • timestamp: Insert the current date and time, in the format YYYY-MM-DD HH:MM:SS, into a field when the record is changed. Example: timestamp=last_modified. The field you're timestamping should not be included in the "fields" parameter, nor in the form.
  • delete: If you specify delete=fieldname in the form tag, and if a field by that name is submitted with the value 1, and if all the other required fields are present, then the record will be deleted from the database. You can cause related records in other tables to be deleted at the same time by defining the array $UpdateDependencies in your config.php like this:
    $UpdateDependencies = array (
 'ParentTable.parent_id' => 'table1.parent_id,table2.parent_id',
 'Catalog.part_number' => 'inventory.widget_id'
)
    Then when a record is deleted from ParentTable, any records in table1 and table2 with matching parent_ids will also be deleted, and when a record is deleted from Catalog, any records in inventory whose widget_ids match the deleted part_number will also be deleted. It probably goes without saying that you should be very very careful with this feature!

Optional Parameters in Form Elements

  • mask: Supplies the format for a number typed into a text field. For example, (:update text zipcode mask=#####-####:) will format the number as five digits followed by a dash and four more digits, and mask=(###)###-#### is a North American phone number. The mask is enforced by an optional, external Javascript -- if you don't want to use it, just comment out the line near the top of the updateform.php script.

    This feature is especially useful for datetime fields, since users seem to have trouble remembering to use the format YYYY-MM-DD HH:MM:SS. But slap on mask="####-##-## ##:##:##" and they'll have no choice but to provide valid data!
  • tabindex: If you specify tabindex=1 on the first field of your form (or any number in any field), the next form element will be tabindex=2 and so on until you tell it to stop with tabindex=0. This feature can save you a lot of typing, and save your users a lot of pointing and clicking, since it allows them to navigate the form without using the mouse.

Drop-down menus

Drop-down menus can be generated, using another query to supply the list of values. This is extremely useful even outside of an UpdateForm; for example, you can use a drop-down inside a traditional (:input:) form to set the parameters for a SelectQuery.

Required parameters:

  • name: if you don't specify a value or label, the name will be used in their place, saving you some typing.
  • from: the table to pull the data from

Simple example: (:update select cat from=menagerie:)
This will generate code that looks like, 

 <select name='cat'>
  <option value='tabby'>tabby</option>
  <option value='persian'>persian</option>
  ...
 </select>

Optional parameters:

  • value: If the field whose value you want to save doesn't have the same name as the field you're saving it in, specify value=fieldname.
  • label: If the label you want to display is not the same as the value you want to save, specify label=fieldname.
  • where: If you don't want to show every row in the table, specify where="conditions".
  • order: To sort the rows.
  • null: To provide an option at the top of the list that has no value and appears when no other option has yet been selected.

Complex Example: (:update select name=widget_id value=part_number label=catalog_name from=inventory where="catalog_name > 'E'" order=catalog_name null="Select your widget":).
This will generate code that looks like, 

 <select name='widget_id'>
  <option value=''>Select your widget</option>
  <option value='00305023.82F'>Egyptian Rat Screw</option>
  <option value='00025325.52Q'>Fibble Extender</option>
  <option value='8535937X.42X'>Glabbish</option>
 </select>

Textareas

Unlike an (:input textarea:), an (:update textarea:) can have a value -- either specified by the database record or explicitly as (:update textarea memo value="blah de blah":). If both are given, the value from the database will override the default value. Note that the value from the database can contain line breaks, but the value specified in the markup tag cannot, because it's part of the tag, and tags can't have line breaks in them.

Examples

Here's an example form, with linebreaks inserted for readability. See it in action at interdepweb.com. 

(:update form table=members 
fields=memberid,personid,startdate,member_type,dues_paid,
payment_id,hours_pledged,notes 
required=personid,startdate,expiredate,member_type 
null=dues_paid default=hours_pledged where=memberid 
redirect=SelectForm/Members:)

||border=0
||! Member Name:(:update hidden memberid:)||(:update select 
personid label="CONCAT(lastname,', ',firstname)" from=people 
where="lastname IS NOT NULL" order=lastname,firstname
null="Select a person" tabindex=1:) ||
||! Start Date:||(:update text startdate size=10 mask=####-##-##:) (YYYY-MM-DD) ||
||! Expiration Date:||(:update text expiredate size=10 mask=####-##-##:) (YYYY-MM-DD) ||
||! Type:||(:update radio member_type household:)Household\\
(:update radio member_type student:)Student\\
(:update radio member_type supporting:)Supporting ||
||! Dues Paid:||(:update checkbox dues_paid 1:) ||
||! Payment ID:||(:update text payment_id size=20:) ||
||! Hours Pledged:||(:update text hours_pledged value=5 size=3 mask=###:) ||
||! Notes:||(:update textarea notes rows=3 cols=30:) ||

(:update submit value="Submit Changes":)
(:update end:)

If the above form is loaded with a memberid specified (for example by a SelectQuery), it will automatically pull up that member's information and display it -- even the radio buttons will display the stored information -- and any changes submitted will be stored in the database. If no memberid is specified, the form will be blank and a new membership record can be inserted (assuming that memberid is an auto-increment field).

null=dues_paid means that if the dues_paid checkbox is not checked, it will receive a NULL value (or 0 if the field is numeric and defined as NOT NULL). Had I not specified this, an unchecked checkbox would have no effect because it has no value.

The default=hours_pledged parameter means that the hours_pledged field has a default value specified in the database, and so if no value is received in the form, the field should be allowed to keep its default value. Note, though, that I've also specified "value=5" in the hours_pledged text blank... this makes the default visible to the user. If another value has been saved in the record already, that value will appear in place of 5.

Now suppose we want to let the users update their own e-mail addresses. Just set $UpdateUserId = 'user_id' (near the top of the updateform.php file), and write a form like the one below. The current wiki user's own information will be displayed in the form, and no user will be able to modify any other user's information. 

 (:updateform table=people fields=email where=user_id:)
 E-mail address: (:update text email:)
 (:update submit:)(:update end:)

Note that this form does not include the peopleid field, which we saw in the previous example was the primary key for this table. Leaving the primary key field out of the form entirely is the best way to ensure that it doesn't get changed or compromised.

Pitfalls to watch out for:

  • As with most PmWiki markup, linebreaks within tags will break the tags. If you copy the code above, be sure to remove all linebreaks that appear between (: and :).
  • If you use field names that are reserved words in MySQL, such as delete, you may need to put them in `backquotes`, for example fields=name,address,`delete`.
  • The ID of the record to display must be submitted via GET (on the command line, like path/group/page?recordid=x) and not via POST. UpdateForm assumes that incoming POST data is meant for the database.
  • If you list a field in the 'fields' parameter, you must include it in the form. If you don't want it to be visible (because it's the primary key, for example), then by all means make it hidden, but if it's not in the form the update query will attempt to erase its value when the form is submitted. If the form edits a user's record, it's safest to leave the primary key field out of the 'fields' parameter altogether, as I've done in the second example above, so that the update will leave it unchanged.
  • If a submitted form contains a value for the key field(s) (named in the where parameter), UpdateForm will attempt to update an existing record that has those values rather than insert a new one. This means that you can only use the same form for inserts and updates if your key field is set to auto-increment. Otherwise you will need two forms: one to insert (with no where parameter) and one to update (with a where parameter).
  • UpdateForm does not currently check the source of incoming data. That means that if data submitted through another form (for example, one that sets the criteria for a SelectQuery on the same page) satisfies the criteria of the (:update form:) statement, the record will be updated, even if that wasn't what you had in mind! To protect against this:
    • always specify required fields, and
    • avoid creating any other forms that send data to the same page as an UpdateForm.
  • Note that unlike wiki pages, when MySQL records are deleted, they're gone forever. I recommend having a 'deleted' column in the table and providing a checkbox to "flag" records as deleted rather than actually deleting them. If you must delete records, consider using a second (:update form:) statement on the same page to insert the data into another table for safekeeping. Add a timestamp to this second table, and you'll have a log of all changes that are made to the first table.
  • As with (:input form:), the action of an (:update form:) must either be a complete URL or a page within the current group (if you specify an action at all). If you use action=Group/Page or action=Group.Page in either an input or update form, the contents of your form will not be received by the specified page, even though the HTML code will look just fine. I'll fix my bug just as soon as PM fixes his. ;-)
  • Although the (:update select multiple=multiple:) markup will allow you to generate a box where multiple lines can be selected (by holding down the ctrl key), only the last line selected will be received and processed. Similarly, you can have multiple checkboxes for a single field, but when the form is submitted only the last checked box will be received and processed. This means that there is currently no way to use the SET datatype with UpdateForm. If you'd like to help add that functionality, be my guest!
  • The automatic tabindexing feature only applies to (:update:) tags, so if you use (:input:) tags as well, you'll need to manually tabindex them and also the following (:update:) tag to get the indexing to resume with the right number. Similarly, if you include a form or part of a form from another page, the tabindex counter will need to be told where to start on the included page and where to resume on the including page.

Release Notes

If the recipe has multiple releases, then release notes can be placed here. Note that it's often easier for people to work with "release dates" instead of "version numbers".

2006-05-14 version 1.0 posted.

2006-07-19 version 1.1: minor bug fix; 'default', 'null', and 'redirect' parameters added. Thanks to Helge Larsen for his contributions!!

2006-07-20 added null values for select boxes (drop-down menus)

2006-07-29 minor bug fixes, added timestamp capability.

2006-08-28 version 1.2: numerous bug fixes, input masks, deletion, automatic tabindexing.

2006-02-08 version 1.3: provides user-query functionality to SelectQuery.

2017-08-20 version 1.3 updated to use PHP 5.3 through 7.x, renamed to updateform7.php (no additional functionality).

Future Plans

The DataQuery recipe, in combination with ZAP, is designed to replace UpdateForm. Once the DataPlates recipe is completed (spring 2007), that combination should be even easier to use than UpdateForm (whereas now it is harder).

Comments

2006-12-19 by GhostRider? Hi, I was trying to use updateform on test site, and probably hit a bug. Test url was like: http://blablah/~testuser/pmwiki.php. After creating a really simple form on page http://blahblah/~testuser/pmwiki.php/Main/Submit, 'submit' button was trying to submit data on http://blahblah/Main/Submit. I dig a bit in code, and found out the following code which is doing that nasty thing: 

# (:update form:)
SDVA($UpdateTags['form'], array(
  ':args' => array('action', 'method', 'table', 'fields', 'required', 'where', 'tabindex'),
  ':html' => "<form \$UpdateFormArgs>",
        'action' => '/'.$pagename,
        'method' => 'post'));

action seems wrong, as it is supposed to IMHO be:

'action' => $ScriptUrl.'/'.$pagename,

It fixed my problem. Think it might be useful to others, so I submit.

Regards,

Thanks, GhostRider. I've made that change in the current version.

Ben Stallings February 08, 2007, at 10:39 AM

2007-31-01 by GhostRider?

Another observation here, after a successfull submission to db, Update Form displays information about it, but fields are still filled with previously submitted values which I find quite annoying. Is it possible to reset form after successfull submission ?

Well, you have two options... you can either provide a link to the form page (which will reset the form because no record is specified), or you can have a separate form for data entry which uses input tags instead of update tags. Update tags will always display the current record when a record is specified. Sorry for the confusion.

Ben Stallings February 08, 2007, at 10:39 AM

Input tags instead of update tags did the thing, thank you :)

2007-03-03 by GhostRider?

2007-03-04 by GhostRider?

Another question, hope it won't bother you much ;). I'm trying to get the following: - perform a query to db (using e.g. SelectQuery) - provide 'edit this row' link inside the query above like this:

idwhatthingcomment'edit data in this specific row link'

I need to provide a way for my users to edit entries in db. It's quite tricky I think.

Not tricky at all! SelectQuery is designed to do just that. I'll address your question on the SelectQuery page.

Ben Stallings March 04, 2007, at 10:33 AM

2008-04-13 by Umlauf?

Is there an easy way to update or add one form field (entered or selected by user) to other fields of other tables than the one the form users see? I.e. can I do sort of hidden add/'insert into' actions that are run with the same submit button, within the same form? And if so, could you provide example code?

Also, could you show an example where in one form you obtain data from one table, and add that selected data to a different table? I.e. do cross-table copy-paste like actions. I hope it can be done using UpdateForm (and maybe SelectQuery?), but I'm tempted to abandon pmwiki for this entirely.

By the way, the %center% tag does not seem to work in front of Submit buttons, or am I missing something?

UpdateForm as it currently stands has no ability to rename fields, so you can only save incoming data to a table field with the same name as the form field it was submitted in. However, if you have fields with the same names in more than one table, you can save to multiple tables like this: 

 (:update form table=tblPlants fields="plant_id,common_name,scientific_name" where=plant_id:)
 (:update hidden plant_id:)(:update hidden change_id:)
 Common Name: (:update text common_name:)
 Scientific Name: (:update text scientific name:)
 (:update end:)
 (:update form table=chgPlants fields="plant_id,common_name,scientific_name" where=change_id:)
 (:update end:)

In order to save incoming data to a field with a different name, you would have to either use a form processor like ZAP to rename the fields (and then use DataQuery instead of UpdateForm) or add a feature to UpdateForm so that you could use a syntax like 

 (:update form table=tblPlants fields="plant_id AS id":)

If you need help with that, let me know.

Ben Stallings April 17, 2008, at 09:07 AM

Regarding your other questions, if you want to pull data from a SelectQuery, display it in a form, and upon submitting that form save the data to another table, you would do something along the lines of 

 (:selectquery columns=firstname tables=users options=userid display=custom:)
 (:update form table=losers fields=nickname where=loser_id:)
 Your Nickname: (:input text nickname "{`firstname`}":)
 (:update submit:)
 (:update end:)

Note I am using an input rather than an update for the text field, so that the value from the SelectQuery will be used instead of the one from the UpdateForm. Depending on what you have in mind, you might use an update field instead.

To center the submit button (or anything else), if %center% doesn't work, use 

 >>center<<
 (:update submit:)
 >><<

Hope this helps. Ben Stallings April 18, 2008, at 01:43 PM

I had a problem where there was probably a conflict with another plug-in. Every time I edited the form, updateform.php would process the form as if someone had submitted to the database. This was fixed by adding:

if ($action == 'edit') return true;

to the beginning of updateform.php before any other processing takes place. Now the form no longer attempts to process data when the markup is edited. XES February 02, 2009, at 05:02 PM

See Also

SelectQuery, DataQuery

Contributors

Ben Stallings

User notes +1: If you use, used or reviewed this recipe, you can add your name. These statistics appear in the Cookbook listings and will help newcomers browsing through the wiki.