Sparse: SQLField Tag

`sqlfield` Tag

Attribute List:

name
type
calType
isSearch
value
labelClass
labelStyle
constraints
numeric
max
min
required
displayValue
returnValue
enumset
Other attributes

This tag will display a field corresponding to one column in the returned data. It's the most customizable of the tags. By default, an sqlfield will turn into text (for display templates) or an input element (textbox, textarea, select box etc.) for the other template types.

Most sqlfields must be inside an sqlrow. The exception is "search" fields (see the isSearch attribute).

Type of tag: Self-closing

Attributes

name

Required: Yes
Templates: All
Values: Name of one of the SQL columns returned by the query

This simply tells which column of data the field will be associated with; i.e. which data to display or which data to collect (or both). Note that name must be unique in all cases, except if the type is radioSingle or checkSingle. If you used aliases in your query, you'll have to use the alias name here.

type

Required: No
Templates: edit, add
Values: See below

By default, every field will be turned into a proper input widget depending on the SQL type of the column associated with this tag. For example, "text" and "longtext" become textareas; "char" and "varchar" become text fields; "enum" becomes a drop-down list; "set" becomes a select list. By including this attribute, you can force it to be another widget:

text: Plain text (i.e. not editable at all).
textfield: A plain ol' text field. Available to all SQL types. Default type for char, varchar, integer, and real types.
textarea: A large text area. Available to all SQL types. Default type for tinytext, text, and longtext types.
password: A text field with password hiding. For all SQL types.
radio: A set of radio buttons. Only available to "enum" or "set" fields, or if the enum attribute is set on this tag.
radioSingle: A single radio button. The value of the button can be set with the value attribute. If you use an enumset, you can automatically display the label. Otherwise just write the text next to the button.
checkbox: A set of checkboxes. Only available to "set" fields, or if the enum attribute is set on this tag.
checkSingle: A single checkbox; works identically to radioSingle.
select: A drop-down listbox. Only available to "enum" or "set" fields, or if the enum attribute is set on this tag. Default type for enum.
selectMultiple: A list which allows multiple selection. Only available to "set" fields, or if the enum attribute is set on this tag. Default type for set.
hidden: A field which doesn't show up. You can use this to automatically fill in fields for an add template, or to give two fields the same value in edit templates, e.g. for foreign keys.
calendar: This will print out fields for month, day, year, hour, minute, and second depending on your calType.

Default Types:

MySQL Type	Sparse Type	Sparse CalType
TEXT LONGTEXT	textarea
DATE	calendar	y/m/d
DATETIME	calendar	y/m/d h:i:s
TIME	calendar	h:i:s
TIMESTAMP	calendar	y/m/d h:i:s
SET	selectMultiple
ENUM	select
Everything else	textfield

Note that you do not need to use hidden fields for the primary keys of the row - Sparse will take care of it! For every row, the primary keys for the tables will be automatically recorded in the form and sent along with the edit/delete/add request. You don't have to worry about it at all!

calType

Required: No
Templates: edit, add

This is only for fields of type calendar or type text . This acts similarly to the PHP date() function in that it allows you to print out fields for day, month, year, hour, minute, and second depending on what you put in your calType. The following letters are recognized as of now (case-insensitive):

y	Year
m	Month
d	Day
h	Hour
i	Minute
s	Second

Any character which isn't in the above list will be printed out as-is. So for example, "Y-M-D" will print out fields for year, month, and day, with a dash between each one.

If your field is of type text, you can use the full PHP date() syntax. See the PHP reference for more information. Note that PHP date() is case-sensitive, so the use may be slightly different than Sparse's current syntax (for example, the full year is a capital Y, and full hour is a capital H).

isSearch

Required: No
Templates: display, edit
Values: true | false

If this is set, the field will be a search field. It will still have all the functionality of a regular sqlfield (creating a widget automatically, allowing you to specify types, styles etc.) but it will be used for searching rather than for entering or displaying data. You must use it together with an sqlaction of type search. See Searching for more details.

value

Required: No
Templates: add (any field), edit (radioSingle and checkSingle fields)
Values: Any correct value

By default, the initial value of a widget in an add template will be blank. You can alter this by setting the value attribute. This will set the text value (in textareas/textfields/passwords) or the selected item (in all the rest). Note that this only takes regular text; if you need to use a more complex expression, use the displayValue attribute instead. You can also use this attribute to set the value attribute of the tag for a radioSingle or checkSingle field; again, if you need a more complex expression, use returnValue.

labelClass

Required: No
Templates: All
Values: A valid CSS class

You can use this to set the class attribute of the label next to the radio or check buttons.

labelStyle

Required: No
Templates: All
Values: A valid CSS style

You can use this to set the style attribute of the label next to the radio or check buttons.

Advanced Attributes

constraints

Required: No
Templates: edit, add
Values: PHP code which evaluates to some numeric or string value

This attribute allows you to only accept incoming values that conform to specific cases. For example, you can require that an entered ID is a valid customer ID; you can call any function previously defined, which will then be evaluated. The code should evaluate to true for the value to be accepted. If it evaluates to false, a general error will be printed; if it evaluates to anything else, that will be taken as the error. You can use $value to refer to the entered value, or $row to refer to the entire entered row.

Example 1:

<sqlfield name="amount"
constraints="is_numeric($value) && $value >= 5"
/>

This code will evaluate to true (the value is accepted) or false (a general error message will be generated).

Example 2:

<?php
include('Sparse.php');
function is_valid_customer_id($val)
{
//search your database and check if an ID is valid.
//return true, "Invalid ID!", or "Valid ID but not a customer!"
}
SparseThisPage($username, $password);
?>

<sqlfield name="id" constraints="is_valid_customer_id($value)" />

numeric

Required: No
Templates: edit, add
Values:

true | false

If set, the value entered by the user must be numeric. This is the same as setting constraints to is_numeric($value).

max

Required: No
Templates: edit, add
Values: Any number or string

If set, the value entered by the user must be less than or equal to the given value. This is the same as setting constraints to $value <= max.

min

Required: No
Templates: edit, add
Values: Any number or string

If set, the value entered by the user must be greater than or equal to the given value. This is the same as setting constraints to $value >=min.

required

Required: No (how ironic)
Templates: edit, add
Values: true/false

If this is set to true, an error will occur if the user tries to submit the data without filling in a value. This is the same as setting constraints to trim($value) != ''

displayValue

Required: No
Templates: display, edit
Values: PHP code evaluating to a string or integer

This allows you to format the value of the SQL data for display. Like constraints, you can use $value to indicate the SQL data value and $row to indicate the rest of the row. For example, you can format an integer number of cents to look like a dollar amount:

<sqlfield name="amount" displayValue="'$' .
number_format($value / 100, 2)" />

returnValue

Required: No
Templates: edit, add

This allows you to edit the incoming SQL value before inserting or updating the field. Like constraints, you can use $value to refer to the incoming value and $row to refer to the incoming row.. For example, you can remove all dashes and spaces from the value before putting it in a date or integer field:

<sqlfield name="startDate"
returnValue="str_replace(' ', '', str_replace('-', '', $value))"
/>

enumset

Required: No
Templates: All
Values: A valid enumset id

By setting this attribute, you're telling Sparse to grab the enumset referenced by the ID, and replace the enum keys with their values when displaying the field, while keeping the original value as the one sent to the server. In particular:

"Text" fields will simply display the enum value. (I.e. if the value retrieved in the SQL data is "key", value is what will actually be printed.)
"Select" and "SelectMultiple" fields will use the key as the "value" attribute of their OPTIONs, and the value as the text between OPTION tags.
Radio and checkboxes will use the key as its "value" and display the value as text next to the boxes.
Calendar, Textfield, Password, and Textareas will ignore this attribute.

Other attributes:

Templates: All
You can add any other attributes that would normally be associated with the input element you're dealing with. For example:

<sqlfield type="textfield" size="15"
class="myTextFieldClass" />




<sqlfield type="textarea" cols="10" rows="10" />

The style and class attributes for a text widget, if given, will be put into a <span> tag enclosing the value.

sqlfield Tag