SQLITE_ESCAPE_STRING(3)SQLITE_ESCAPE_STRING(3)sqlite_escape_string - Escapes a string for use as a query parameterSYNOPSIS
string sqlite_escape_string (string $item)
DESCRIPTION sqlite_escape_string(3) will correctly quote the string specified by $item for use in an SQLite SQL statement. This includes doubling up
single-quote characters ( ') and checking for binary-unsafe characters in the query string.
Although the encoding makes it safe to insert the data, it will render simple text comparisons and LIKE clauses in your queries unusable
for the columns that contain the binary data. In practice, this shouldn't be a problem, as your schema should be such that you don't use
such things on binary columns (in fact, it might be better to store binary data using other means, such as in files).
PARAMETERS
o $item
- The string being quoted. If the $item contains a NUL character, or if it begins with a character whose ordinal value is 0x01,
PHP will apply a binary encoding scheme so that you can safely store and retrieve binary data.
RETURN VALUES
Returns an escaped string for use in an SQLite SQL statement.
NOTES
Note
Do not use this function to encode the return values from UDF's created using sqlite_create_function(3) or sqlite_create_aggre-
gate(3) - use sqlite_udf_encode_binary(3) instead.
Warning
addslashes(3) should NOT be used to quote your strings for SQLite queries; it will lead to strange results when retrieving your
data.
SEE ALSO sqlite_udf_encode_binary(3).
PHP Documentation Group SQLITE_ESCAPE_STRING(3)
Check Out this Related Man Page
SQLITE_CREATE_AGGREGATE(3)SQLITE_CREATE_AGGREGATE(3)sqlite_create_aggregate - Register an aggregating UDF for use in SQL statementsSYNOPSIS
void sqlite_create_aggregate (resource $dbhandle, string $function_name, callable $step_func, callable $finalize_func, [int $num_args
= -1])
DESCRIPTION
Object oriented style (method): void SQLiteDatabase::createAggregate (string $function_name, callable $step_func, callable $final-
ize_func, [int $num_args = -1])
sqlite_create_aggregate(3) is similar to sqlite_create_function(3) except that it registers functions that can be used to calculate a
result aggregated across all the rows of a query.
The key difference between this function and sqlite_create_function(3) is that two functions are required to manage the aggregate;
$step_func is called for each row of the result set. Your PHP function should accumulate the result and store it into the aggregation con-
text. Once all the rows have been processed, $finalize_func will be called and it should then take the data from the aggregation context
and return the result. Callback functions should return a type understood by SQLite (i.e. scalar type).
PARAMETERS
o $dbhandle
- The SQLite Database resource; returned from sqlite_open(3) when used procedurally. This parameter is not required when using the
object-oriented method.
o $function_name
- The name of the function used in SQL statements.
o $step_func
- Callback function called for each row of the result set. Function parameters are &$context, $value, ....
o $finalize_func
- Callback function to aggregate the "stepped" data from each row. Function parameter is &$context and the function should return
the final result of aggregation.
o $num_args
- Hint to the SQLite parser if the callback function accepts a predetermined number of arguments.
RETURN VALUES
No value is returned.
EXAMPLES
Example #1
max_length aggregation function example
<?php
$data = array(
'one',
'two',
'three',
'four',
'five',
'six',
'seven',
'eight',
'nine',
'ten',
);
$dbhandle = sqlite_open(':memory:');
sqlite_query($dbhandle, "CREATE TABLE strings(a)");
foreach ($data as $str) {
$str = sqlite_escape_string($str);
sqlite_query($dbhandle, "INSERT INTO strings VALUES ('$str')");
}
function max_len_step(&$context, $string)
{
if (strlen($string) > $context) {
$context = strlen($string);
}
}
function max_len_finalize(&$context)
{
return $context;
}
sqlite_create_aggregate($dbhandle, 'max_len', 'max_len_step', 'max_len_finalize');
var_dump(sqlite_array_query($dbhandle, 'SELECT max_len(a) from strings'));
?>
In this example, we are creating an aggregating function that will calculate the length of the longest string in one of the columns of the
table. For each row, the max_len_step function is called and passed a $context parameter. The context parameter is just like any other PHP
variable and be set to hold an array or even an object value. In this example, we are simply using it to hold the maximum length we have
seen so far; if the $string has a length longer than the current maximum, we update the context to hold this new maximum length.
After all of the rows have been processed, SQLite calls the max_len_finalize function to determine the aggregate result. Here, we could
perform some kind of calculation based on the data found in the $context. In our simple example though, we have been calculating the result
as the query progressed, so we simply need to return the context value.
Note
The example above will not work correctly if the column contains binary data. Take a look at the manual page for
sqlite_udf_decode_binary(3) for an explanation of why this is so, and an example of how to make it respect the binary encoding.
Tip
It is NOT recommended for you to store a copy of the values in the context and then process them at the end, as you would cause
SQLite to use a lot of memory to process the query - just think of how much memory you would need if a million rows were stored in
memory, each containing a string 32 bytes in length.
Tip
You can use sqlite_create_function(3) and sqlite_create_aggregate(3) to override SQLite native SQL functions.
SEE ALSO sqlite_create_function(3), sqlite_udf_encode_binary(3), sqlite_udf_decode_binary(3).
PHP Documentation Group SQLITE_CREATE_AGGREGATE(3)