Sparse - Working With Data

Welcome to advanced use of Sparse! Sure, you can use Sparse to display and manage your data, but what if you want a little more? There are three ways to alter the incoming or displayed data, and all of them are accomplished via attributes of the sqlfield tag.

All three ways involve using PHP code to deal with the incoming data value. You can reference that value with $value. You can also, if you like, reference the entire incoming row via $row. (This is fetched using mysql_fetch_array, so both numeric and associative keys are set; e.g. $row[0] and $row['employeeID'] are both valid.)

Changing the Displayed Data

The displayValue attribute allows you to alter what shows up on the template. For example, let's say you've got a field in your employees table called "salary" which for some obscure reason is stored as an integer. You want to format it to a nice dollar amount. No problem! Here's what you do:

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

Et voilà. You can use any PHP code, even a previously declared function (i.e. declared before you call SparseThisPage or SparsePage). Sparse will use eval() to output the computed value.

If you want to display some combination of several fields in a separate field, it's probably best to just use a plain PHP expression, like this:

<?php print $row['lastName'] . ', ' . $row['firstName']; ?>

You can use $row anywhere inside the sqlrow tag.

Changing the Submitted Data

The flip side of this is what to do when the data doesn't match the internal SQL value you want it to. For example, say you want to put the nice dollar value back as an integer. You can use returnValue to do that. Here's how it'd look:

<sqlfield name="salary"
returnValue="(int)(substr($value, 1) * 100)" />

...which will remove the $ and multiply by 100. Whee!

Constraining the Data

The most interesting way to interact with the data is to force the user to enter it in a specific format. There are several built-in attributes which cover the most common constraints: required indicates the value can't be blank; numeric ensures the data must be a number; min and max require it to be greater or lesser than specific values. But you can indicate any constraints you like. The easiest way to do this is to create your own PHP function. The reason is because doing this will allow you to "personalize" your error messages (see Errors).

Let's say you want to ensure the entered value is a valid employee name and is not more than ten characters long. You might define a function like this:

function employeeExists($name)
{
if (strlen($name) > 10) return 'Name is too long!';
mysql_query('SELECT name FROM employees WHERE name=' . $name);
if (mysql_num_rows) < 1) return 'Employee not found!';
return true;
}

Your tag would then be:

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

If the constraints tag evaluates to true, the value will be accepted. If it evaluates to false, a generic error message will be displayed. If it evaluates to anything else, though, it will print out that message. So it's a great way to define constraints without interleaving the code with the display!

Previous: Sorting | Working With Data | Next: Errors