How to retrieve, sort, and display information from a database using the Stanford Web Application Toolkit

From Web Services Wiki

Jump to: navigation, search

Contents

Problem

You want to get information from a MySQL database and optionally display it in an HTML table.

Solution

Use the StanfordDBQuery class to perform simple database queries and retrieve the result as an associative array.

Connect to the database

Read How to configure and access MySQL using the Stanford Web Application Toolkit for more information about accessing MySQL through the toolkit. Please note that only groups, departments, and services have access to MySQL at this time. Individual user accounts are not supported.

Basic usage

After connecting to the database, create a new StanfordDBQuery object. Call display_as_html_table to output the information from the database table in an HTML table.

// Include StanfordDBQuery
include_once("stanford.dbquery.php");
 
// Connect to the database using your method of choice (Read How to configure and access MySQL using the Stanford Web Application Toolkit)
$db = new StanfordDatabase("my_username", "my_password", "my_database");
 
// Create a new StanfordDBQuery object and specify which table to perform the query on
// The database connection handler must be of type MySQLi (StanfordDatabase is an extension of MySQLi)
$data = new StanfordDBQuery($db, "table_name");
 
// Output the data from the table
$data->display_as_html_table();

If you would like to simply run a query and get information from the database without outputting a table, use the retrieve method.

// Get the data from the table
$result = $data->retrieve();
 
// Read the data from the result
foreach($result as $row) {
  $id = $row['id'];
  $subject = $row['subject'];
  // etc..
}

Filtering results

Using StanfordDBQuery you may be selective about what data to return from the database table. It is possible to select only certain fields in a certain order, sort results by one or more parameters, and choose the number of rows to return.

Set list of fields to retrieve

Call set_fields_to_retrieve to specify which columns to return.

// Use set_fields_to_retrieve to select which fields to retrieve from the table
$data->set_fields_to_retrieve("last_name", "first_name", "email_address");

Set constraints

Use set_constraints to set specific constraints on the data that is to be returned. In SQL terms, this is the WHERE clause.

Important: All user input must be escaped before being sent to set_constraints. Read How to securely store data into MySQL for more information.

// Get user input from form
$search = $_GET['last_name_search'];
 
// IMPORTANT: Must escape all user input that goes into the set_constraints function, otherwise it is vulnerable to SQL injection
// In this example, $db is our database connection object that we configured earlier (either MySQLi or StanfordDatabase)
$db = $data->get_database_connection();
$search = $db->escape_string($search);
 
// Return only results whose last names match a user's search term
$data->set_constraints("last_name LIKE '%$search%'");

Set field(s) to order by

Call sort_by to choose the fields by which to order. Calling this function multiple times results in a multidimensional sort.

// Order first by last name
$data->sort_by("last_name");
 
// And then by first name
$data->sort_by("first_name");

To do a reverse sort, specify the direction in sort_by.

// Order by ID, highest to lowest -- default is ASCENDING
$data->sort_by("id", StanfordDBQuery::DESCENDING);

Set limit and offset

Use set_limit and set_page to specify which slice of the resultset to return.

// Get the first 10 results
$data->set_limit(10);
$data->set_page(1);  // set_page automatically computes offset
                     // default page is 1, so for the first 10 results, simply calling set_limit(10) is sufficient

While using set_limit and set_page is usually sufficient for choosing which data to return, you may manually set the offset using set_offset.

// Manually set the offset when returning only part of the dataset
$data->set_offset(15);  // retrieve all results after the 15th row

Retrieve the data

The retrieve function returns all of the data as an associative array. Call this function after setting up all required filtering and sorting parameters. Alternatively, if you'd just like to display a table without processing the data, call display_as_html_table (see below).

// Get the data from the MySQL table
$result = $data->retrieve();

Getting information about the resultset

When using a limit on the number of results to retrieve, it is often useful to get the total number of results available. Use the methods get_num_results and get_num_results_available to find out how much data was retrieved and how much there is in total. Call these functions after retrieving data or displaying a table.

// Get the number of results retrieved
$num = $data->get_num_results();
 
// Get the total number of results available
$num = $data->get_num_results_available();

Displaying the Data

Custom Headings

You may customize the table outputted by display_as_html_table in several ways, including changing the order of the fields, the table headings or labels, and the CSS class.

// Display the date of birth first, then the name
$data->set_fields_to_display("dob", "first_name", "last_name");
 
// Set the table heading for the database field "dob" to "Date of Birth"
$data->set_heading("dob", "Date of Birth");
 
// Set the other headings
$data->set_heading("first_name", "First Name");
$data->set_heading("last_name", "Last Name");
 
// Optionally, set a custom CSS class for the <table> tag
$data->set_css_class("my-table");
 
// Output the table
$data->display_as_html_table();

Alternatively, use the shortcut method set_headings to create labels for your table, which in some cases may simplify your code compared to using set_heading on each field.

// Display the date of birth first, then the name
$data->set_fields_to_display("dob", "first_name", "last_name");
 
// Set all of the headings at once (in display order -- must correspond to the data being displayed)
$data->set_headings("Date of Birth", "First Name", "Last Name");
 
// Output the table
$data->display_as_html_table();

If you don't want to display any headings at all, simply set display_headings to false.

// Do not display table headings when calling display_as_html_table
$data->set_display_headings(false);

Date Formats

To format date fields in the table, call the function set_date_format, as shown below.

// Change date format for "Date of Birth" (e.g. from "December 3, 1984" to "Dec 03, 1984")
$data->set_date_format("Date of Birth", "M d, Y");

For a list of all available date formats, visit PHP.net: date function.

Please note that date formats apply only to displaying data in an HTML table. When getting data using the retrieve function, format conversion is left to the developer.

Display Functions

In many cases, data is stored differently than it is displayed. For example, a standard phone number may be stored as a 10 character field with no formatting, but you'd prefer to display it with parentheses, dashes, and spaces to make it more readable. Use the method add_display_function to define a custom formatting function to be called before a field is displayed. See the sample code below for an example of calling a function with a single parameter.

// This example assumes that values in the "amount" column are stored as floats (e.g. 1234.56)
 
// Define a custom function to return values in the format $1,234.56
function formatMoney($number) {
  return "$" . number_format($number, 2);
}
 
// Add custom display function to the "amount" field
// $data is a StanfordData object
$data->add_display_function("amount", "formatMoney");
 
// Display the table
// The amount column will be displayed as defined in the formatMoney function
$data->display_as_html_table();

To define a display function with more than one parameter, set the third parameter of add_display_function to an array containing the arguments to the method. Use the placeholder %FIELD% to mark which parameter is the value of the field.

// Configure StanfordData to call the function number_format($amount, 2) each time a cell in the "amount"
// column is outputted by display_as_html_table
$data->add_display_function("amount", "number_format", array("%FIELD%", 2));

You may define multiple display functions for one field. Each function will be called successively in the order that it is defined, and the return value from each function is passed as the field value to the method that follows.

Allowing HTML

By default, HTML code contained within cells of the table is escaped (and thus not parsed by the browser) for security purposes. If you define a custom display function (see above) to make a field bold by surrounding it with HTML tags, you must also enable HTML for that column. To remove the HTML output restriction from a column, use the function set_allow_html_in_column($field_name), as shown below.

// Do not escape HTML in the column for the 'email' field
$data->set_allow_html_in_column("email", true);

To allow HTML output in the entire table, use the function set_allow_html_in_table.

// Do not escape any HTML output
$data->set_allow_html_in_table(true);

You may use a combination of the HTML output settings shown above. The more specific setting (per-column) takes precedence. Setting the boolean parameter in each of the functions to false means that HTML code will be escaped.

If you need to disable the automatic HTML restriction, we strongly suggest setting up a custom display function to escape user input wherever possible. Visit PHP.net: htmlspecialchars for one way of escaping HTML output.

Discussion

Can I change the style of the table outputted by 'display_as_html_table?'

Yes, either modify the default style for a table or create a custom class.

// In your PHP code, before outputting the table, set the desired name of the CSS class for the <table> tag
$data->set_css_class("php-table");
 
// Output the table
$data->display_as_html_table();

Some sample style settings are shown below. Rows containing table headings automatically belong to the 'headings' class (use set_headings_class to modify). Every other data row's class is set to 'alternate' by default (use set_alternate_row_class to modify).

/* Table */
.php-table {
  background-color: #900;
  border: 3px solid #000;
  border-collapse: collapse;
}
 
/* Column headings */
.php-table tr.headings {
  color: #FFF;
}
 
/* Data cells */
.php-table td {
  color: #E7D19A;
  padding: .5em;
}
 
/* Alternate rows */
.php-table tr.alternate {
  background-color: #800;
}

How can I retrieve the code for the HTML table without displaying it?

Set the only parameter to display_as_html_table to false. The HTML code is returned and no output is displayed.

// Get the code, do not display the table
$html_code = $data->display_as_html_table(false);
 
// Get the number of results
$num = $data->get_num_results();
$total = $data->get_num_results_available();
 
// Output a heading
echo "<p>Displaying $num of $total results</p>";
 
// Display the table
echo $html_code;
Personal tools