Templates and Queries

An SQL query can safely be said to be made of five parts:

The type of query - "SELECT...FROM", "UPDATE...SET", "INSERT INTO...VALUES", etc.
The fields to select.
The tables to select from - e.g. the FROM clause in SELECT queries.
The constraints on the selection - i.e. the WHERE, HAVING, GROUP BY etc. clauses.
The LIMIT clause.

Sparse reconstructs the SQL query in the following ways

Type of query: Depends on the sqltemplate type attribute, and the action of the user. The whole point of using Sparse is to be able to recreate the same query using different types with only one definition, e.g. being able to both display and edit the data.
Fields to select: These are taken from the sqlfield tags, and in addition Sparse will also select all primary keys, even if they're not displayed. They're needed to identify the rows which are edited or deleted.
Tables to select: These are taken from the sqltemplate tables attribute.
Constraints: These are taken from the sqltemplate constraints attribute.
Limit Clause: Again, taken from the sqltemplate limit attribute.

Let's say you want your Sparse template to mirror the following SQL query:

SELECT e.id, e.name FROM employees AS e WHERE e.salary
> 10000 AND e.country='Canada' ORDER BY e.name DESC LIMIT 10;

Here's what your template would look like:

<?php
include('Sparse.php');
SparseThisPage('username', 'password');
?>
<html>
<h2 align="center">Employee Database</h2>
<table>
<tr>
<th>Employee Name</th>
<th>Employee ID</th>
</tr>
    <sqltemplate type="display" database="myDB" tables="employees AS e" constraints="WHERE e.salary > 10000 AND e.country='Canada' ORDER BY e.name DESC" limit="10">
    <sqlrow>
    <tr>
    <td><sqlfield name="e.id" /></td>
    <td><sqlfield name="e.name" /></td>
    </tr>
    </sqlrow>
</table>
<sqlaction type="prev" /><sqlaction type="next" />
    </sqltemplate>
</html>

In this case, the template type has been changed to display to simplify things (and the sqlaction edit tag has therefore been taken out). As you can see, Sparse can do pretty much anything basic SQL can do!

Note the sqlaction buttons, prev and next. These allow the user to cycle through the displayed results. The limit defined here is 10, but Sparse can display the first 10, the second 10, etc. You can also use sqlnavigation to print a full page-by-page list of links. Note that you can navigate the data when editing it as well as displaying it!

One last thing: you can use PHP variables inside your constraints attribute. For example, all form data (both GET and POST) is put in the associative array $_REQUEST. So if your URL looks like this:
http://www.mydomain.com/mypage.phtml?name=John
You can specifically display only John's data by using the following constraints:

constraints='WHERE name="{$_REQUEST["name"]}"'

Note that all the inner quotes are the opposite of the outer quotes; you don't have to worry about quotes cancelling each other out. (We're using heredoc syntax here.) Also, don't forget the {curly brackets} around the name of the variable.

Note that you may want to consider taking advantage of Sparse's search capabilities rather than using PHP variables in the method described above.

Previous: Templates - Overview | Templates and Queries | Next: Template Types