Registers a callback that compares two SQLite text values for ORDER BY or equality rules.
The objective is to understand the behavior and operational limits, not only the method name.
What It Does
Registers a callback that compares two SQLite text values for ORDER BY or equality rules.
Syntax and Return Value
Pdo\Sqlite::createCollation(...)The return value and exceptional cases depend on the call described here: Comparator must return a negative value, zero or positive value consistently; avoid locale-unstable sorting.
Requirements: install and enable the corresponding PHP driver or extension and configure the service before running the examples.
The driver-specific PDO subclasses are available starting with PHP 8.4, except setAuthorizer, which starts with PHP 8.5.
Example 1: Basic Use
The comparison callback reverses bytewise lexical ordering; use an ICU collation for linguistic ordering.
<?php
$sqlite = PDO::connect("sqlite::memory:");
$sqlite->exec("CREATE TABLE scores (name TEXT, points INTEGER)");
$sqlite->exec("INSERT INTO scores VALUES ('Ava',10),('Ben',20),('Cai',30)");
$sqlite->createCollation("REVERSE_TEXT", static fn(string $a, string $b): int => strcmp($b, $a));
foreach ($sqlite->query("SELECT name FROM scores ORDER BY name COLLATE REVERSE_TEXT") as $row) echo $row["name"] . PHP_EOL;Example 2: A Second Scenario
The comparison callback reverses bytewise lexical ordering; use an ICU collation for linguistic ordering. This additional check distinguishes the method's result from connection or statement state.
<?php
$sqlite = PDO::connect("sqlite::memory:");
$sqlite->exec("CREATE TABLE scores (name TEXT, points INTEGER)");
$sqlite->exec("INSERT INTO scores VALUES ('Ava',10),('Ben',20),('Cai',30)");
$sqlite->createCollation("REVERSE_TEXT", static fn(string $a, string $b): int => strcmp($b, $a));
foreach ($sqlite->query("SELECT name FROM scores ORDER BY name COLLATE REVERSE_TEXT") as $row) echo $row["name"] . PHP_EOL;
echo $sqlite->query("SELECT name FROM scores ORDER BY name COLLATE REVERSE_TEXT LIMIT 1")->fetchColumn();Example 3: Practical Checks
The comparison callback reverses bytewise lexical ordering; use an ICU collation for linguistic ordering. This additional check distinguishes the method's result from connection or statement state.
<?php
$sqlite = PDO::connect("sqlite::memory:");
$sqlite->exec("CREATE TABLE scores (name TEXT, points INTEGER)");
$sqlite->exec("INSERT INTO scores VALUES ('Ava',10),('Ben',20),('Cai',30)");
$sqlite->createCollation("REVERSE_TEXT", static fn(string $a, string $b): int => strcmp($b, $a));
foreach ($sqlite->query("SELECT name FROM scores ORDER BY name COLLATE REVERSE_TEXT") as $row) echo $row["name"] . PHP_EOL;
echo $sqlite->query("SELECT COUNT(*) FROM scores WHERE name = 'Ava' COLLATE REVERSE_TEXT")->fetchColumn();Important Behavior and Edge Cases
Comparator must return a negative value, zero or positive value consistently; avoid locale-unstable sorting.
For production use, also account for error handling, data validation, resource limits and environment-specific driver behavior.
Comparison with Related Approaches
| Approach | Important distinction |
|---|---|
| Pdo\Sqlite::createCollation | Registers a callback that compares two SQLite text values for ORDER BY or equality rules. |
| Related API / pattern | createFunction creates a general SQL function, not a collation comparator. |
Common Beginner Mistakes
| Mistake | Correct approach |
|---|---|
| Assuming the happy path covers edge cases | Comparator must return a negative value, zero or positive value consistently; avoid locale-unstable sorting. |
| Using a related API interchangeably | createFunction creates a general SQL function, not a collation comparator. |
Key Takeaways
- Registers a callback that compares two SQLite text values for ORDER BY or equality rules.
- Comparator must return a negative value, zero or positive value consistently; avoid locale-unstable sorting.
- createFunction creates a general SQL function, not a collation comparator.
Choose this API or pattern when its exact semantics match your task, and verify the result against the database driver and runtime used by your application.