SL1 includes a class for executing SQL queries against SL1's database instance. SL1 automatically instantiates $db as an instance of this class for use in gluecode. This chapter describes the methods that are available for this object. For all the examples in this chapter, the following table named cities is used:
| state | city | area_code |
|---|---|---|
| VA | Reston | 703 |
| VA | Richmond | 804 |
| DC | Washington | 202 |
| MD | Baltimore | 410 |
autofetch_all
Description
array autofetch_all(string sql)
Returns the results of the SQL query as a linear array of associative arrays. Each associative array contains data for one row returned by the SQL query, with each field name as a key.
Parameters
- sql. SQL query to run against the database.
Return Values for SQL errors and zero returned rows
If there is an error in the SQL statement, returns FALSE.
If the database query returns zero rows, returns an empty array.
Example
Code:
$sql = "SELECT * FROM cities";
$results = $db->autofetch_all($sql);
print_r($results);
Output:
Array
(
[0] => Array
(
[state] => VA
[city] => Reston
[area_code] => 703
)
[1] => Array
(
[state] => VA
[city] => Richmond
[area_code] => 804
)
[2] => Array
(
[state] => DC
[city] => Washington
[area_code] => 202
)
[3] => Array
(
[state] => MD
[city] => Baltimore
[area_code] => 410
)
)
autofetch_all_assoc
Description
array autofetch_all_assoc(string sql [, array columns, bool collapse, bool multival])
Without the optional parameters, returns the results of the SQL query as an associative array. Each key value in the returned array maps to the value of the first column returned from the SQL statement. Each value in the associative array is an associative array containing the other values from that row, using each field name as a key.
When the columns parameter is specified, returns the results of the SQL query as an associative array. Each key value in the returned array maps to value of the first column value specified in columns. Each value in the associative array for the first column is an associative array with each key value as the value from the second column value specified in columns. Each value in the associative array for the second column is an associative array, using the names of the remaining columns returned by the SQL query as the keys. More than two columns can be specified in columns, which will apply the behavior recursively, with each value in the columns array as another associative array.
Parameters
- sql. SQL query to run against the database.
- columns. see the Description.
- collapse. If TRUE: If after columns is processed only one returned column remains, the values in the associative array for the last value in columns will be the value in the remaining column. This is instead of the values in the associative array for the last value in columns being an associative array containing one key for the remaining column.
- multival. Prevents function from overwriting data rows if there are duplicate array keys. If TRUE, after the columns array is processed to create nested associative arrays, the value of the innermost array will be a linear array with each value corresponding to one returned row. The value of the linear array is an associative array that uses the remaining column names as keys. This is instead of the innermost associative array having the value of an associative array that uses the remaining column names as keys.
Return Values for SQL errors and zero returned rows
If there is an error in the SQL statement, returns FALSE.
If the database query returns zero rows, returns an empty array.
Example 1
Code:
$sql = "SELECT * FROM cities";
$results = $db->autofetch_all_assoc($sql);
print_r($results);
Output:
Array
(
[VA] => Array
(
[city] => Richmond
[area_code] => 804
)
[DC] => Array
(
[city] => Washington
[area_code] => 202
)
[MD] => Array
(
[city] => Baltimore
[area_code] => 410
)
)
In example 1, the first and second rows returned by the SQL query have the same index in the returned array. The first row has not been returned by the function as it has been overwritten by the second row. Example 4 shows the same query with the multival parameter set to TRUE, which prevents this data loss.
Example 2
Code:
$sql = "SELECT * FROM cities";
$columns = array(0 => state, 1 => city);
$results = $db->autofetch_all_assoc($sql, $columns);
print_r($results);
Output:
Array
(
[VA] => Array
(
[Reston] => Array
(
[area_code] => 703
)
[Richmond] => Array
(
[area_code] => 804
)
)
[DC] => Array
(
[Washington] => Array
(
[area_code] => 202
)
)
[MD] => Array
(
[Baltimore] => Array
(
[area_code] => 410
)
)
)
Example 3
Code:
$sql = "SELECT * FROM cities";
$columns = array(0 => state, 1 => city);
$results = $db->autofetch_all_assoc($sql, $columns, TRUE);
print_r($results);
Output:
Array
(
[VA] => Array
(
[Reston] => 703
[Richmond] => 804
)
[DC] => Array
(
[Washington] => 202
)
[MD] => Array
(
[Baltimore] => 401
)
)
Example 4
Code:
$sql = "SELECT * FROM cities";
$results = db->autofetch_array_assoc($sql, NULL, FALSE, TRUE);
print_r($results);
Output:
Array
(
[VA] => Array
(
[0] => Array
(
[city] => Reston
[area_code] => 703
)
[1] => Array
(
[city] => Richmond
[area_code] => 804
)
)
[DC] => Array
(
[0] => Array
(
[city] => Washington
[area_code] => 202
)
)
[MD] => Array
(
[0] => Array
(
[city] => Baltimore
[area_code] => 410
)
)
)
autofetch_column
Description
array autofetch_column(string sql [, bool associative])
If the associative parameter is not passed or equates to FALSE, returns a linear array of values from the first column returned by the SQL statement.
If the associative parameter equates to TRUE, returns an associative array of values from the second column returned by the SQL statement. The array uses each value from the first column returned by the SQL statement as an index.
Parameters
- sql. SQL query to run against the database.
- associative. If FALSE or undefined, returns a linear array of values from the first column returned by the SQL statement. If TRUE, returns an associative array that uses the values from the first column returned by the SQL statement as keys and values from the second column returned by the SQL statement as values.
Return Values for SQL errors and zero returned rows
If there is an error in the SQL statement, returns FALSE.
If the database query returns zero rows, returns an empty array.
Example 1
Code:
$sql = "SELECT * FROM cities";
$results = $db->autofetch_column($sql)
print_r($results);
Output:
Array
(
[0] => VA
[1] => VA
[2] => DC
[3] => MD
)
Example 2
Code:
$sql = "SELECT * FROM cities";
$results = $db->autofetch_column($sql, TRUE)
print_r($results);
Output:
Array
(
[VA] => Richmond
[DC] => Washington
[MD] => Baltimore
)
In example 2, the first and second rows returned by the SQL query have the same index in the returned array. The first row has not been returned by the function as it has been overwritten by the second row.
autofetch_column_multival
Description
array autofetch_column_multival(string sql)
Operates on the first two columns returned by the SQL query. Returns an associative array that uses each unique value from the first column as indexes. Each value in the array is a linear array of values from the second column that is associated with that key value.
Parameters
- sql. SQL query to run against the database.
Return Values for SQL errors and zero returned rows
If there is an error in the SQL statement, returns FALSE.
If the database query returns zero rows, returns an empty array.
Example
Code:
$sql = "SELECT state, city, area_code FROM cities";
$results = $db->autofetch_column_multival($sql);
print_r($results);
Output:
Array
(
[VA] => Array
(
[0] => Reston
[1] => Richmond
)
[DC] => Array
(
[0] => Washington
)
[MD] => Array
(
[0] => Baltimore
)
)
autofetch_columns
Description
array autofetch_columns(string sql, array fields)
Returns an associative array that uses the fields in the fields array as the keys. Each value in the array is a linear array of values returned for that field by the SQL statement.
Parameters
- sql. SQL query to run against the database.
- fields. Array of fields to use as keys in the returned associative array.
Return Values for SQL errors and zero returned rows
If there is an error in the SQL statement, returns FALSE.
If the database query returns zero rows, returns an empty array.
Example
Code:
$sql = "SELECT * FROM cities";
$fields = array(0 => "state", 1 => "city");
$results = $db->autofetch_columns($sql, $fields);
print_r($results);
Output:
Array
(
[state] => Array
(
[0] => VA
[1] => VA
[2] => DC
[3] => MD
)
[city] => Array
(
[0] => Reston
[1] => Richmond
[2] => Washington
[3] => Baltimore
)
)
autofetch_row
Description
array autofetch_row(string sql)
Returns the first row returned by the SQL statement as an associative array. The name of each field returned is a key value in the array.
Parameters
- sql. SQL query to run against the database.
Return Values for SQL errors and zero returned rows
If there is an error in the SQL statement, returns FALSE.
If the database query returns zero rows, returns FALSE.
Example
Code:
$sql = "SELECT * FROM cities";
$results = $db->autofetch_row($sql);
print_r($results);
Output:
Array
(
[state] => VA
[city] => Reston
[area_code] => 703
)
autofetch_value
Description
mixed autofetch_value(string sql)
Returns the value in the first field of the first row returned by the SQL statement.
Parameters
- sql. SQL query to run against the database.
Return Values for SQL errors and zero returned rows
If there is an error in the SQL statement, returns FALSE.
If the database query returns zero rows, returns FALSE.
Example
Code:
$sql = "SELECT * FROM cities";
$results = $db->autofetch_value($sql);
echo $results;
Output:
VA
expunge_call
Description
NULL expunge_call()
Removes the additional result sets generated when calling a stored procedure. If you call a stored procedure using the $db object, you must call this method before executing additional queries.
Parameters
The expunge_call() method does not take any parameters.
Return Values for SQL errors and zero returned rows
The expunge_call() method always returns NULL.