Creating databases in server memory - PHP

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!