Creating SQLite Database Tables with PHP: A Step-by-Step Guide

Introduction

In web development, databases are essential. SQLite is a lightweight embedded DBMS widely used in small projects and mobile applications. This article explains how to create SQLite database tables using PHP and provides code examples.

Step 1: Connect to SQLite Database

Before using SQLite, install SQLite and the PHP SQLite extension. After installation, the following code connects to an SQLite database:

<?php
  $db = new SQLite3('database.sqlite');
?>

The code creates a SQLite3 object for a file named database.sqlite and assigns it to the variable $db.

Step 2: Create Table

Use an SQL statement to create a table. The example creates a users table with id, name, email, and password fields, where id is an auto‑increment primary key.

<?php
  // 定义SQL语句
  $sql = "CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      email TEXT NOT NULL,
      password TEXT NOT NULL
  )";

  // 执行SQL语句
  $result = $db->exec($sql);

  // 检查是否成功创建表格
  if($result) {
      echo "表格创建成功！";
  } else {
      echo "表格创建失败！";
  }
?>

The script executes the SQL with $db->exec() and outputs whether the table creation succeeded.

Step 3: Verify Table Creation

To verify the table, run a PRAGMA query to retrieve the table schema and output each column’s name and type.

<?php
  // 查询表格结构
  $result = $db->query("PRAGMA table_info(users)");

  // 循环输出查询结果
  while($row = $result->fetchArray()) {
      echo $row['name'] . ": " . $row['type'] . "<br/>";
  }
?>

The query() and fetchArray() methods are used to fetch and display the structure, confirming successful creation.

Conclusion

The article demonstrates the basic process of connecting to SQLite, creating a table with PHP, and verifying its structure, providing code snippets to help readers understand and apply SQLite databases in their web projects.