sqlite
  1. sqlite-php-sqlite
PreviousNext

PHP SQLite Connectivity

SQLite is a popular embeddable relational database management system. PHP provides extensive support for SQLite, making it easy to connect to a database, create tables, and perform other database operations in your PHP application.

Syntax

To connect to an SQLite database using PHP, you can use the following syntax:

$db = new SQLite3('filename.db');

Here, filename.db is the name of the SQLite database file that you want to connect to.

Example

Suppose we want to create an SQLite database to store information about books, including the title, author, and publication year. We can create a database called books.db using the following code:

$db = new SQLite3('books.db');

We can then create a table called books with columns for the title, author, and publication year using the following code:

$db->exec('CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author TEXT, year INTEGER)');

We can insert data into the books table using the following code:

$db->exec("INSERT INTO books (title, author, year) VALUES ('To Kill a Mockingbird', 'Harper Lee', 1960)");
$db->exec("INSERT INTO books (title, author, year) VALUES ('1984', 'George Orwell', 1949)");

We can retrieve the data from the books table using the following code:

$results = $db->query('SELECT * FROM books');
while ($row = $results->fetchArray()) {
    echo $row['id'] . ' - ' . $row['title'] . ' by ' . $row['author'] . ' (' . $row['year'] . ')' . PHP_EOL;
}

Output

The output of the above code would be:

1 - To Kill a Mockingbird by Harper Lee (1960)
2 - 1984 by George Orwell (1949)

Explanation

In the example above, we create an SQLite database books.db using the new SQLite3('books.db') syntax. We then create a table called books with columns for the title, author, and publication year using the exec() method.

We insert two rows of data into the books table using the exec() method, and then retrieve the data using the query() method and a SELECT statement. We loop through the data using a while loop and print out the values of each row using the fetchArray() method.

Use

PHP SQLite connectivity can be used for a variety of database applications, including data storage, data retrieval, and other tasks that require database connectivity. It is particularly useful for smaller applications that require a lightweight database system.

Important Points

  • SQLite databases are stored in a single file, making them easy to distribute and move between systems.
  • SQLite does not require a server. Everything is contained within the application itself.
  • SQLite supports most standard SQL statements, making it compatible with other SQL-based database systems.
  • SQLite is not suitable for large-scale, high-traffic applications.

Summary

In this tutorial, we learned about PHP SQLite connectivity and saw example code for connecting to a database, creating tables, inserting data, and retrieving data. We also discussed the advantages and disadvantages of using SQLite for database applications.

Java SQLitePython SQLite
Published on: