Additional Methods for Using SQLite with PHP 5 - Creating databases in server memory
(Page 5 of 5 )
The last SQLite feature that I plan to cover here concerns specifically the creation of databases in server memory, instead of using the conventional file system. As you can imagine, this type of database can be used (among other situations) in those cases where you need to have at your disposal a fully-structured database relational system, but your data will be rather temporary, at least during the execution of your application.
That being said, defining a memory-based database with SQLite is reduced to code something as simple as this:
// example using memory-based database
// create a new memory-based database
$db = new SQLiteDatabase(":memory:");
// create table 'USERS' and insert some data
$db->query("BEGIN;
CREATE TABLE users (id INTEGER PRIMARY KEY, name VARCHAR
(255),email VARCHAR(255));
INSERT INTO users (id,name,email) VALUES
(NULL,'User1','user1@domain.com');
INSERT INTO users (id,name,email) VALUES
(NULL,'User2','user2@domain.com');
COMMIT;");
// display number of affected rows after the insertion
echo $db->changes().' rows affected by the insertion<br />';
// display ID of last inserted row
echo "ID of last inserted row is: ".$db->lastInsertRowid();
/*
displays the following
2 rows affected by the insertion
ID of last inserted row is: 2
*/
As shown above, a new database has been created in memory by simply specifying the “:memory” argument for the corresponding SQLite constructor. After this process has been performed, I defined a “USERS” table, in addition to inserting some trivial data, and finally displayed the ID that corresponds to the last inserted row.
As I always suggest, try creating different memory-based databases and watch what happens in each case. The process is truly educational.
Final thoughts
We’ve come to the end of this series. In these three consecutive tutorials, I took an in-depth look at the most relevant methods that come with the SQLite RDBMS, which has been included with PHP 5.
As you learned here, if your database-driven application doesn’t require all the features offered by MySQL, or another RDBMS, then this tight yet powerful library is worth considering.
See you in the next PHP article!
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
