Understanding the query list (Advanced)
From FacileWiki
Contents |
Introduction
In my previous article Understanding the query list I talked about some of the ways it is possible to customise the query list to display data in a more dynamic manner, in this article I will create a test form that uses the most commonly used methods.
We will create a two page form, on the first page will be the query list and the second will be to view data in more detail, for the purpose of the test form I will use the user database table of joomla/mambo everyone has this so it always makes a good example.
Setup
First of all create a new form lets call it "tabular", it is important that you use lower case text only. Create a query list on the first page and use the settings below...
Settings tab
Notice css class has not been used this is entirely up to you.
Query tab
A nice simple query that gives us user data by id, if you have a large number of users for the purpose of this example it may be wise to limit the number of database entries to display by adding for example "limit 5" at the end of the query.
Columns tab
And finally we need a column to show our custom html code...
The column settings...
We will use a nice simple table for our example, which will look like this...
Custom Html
Now we have our query list setup we can add the final part, insert the custom html code below into the "User Information" column value of your query list...
<?php
//create custom table
return
'<table border="1" width="100%">'.
'<tr><td width="50%" align="left"><b>Name<b></td><td width="50%" align="left"><b>Username</b></td></tr>'.
"<tr><td>$row->name</td><td>$row->username</td></tr>".
'<tr><td width="50%" align="left"><b>User type</b></td><td width="50%" align="left"><b>Ref</b></td></tr>'.
"<tr><td>$row->usertype</td><td>$row->id</a></td></tr>".
'</table><br>';
?>
As you can see we are using php to to display the table, it is important to note the sort of quotes that encasulate each line of the code. Normally you would just use single quotes but if you want to return a variable you need to use double quotes, when using double quotes any double quotes contained within must be escaped, in other words canceled out otherwise errors will occur.
For example..
"<tr><td width=\"50%\" align=\"left\"><b>Name<b></td><td width=\"50%\" align=\"left\"><b>Username</b></td></tr>".
If you look at the double quoted parts of the custom table code you will notice that it is this part of the code that is displaying the values we are going to return. To understand this we need to think about the $rows variable in our query, In my first article Understanding the query list I said this about the $rows variable..
"The data that the variable '$rows' holds is an array where each element contains a data structure containing all used columns of the query."
So the variable $rows has all the information about each user stored in an array, each user id (element) holds data about the user which is set by our query, the query calls all the columns in the database table (used columns).
To display data about the user the custom table contains the following code...
$row->name $row->username $row->usertype $row->id
All this does is display particular data about the user that is held in the array, so in our case we want the users name, username, usertype (whether they are a special users or regular user) and finally their id number. Dont forget that each piece of code reflects exactly the name of the database table column that we want data for, if the column name is spelt incorrectly errors will occur.
NB: It is also possible to use custom code in the header of the query list.
Now test your form if you have done everything correctly then the query list should display the data with our custom code in each row of the list.
First column mode
Facile forms allows you to set the first column of the query list to one of three modes Normal, Checkboxes or Radio buttons, this is called "First column mode" and should be currently set to normal, in our example form we will be using checkboxes for our first column, DO NOT change this now.
In order for us to use first column mode we need to create a new column in our query list lets call it "id", this will return the id of the user for each record, give the id column the following settings..
You will notice the new column defaults to the bottom of the list you must reorder it to the top otherwise the "User Information" column will become checkboxes which obviously we want to avoid.
Now change the column width of the "User Information" column to 99%, finaly go to the query list settings tab and change first column mode to checkboxes, the first column of the query list should now be checkboxes.
Using the id column
Although we have changed the id column to display as a checkbox for each entry in the database, the value of the column is retained. When a selection is made from the list that value will be carried when the form is submitted or another action using the value is performed.
To be able to use the checkboxes we need to add a few more elements to page one of the form, these elements are...
- A regular button called "details"
- A hidden input called "mytask"
- A hidden input called "view_id"
You will have to rearrange your form to accommodate the "details" button, leave the two hidden inputs as created.
The Details button
We are going to use the "details" button to perform three actions...
- Validate that a selection has been made.
- Set hidden input values.
- Submit the form.
Your button settings should be set as below...
For the code framework within the scripts tab of the "details" button place the following code...
function ff_details_action(element, action)
{
//check to see which record was selected
var rows = ff_getQuerySelectedRows('users');
//loop through array to find match
if (rows.length) {
row = rows[0];
for (i = 0; i < row.length; i++);
//set id of record to edit
var r = ff_getQuerySelectedRows('users');
var v = ff_getElementByName('view_id');
v.value = r[0][0];
//submit form
ff_getElementByName('mytask').value = 'view';
ff_submitForm();
} else {
//if non selected
alert('You must select data to view!');
}
} // ff_details_action
Firstly the code looks for a selection by looping through the entire query list, secondly if it finds a selection it sets the value of our hidden input "view_id" to that of the selected record and submits the form setting the value of our hidden input "mytask" to view, if no selection was made at all an alert message is displayed.
Submitting the form
Now the "details" button is set we can deal with submitting the form, if you were to press the "details" button now it would only validate and then redirect to page one of our form.
We are going to use a switch method for our submit, the advantage of this is that if a number of buttons are used within the form it can perform a different submit action for each of the buttons, if you would like to see this in action I recommend you take a look at Basic database application version 2 as it has an expanded switch.
Edit the form and go to the "Submit pieces" tab and place the code below into the "Begin submit"...
// load the standard form creation utilities
$this->execPieceByName('ff_InitLib');
$task = '';
foreach ($this->submitdata as $data)
switch ($data[_FF_DATA_NAME]) {
//for submit
case 'mytask':
$task = $data[_FF_DATA_VALUE];
break;
default:
break;
}
switch ($task) {
//redirect to edit page
case 'view':
$vid = ff_getSubmit('view_id');
header("Location: {mossite}/index.php?option=com_facileforms&ff_name=tabular&ff_param_view=$vid&ff_page=2");
break;
default:
break;
}
What this does is take all submitted element names and values and makes them available to the "view" case, we are only interested in the record id which is held in the hidden input called "view_id", we take that submitted id and assign it to the variable $vid, next we perform a redirection which contains a parameter the parameter then sends the value of $vid to the second page of the form...
&ff_param_view=$vid
We have no page two of our form so click "Add page behind" to add our second page.
Details display
Before we can view any details we need to create a method to receive the value of the parameter, effectively when the form is submitted we refresh the form so what we will do next is create an action that is run every time the form loads.
Edit your form and go to the "Form pieces" tab, "Before form" and select custom, add the code below...
// load the standard form creation utilities
$this->execPieceByName('ff_InitLib');
//set globals
global $rec;
//recieve parameter
$vw = ff_getParam('ff_param_view');
//query the database
if ($vw) {//check vw has a value
$record = ff_select("select * from `#__users` where `id`='$vw'");
$rec = $record[0];
} else {
$vw = null;
}
The code receives the value of the parameter "view" and assigns it to the variable $vw, now we have an id we can query the database for the user with that id, an array is created containing the user data which we can then access using $rec, we now make $rec into a global variable allowing us to use it anywhere in the form.
For page two we need to add two elements...
- A regular button called "back".
- A static Text/Html called "details".
The "back" button is pretty straight forward simply use the library function "ff_previouspage" in the scritps tab to redirect back to page one of the form.
Place the code below into the "details" static Text/Html element...
<?php
//set globals
global $rec;
if ($rec){
return
'<table border="1" width="100%">'.
'<tr><td width="100%" colspan="2"><b>Details</b></td></tr>'.
'<tr><td width="50%"><b>Name</b></td><td width="50%"><b>Username</b></td></tr>'.
"<tr><td width=\"50%\">$rec->name</td><td width=\"50%\">$rec->username</td></tr>".
'<tr><td width="100%" colspan="2"><b>User type</b></td></tr>'.
"<tr><td width=\"100%\" colspan=\"2\">$rec->usertype</td></tr>".
'<tr><td width="50%"><b>Registered</b></td><td width="50%"><b>Last visit</b></td></tr>'.
"<tr><td width=\"50%\">$rec->registerDate</td><td width=\"50%\">$rec->lastvisitDate</td></tr>".
'</table>';
} else {
$rec = null;
}
As you can see this works in exactly the same way as the custom table code in the query list its self, the only difference being it shows far more detail about the user...
If you have done everything correctly then you should be able to make a selection and view the user details in more detail.
Links and redirection
A simpler but less versatile method of viewing data in more detail is to use a redirect, this is pretty much the same redirect as we use in the submit but we are going to place it directly into the custom table code as below...
<?php
//create custom table
return
'<table border="1" width="100%">'.
'<tr><td width="50%" align="left"><b>Name<b></td><td width="50%" align="left"><b>Username</b></td></tr>'.
"<tr><td>$row->name</td><td>$row->username</td></tr>".
'<tr><td width="50%" align="left"><b>User type</b></td><td width="50%" align="left"><b>Ref</b></td></tr>'.
"<tr><td>$row->usertype</td>".
"<td><a href=\"{mossite}/index.php?option=com_facileforms&ff_name=tabular&ff_param_view=$row->id&ff_page=2\">$row->id</a></td></tr>".
'</table><br>';
?>
Notice as before the redirect contains the parameter "view" which is picked up in the same way as previously...
<a href=\"{mossite}/index.php?option=com_facileforms&ff_name=tabular&ff_param_view=$row->id&ff_page=2\">$row->id</a>
Images
Sometimes you will find that you want to use images along with text or to replace text all together, in our example we are going to add an image to indicate whether a user is a special user such as super administrator, admin, publisher etc as compared to a standard registered user.
There are a large numer of administration images to be found in the /administrator/images folder of your joomla/mambo installation that can be very useful and also very good when you are writting forms to be used on multiple sites as you do not have to upload them for each site, we are going to use a simple tick and cross to indicate user type.
We will again need to modify our custom table code to accommodate our user check...
<?php
//check manage value
if ($row->usertype == 'Registered') {
$img="publish_x.png";
} else {
$img="tick.png";
}
//create custom table
return
'<table border="1" width="100%">'.
'<tr><td width="50%" align="left"><b>Name<b></td><td width="50%" align="left"><b>Username</b></td></tr>'.
"<tr><td>$row->name</td><td>$row->username</td></tr>".
'<tr><td width="50%" align="left"><b>User type</b></td><td width="50%" align="left"><b>Ref</b></td></tr>'.
"<tr><td>$row->usertype <img name=\"admin_img\" src=\"{mossite}/administrator/images/$img\" border=\"0\"></a></td>".
"<td><a href=\"{mossite}/index.php?option=com_facileforms&ff_name=tabular&ff_param_view=$row->id&ff_page=2\">$row->id</a></td></tr>".
'</table><br>';
?>
We know that any other user type than "Registered" is a special user so the code checks the "usertype" and if it finds the user is "Registered" sets the "$img" variable to a cross, if any other result is found the "$img" variable is set to a tick. You could use this method in your "details" table on page two also if you wished.
The code to display the image is standard html but we add the "$img" variable instead of the specific name...
<img name=\"admin_img\" src=\"{mossite}/administrator/images/$img\" border=\"0\"></a>
Review your form and you should see the correct image next to the user type for each record.
Resources
The completed example Tabular can be downloaded from the Facile Forum, please feel free to ask any questions there.
As mentioned before it is also worth looking at Basic database application version 2 for a more complex example.
--Boldee 12:45, 8 June 2008 (CEST)