<?php
class Test extends SQLite3
{
public function __construct($file)
{
parent::__construct($file);
$this->createAggregate('groupConcat', [$this, 'concatStep'], [$this, 'concatFinal']);
}
public function concatStep(&$context, $rowId, $string, $delimiter)
{
if (!isset($context)) {
$context = [
'delimiter' => $delimiter,
'data' => []
];
}
$context['data'][] = $string;
return $context;
}
public function concatFinal(&$context)
{
return implode($context['delimiter'], $context['data']);
}
}
$SQLite = new Test('/tmp/test.sqlite');
$SQLite->exec("create table `test` (`id` TEXT, `color` TEXT, `size` TEXT)");
$SQLite->exec("insert into `test` (`id`, `color`, `size`) values ('1', 'red', 'M')");
$SQLite->exec("insert into `test` (`id`, `color`, `size`) values ('1', 'green', 'M')");
$SQLite->exec("insert into `test` (`id`, `color`, `size`) values ('1', 'blue', 'S')");
$Result = $SQLite->query("select `size`, groupConcat(`color`, ';') as `color` from `test` group by `size`");
while ($row = $Result->fetchArray(SQLITE3_ASSOC)) {
print_r($row);
}
/*
Array
(
[size] => M
[color] => red;green
)
Array
(
[size] => S
[color] => blue
)
*/
SQLite3::createAggregate
(PHP 5 >= 5.3.0, PHP 7)
SQLite3::createAggregate — Registers a PHP function for use as an SQL aggregate function
설명
public bool SQLite3::createAggregate
( string
$name
, mixed $step_callback
, mixed $final_callback
[, int $argument_count = -1
] )Registers a PHP function or user-defined function for use as an SQL aggregate function for use within SQL statements.
인수
-
name -
Name of the SQL aggregate to be created or redefined.
-
step_callback -
The name of a PHP function or user-defined function to apply as a callback for every item in the aggregate.
-
final_callback -
The name of a PHP function or user-defined function to apply as a callback at the end of the aggregate data.
-
argument_count -
The number of arguments that the SQL aggregate takes. If this parameter is negative, then the SQL aggregate may take any number of arguments.
반환값
Returns TRUE upon successful creation of the aggregate, FALSE on
failure.
add a note
User Contributed Notes 2 notes
boris dot dd at gmail dot com ¶
7 years ago
sukmaagungsaputra at gmail dot com ¶
9 years ago
Lacks of example, right?
Let's try to give to SQlite3 the capability like ones of MySQL's
- REGEXP operator,
- MD5 function, and
- GROUP_CONCAT aggregate function
$db = new SQLite3($filename);
$db->createFunction('regexp', function ($a,$b) { return preg_match("/$a/i", $b); });
$db->createFunction('md5', function ($a) { return md5($a); });
$db->createAggregate ('group_concat',
function(&$context, $rownumber, $str) { $context[]=$str; return $context; },
function(&$context) {return implode(",", (array) $context); });
