PHP and SQLite: How to create database tables

PHP and SQLite: How to create a database table

Introduction:
In web development, the database is a very important part. SQLite is a lightweight embedded database management system widely used for small projects and mobile application development. This article will explain how to create database tables using PHP and SQLite, and provide code examples to help you understand better.

Step 1: Connect to SQLite database
Before using SQLite, you first need to install the SQLite extension of SQLite and PHP. After installation, we can use the following code to connect to the SQLite database:

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

Through the above code, we successfully created a SQLite database connection object named "database.sqlite" and assigned it to the variable $db.

Step 2: Create a table
In SQLite, we can use the following SQL statement to create a table:

<?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 SQL statement defined in the above code will create a table named "users ” form contains four fields: id, name, email and password. The id field is defined as the primary key and the auto-increment function is enabled.

Execute the SQL statement by calling the exec() method of the $db object. This method returns a Boolean value indicating whether the SQL statement was successfully executed. Based on the returned results, we can output the corresponding creation results.

Step 3: Verify whether the table is created successfully
In order to verify whether the table is created successfully, we can use the following code to query the table structure:

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

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

In the above code, we use the $db object The query() method executes a SQL query to obtain the structural information of the "users" table. Then, we use the fetchArray() method to get a row of data from the query result set and output the field name and type.

Through the above operations, we can verify whether the table has been successfully created and confirm whether the table structure meets our expectations.

Summary:
Through this article, we have learned the basic process of how to use PHP and SQLite to create database tables. First, we need to connect to the SQLite database; then, we can use SQL statements to create the table; finally, we can verify whether the table was successfully created by querying the table structure. I hope the code examples provided in this article can help you better understand and apply SQLite database.

The above is the detailed content of PHP and SQLite: How to create database tables. For more information, please follow other related articles on the PHP Chinese website!