Sparse - Advanced Queries

All the queries we've seen so far have been simple ones, using only a single table. But often you'll want to integrate data from multiple tables, or to use functions etc. Luckily, Sparse allows you to do so!

Multiple Tables, Aliases, and Functions

Let's say you want to use two tables in your template, "employees" and "managers". Recall from the Templates and Queries page that Sparse reconstructs the full query from the sqlfield tags and the attributes of the sqltemplate tag. Adding multiple tables, alieses, or functions is actually pretty straightforward:

<?php
include('Sparse.php');
SparseThisPage('username', 'password');
?>
<html>
<sqltemplate type="display" database="myDB" tables="employees AS e, managers AS m" limit="10" constraints="WHERE e.managerID=m.id AND mname != 'John Smith'">
<table border="1">
<sqlrow>
<tr>
<td><sqlfield name="employeeID" /></td>
<td><sqlfield name="e.name" /></td>
<td><sqlfield name="m.name AS mname" /></td>
<td><sqlfield name="concat(e.address, e.city)" /></td>
</tr>
</sqlrow>
<tr><td colspan="2" align="center">
<sqlaction type="prev" /><sqlaction type="next" />
</td></tr>
</table>
</sqltemplate>
</html>

This is basically equivalent to the following query:

SELECT employeeID, e.name, m.name AS mname,
concat(e.address,
e.city) FROM employees AS e, managers AS m WHERE e.managerID=m.id AND
mname != 'John Smith'

(As mentioned earlier, the primary keys of all tables are also selected, and the limit clause is changed slightly.) So if you know what your SQL query would look like, you can easily translate that into Sparse tags.

A note on updating and deleting from multiple tables. Take the above template. If we turned it into an edit template and updated a row, both the "employees" and "managers" tables would be updated. Sparse does this by taking all the primary keys from the query, and updating each table individually based on its own primary keys and fields that appear in the template.

And a note on functions: You can't edit them (obviously), so Sparse will force any sqlfield tag which is a function to have type text - i.e. plain text, not editable.

Be very careful when deleting from multiple tables. By default, every table which appears in the query will have its row deleted. You can avoid this behavior by setting the deleteTables attribute in sqltemplate.

Be careful when returning fields with the same name. Sparse often assumes that the returned name of each field is unique. If you have two fields in different tables with the same name, it's best to alias them (e.g. declare your sqlfield name as "e.name AS ename" and "m.name AS mname") to differentiate them. Similarly, you should alias tables rather than using a database identifier, e.g. "myDatabase.table1 AS myTable1" in the tables attribute of sqltemplate.

The `fields` Attribute

The above method for declaring fields is nice in that you only have to write them once. However, sometimes you'll find it easier or less confusing to put the full declaration in the sqltemplate tag, and simply reference the aliased name in the body of the template. You can do this by using the fields attribute. Doing this will cause the above template to look like this:

<?php
include('Sparse.php');
SparseThisPage('username', 'password');
?>
<html>
<sqltemplate type="display"
database="myDB"
fields="employeeID, e.name, m.name AS mname, concat(e.address, e.city) AS address"
tables="employees AS e, managers AS m"
limit="10"
constraints="WHERE e.managerID=m.id AND mname != 'John Smith'">
<table border="1">
<sqlrow>
<tr>
<td><sqlfield name="employeeID" /></td>
<td><sqlfield name="name" /></td>
<td><sqlfield name="mname" /></td>
<td><sqlfield name="address" /></td>
</tr>
</sqlrow>
<tr><td colspan="2" align="center">
<sqlaction type="prev" /><sqlaction type="next" />
</td></tr>
</table>
</sqltemplate>
</html>

Note that if you hadn't aliased your CONCAT function, you'd still be able to reference it inside an sqlfield tag by giving it the name concat(e.address, e.city) - i.e. repeating the exact same function you gave it earlier. Still, it's usually nicer to alias the name.

Unions and Other Queries

There are always limits to everything, and Sparse does a pretty good job of things, but there are some queries it just can't handle - for example, UNIONs or subqueries. While Sparse can't let you edit or add to such a query, you can still display it by using the query attribute of sqltemplate. This will force the template type to be display.

Previous: Template Types | Advanced Queries | Next: Quick Templates

Sparse - Advanced Queries

Multiple Tables, Aliases, and Functions

The fields Attribute

Unions and Other Queries

The `fields` Attribute