Buy Now
Sign in

FAQ search

How can I edit a record from a database table?

Details
Published: Thursday, 04 June 2015 19:29

If you have saved form data to a database table you often want to be able to re-load it into a form for editing. This FAQ tells you how to load a record using the DB Record Loader action.

To populate a form for editing you want to use the DB Record Loader (not the DB Multi-Record Loader) in CFv4, or the DB Read action in CFv5. Drag a copy into the On Load event of your form. The inages below are for CFv4, v5 works in a similar way.

Sometimes it's useful to have an 'edit' version of a form as well as a 'create' version for new records. This is especially true if there are some inputs that should not be editiable. You can use the Copy icon in the Forms Manager toolbar to create a second version of your form and then rename and edit it. 

Simple lookup

On the Basic tab of the DB Record Loader put the column name that you want to look up in the DB Field Box; select the table in the Table box; put the name of the parameter in the $form->data array that holds the look-up value in the Request Param box.

This would work if the URL calling the form included, for example &article_id=999 or a preceding form had a select drop-down called article_id with the article id as the value.

If the parameter is included in the page URL or passed from a previous form step that's all you need. 

Note that it's usually easier to use the id or cf_if column of the table rather than a column containing text like a name as names may contain characters, like spaces, that are not allowed in URLs and so need to be URLencoded before they can be used.

Lookup using the User ID

To use the current User ID put user_id in this box; save the DB Record Loader; then drag a Custom Code action into the same event and move it up before the DB Record Loader; open the Custom Code action and add this code: 

<?php
$juser = /JFactory::getUser();
$form->data['user_id'] = $juser->id;
?>

Using Model IDs

ChronoForms has a Model ID feature that can be used to separate out data from a particular table. This is mostly useful in complex forms where you are working with more than one table and some of the column names are the same. This means that, for example you cound have both user['name'] and team['name'] in the same form and keep the two 'name' input values separate.

If you don't need it, you can safely ignore the Model ID feature.

If the Model ID is turned off - that is the 'Load Under Model ID' option is set to 'No' - then the data from the database table is loaded directly into the $form->data array as for example $form->data['column_name']. This data is treated in the same way as any other entry in the $form->data array; it can be used to replace {column_name} and will set the value of any form input with name='column_name'

If the Model ID is turned on then the data from the database table will be loaded into a subarray in the $form->data array as for example $form->data['ModelID']['column_name'].

The Model ID used will be either (a) the key you have entered into the 'Model ID' box; or (b) if the Model ID box is empty, the CamelCase version of the table name e.g. JosContent or  JosChronoformsDataMyform

When a Model ID is set you need to use the Model ID when you access the data. In curly brackets use {ModelID.column_name} e.g. {JosContent.title} and form input names need to be array names like name='ModelID[column_name]' e.g. name='JosContent[title]'.

This sounds more complicated than it is in practice but you do need to take care to be consistent. 

Saving an updated record

The ChronoForms DB Save action is usually the simplest way to save a record to the database. If the form data to be saved included a value with the same name as the table Primary Key and the value already exists in the table then that record will be updated.

For example, if the table primary key is 'id' and you have id=99 in your data then if there is a record with an id of 99 it will be updated.  

If there isn't a record with this id, or if there is no id value in the data then a new record will be created.

The simplest way of keeping the id of a record that you are editing is to add a hidden input into your form with the same name as the Primary Key. ChronoForms will automatically set the value of this to the value of the record you load and will update the record in the DB Save.

Note: The primary key must have a numeric value, the Joomla! code that ChronoForms uses may fail if the Primary Key is a text string.

If you created the table with ChronoForms then the Primary Key will be set to cf_id (unless you changed that in the Create Table dialogue).