Connect to SQLite with PHP

What is SQLite?

A database stores information in an organised way, like a neat spreadsheet. SQLite is a small database that lives in a single file. It needs no separate database server. PHP is a language that runs on web servers. PDO is PHP's standard tool for talking to databases. It works with many database types, including SQLite.

Before you start

PHP needs the pdo_sqlite extension. An extension is a small add-on that gives PHP extra features. To check, make a file with <?php phpinfo(); and open it in your browser. Look for "pdo_sqlite". Then delete the file. If it is missing, ask Hostvento support whether you can turn it on.

Steps

1. Create a new file named sqlite-demo.php in your website folder.
2. Connect to the database. If the file does not exist, SQLite makes it.

   <?php
   try {
       $db = new PDO('sqlite:' . __DIR__ . '/mydata.db');
       $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   } catch (PDOException $e) {
       die('Connection failed: ' . $e->getMessage());
   }

   The second line makes PDO show errors instead of hiding them.
3. Create a table. A table is a grid of rows and columns.

   $db->exec('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)');

4. Add a row. Use a placeholder (a question mark) to keep your data safe from bad input.

   $stmt = $db->prepare('INSERT INTO users (name) VALUES (?)');
   $stmt->execute(['Asha']);

5. Read the rows back.

   foreach ($db->query('SELECT * FROM users') as $row) {
       echo $row['id'] . ' - ' . htmlspecialchars($row['name']) . '<br>';
   }

   htmlspecialchars makes the text safe to show on a web page.
6. Open the file in your browser. Use yourdomain.com/sqlite-demo.php with your own domain. You should see your row.

Keep the database file safe

• Do not leave mydata.db inside a folder anyone can open by its web address. Put it above public_html if you can.
• Make sure PHP can write to the file and its folder.
• Keep a backup. The one file holds all your data.

Quick recap

• SQLite stores data in one file.
• Use new PDO('sqlite:path') to connect.
• Use prepare and execute for safe inserts.
• Protect and back up the .db file.