How to get started with SQLite and PHP in 5 minutes

Photo by Jan Antonin Kolar on Unsplash

First you need a local server, so download and install XAMPP from this source. It's available for all desktop platforms. Open the application and start all servers.

Now open your shell, change directory to the XAMPP application folder and enter its subdirectory /htdocs .

Windows:
cd C:\xampp\htdocs
macOS:
cd /Applications/XAMPP/xamppfiles/htdocs

You’re ready to create your first SQLite database file, which you can query with PHP in your webpage afterwards. The following statements build this file with the name firstdatabase.sq3 and add a table to it.

sqlite3 firstdatabase.sq3
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL);

Your first table named products will list some goods and their prices. It now consists of three columns: An id column (for the primary key), a product name column and a pricing column.

After executing the commands above, just add some data to your database. The NULL arguments tells SQLite to take care of the unique identifiers itself.

INSERT INTO products VALUES (NULL, 'Apple', 0.99);
INSERT INTO products VALUES (NULL, 'Pizza', 3.30);
INSERT INTO products VALUES (NULL, 'Bread', 1.30);
INSERT INTO products VALUES (NULL, 'Milk', 0.89);
INSERT INTO products VALUES (NULL, 'Beer', 6.90);

Now you surely want to see some data in your browser. Just create an products.php file in the /htdocs directory and paste in the following lines.

<?php
$db = new SQLite3('firstdatabase.sq3');
$allProductsQuery = "SELECT * FROM products";
$productList = $db->query($allProductsQuery);
while ($row = $productList->fetchArray(SQLITE3_ASSOC)){
  echo $row['name'] . ': $' . $row['price'] . '<br/>';
}
?>

This code snippet opens your database, queries for all products and generates HTML content, which shows each product and its according price line by line.

As XAMPP runs a local server on your machine, you can reach the created stuff in the /htdocs directory by calling http://localhost/products.php

Okay, I must confess that it takes a bit longer than 5 minutes for absolute beginners, but it was worth the time, wasn't it?

If this article was helpful for you please leave some claps. I am active on Twitter as well.