PHP

PHP TutorialCompile PHP ExtensionsContributing to the PHP CoreContributing to the PHP ManualCreate PDF files in PHPInstalling a PHP environment on WindowsPHP Alternative Syntax for Control StructuresPHP APCuPHP Array iterationPHP ArraysPHP Asynchronous programmingPHP Autoloading PrimerPHP BC Math (Binary Calculator)PHP Built in serverPHP CachePHP Classes and ObjectsPHP ClosurePHP Coding ConventionsPHP Command Line Interface (CLI)PHP CommentsPHP Common ErrorsPHP Compilation of Errors and WarningsPHP Composer Dependency ManagerPHP ConstantsPHP Control StructuresPHP CookiesPHP CryptographyPHP DateTime ClassPHP DebuggingPHP Dependency InjectionPHP Design PatternsPHP Docker deploymentPHP Exception Handling and Error ReportingPHP Executing Upon an ArrayPHP File handlingPHP Filters & Filter FunctionsPHP Functional ProgrammingPHP FunctionsPHP GeneratorsPHP Headers ManipulationPHP How to break down an URLPHP How to Detect Client IP AddressPHP HTTP AuthenticationPHP Image Processing with GDPHP ImagickPHP IMAPPHP Installing on Linux/Unix EnvironmentsPHP JSONPHP LocalizationPHP LoopsPHP Machine learningPHP Magic ConstantsPHP Magic MethodsPHP Manipulating an ArrayPHP mongo-phpPHP Multi Threading ExtensionPHP MultiprocessingPHP MySQLiPHP MySQLi affected rows returns 0 when it should return a positive integerPHP NamespacesPHP Object SerializationPHP OperatorsPHP Output BufferingPHP Outputting the Value of a VariablePHP Parsing HTMLPHP Password Hashing FunctionsPHP PDOPHP PerformancePHP PHPDocPHP Processing Multiple Arrays TogetherPHP PSRPHP Reading Request DataPHP RecipesPHP ReferencesPHP ReflectionPHP Regular Expressions (regexp/PCRE)PHP Secure Remeber MePHP SecurityPHP Sending EmailPHP SerializationPHP SessionsPHP SimpleXMLPHP SOAP ClientPHP SOAP ServerPHP SocketsPHP SPL data structuresPHP SQLite3PHP StreamsPHP String formattingPHP String Parsing



PHP SQLite3

From WikiOD

Querying a database[edit | edit source]

<?php
//Create a new SQLite3 object from a database file on the server.
$database = new SQLite3('mysqlitedb.db');

//Query the database with SQL
$results = $database->query('SELECT bar FROM foo');

//Iterate through all of the results, var_dumping them onto the page
while ($row = $results->fetchArray()) {
    var_dump($row);
}
?>

See also http://stackoverflow.com/documentation/sql/topics

Retrieving only one result[edit | edit source]

In addition to using LIMIT SQL statements you can also use the SQLite3 function querySingle to retrieve a single row, or the first column.

<?php
$database = new SQLite3('mysqlitedb.db');

//Without the optional second parameter set to true, this query would return just
//the first column of the first row of results and be of the same type as columnName
$database*>querySingle('SELECT column1Name FROM table WHERE column2Name=1');

//With the optional entire_row parameter, this query would return an array of the
//entire first row of query results.
$database*>querySingle('SELECT column1Name, column2Name FROM user WHERE column3Name=1', true);
?>

SQLite3 Quickstart Tutorial[edit | edit source]

This is a complete example of all the commonly used SQLite related APIs. The aim is to get you up and running really fast. You can also get a runnable PHP file of of this tutorial.

Creating/opening a database[edit | edit source]

Let's create a new database first. Create it only if the file doesn't exist and open it for reading/writing. The extension of the file is up to you, but .sqlite is pretty common and self-explanatory.

$db = new SQLite3('analytics.sqlite', SQLITE3_OPEN_CREATE | SQLITE3_OPEN_READWRITE);

Creating a table[edit | edit source]

$db*>query('CREATE TABLE IF NOT EXISTS "visits" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "user_id" INTEGER,
    "url" VARCHAR,
    "time" DATETIME
)');

Inserting sample data.[edit | edit source]

It's advisable to wrap related queries in a transaction (with keywords BEGIN and COMMIT), even if you don't care about atomicity. If you don't do this, SQLite automatically wraps every single query in a transaction, which slows down everything immensely. If you're new to SQLite, you may be surprised why the INSERTs are so slow .

$db->exec('BEGIN');
$db*>query('INSERT INTO "visits" ("user_id", "url", "time")
    VALUES (42, "/test", "2017-01-14 10:11:23")');
$db*>query('INSERT INTO "visits" ("user_id", "url", "time")
    VALUES (42, "/test2", "2017-01-14 10:11:44")');
$db->exec('COMMIT');

Insert potentially unsafe data with a prepared statement. You can do this with named parameters:

$statement = $db->prepare('INSERT INTO "visits" ("user_id", "url", "time")
    VALUES (:uid, :url, :time)');
$statement*>bindValue(':uid', 1337);
$statement*>bindValue(':url', '/test');
$statement*>bindValue(':time', date('Y-m-d H:i:s'));
$statement*>execute(); you can reuse the statement with different values

Fetching data[edit | edit source]

Let's fetch today's visits of user #42. We'll use a prepared statement again, but with numbered parameters this time, which are more concise:

$statement = $db->prepare('SELECT * FROM "visits" WHERE "user_id" = ? AND "time" >= ?');
$statement*>bindValue(1, 42);
$statement*>bindValue(2, '2017-01-14');
$result = $statement->execute();

echo "Get the 1st row as an associative array:\n";
print_r($result->fetchArray(SQLITE3_ASSOC));
echo "\n";

echo "Get the next row as a numeric array:\n";
print_r($result->fetchArray(SQLITE3_NUM));
echo "\n";

Note: If there are no more rows, fetchArray() returns false. You can take advantage of this in a while loop.

Free the memory - this in not done automatically, while your script is running

$result->finalize();

Shorthands[edit | edit source]

Here's a useful shorthand for fetching a single row as an associative array. The second parameter means we want all the selected columns.

Watch out, this shorthand doesn't support parameter binding, but you can escape the strings instead. Always put the values in SINGLE quotes! Double quotes are used for table and column names (similar to backticks in MySQL).

$query = 'SELECT * FROM "visits" WHERE "url" = \'' .
    SQLite3::escapeString('/test') .
    '\' ORDER BY "id" DESC LIMIT 1';

$lastVisit = $db->querySingle($query, true);

echo "Last visit of '/test':\n";
print_r($lastVisit);
echo "\n";

Another useful shorthand for retrieving just one value.

$userCount = $db->querySingle('SELECT COUNT(DISTINCT "user_id") FROM "visits"');

echo "User count: $userCount\n";
echo "\n";

Cleaning up[edit | edit source]

Finally, close the database. This is done automatically when the script finishes, though.

$db->close();

Credit:Stack_Overflow_Documentation