Using PHP with SQLite

  PHP provides built-in support for SQLite databases, allowing developers to easily work with SQLite databases using PHP. In order to write to an SQLite database using PHP, you will need to follow these steps:

1. Create an SQLite database file.
2. Connect to the SQLite database.
3. Prepare an SQL statement.
4. Execute the SQL statement.
5. Close the database connection.

Here is a sample PHP script demonstrating how to perform these steps:

<?php
// 1. Create an SQLite database file
$db_filename = 'my_database.db';

// Check if the database file exists
if (!file_exists($db_filename)) {
    // Create an empty database file
    touch($db_filename);
}

// 2. Connect to the SQLite database
try {
    $db = new PDO("sqlite:" . $db_filename);
    // Set errormode to exceptions
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
    exit;
}

// Begin a transaction
$db->beginTransaction();

try {
    // Create the users table if it doesn't exist
    $db->exec("CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY,
                    username TEXT NOT NULL,
                    email TEXT NOT NULL,
                    age INTEGER
                )");
    
    // Commit the transaction
    $db->commit();
} catch (PDOException $e) {
    // Rollback the transaction if there was an error
    $db->rollBack();
    echo "Table creation failed: " . $e->getMessage();
    exit;
}

// 3. Prepare an SQL statement
$sql = "INSERT INTO users (username, email, age) VALUES (:username, :email, :age)";

// Sample data for insertion
$data = [
    'username' => 'JohnDoe',
    'email' => 'john.doe@example.com',
    'age' => 30
];

try {
    // 4. Execute the SQL statement
    $stmt = $db->prepare($sql);
    $stmt->bindParam(':username', $data['username'], PDO::PARAM_STR);
    $stmt->bindParam(':email', $data['email'], PDO::PARAM_STR);
    $stmt->bindParam(':age', $data['age'], PDO::PARAM_INT);
    $stmt->execute();

    echo "New record created successfully";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

// 5. Close the database connection
$db = null;
?>

Here’s a breakdown of the code with explanations for each section:

1. Create an SQLite database file:

   $db_filename = 'my_database.db';
   

   This line sets the filename for the SQLite database file. If the file does not exist, it will be created later in the script.

2. Check if the database file exists and create it if necessary:

   if (!file_exists($db_filename)) {
       touch($db_filename);
   }
   

   This block checks if the database file exists using the file_exists() function. If it doesn’t, it creates an empty file with the specified name using the touch() function.

3. Connect to the SQLite database:

   try {
       $db = new PDO("sqlite:" . $db_filename);
       $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   } catch (PDOException $e) {
       echo "Connection failed: " . $e->getMessage();
       exit;
   }
   

   This block establishes a connection to the SQLite database using the PDO (PHP Data Objects) extension. It sets the error mode to use exceptions so that errors will throw exceptions that can be caught and handled. If there is an error connecting to the database, a message will be displayed, and the script will exit.

4. Begin a transaction:

   $db->beginTransaction();
   

   This line starts a new database transaction. A transaction groups multiple SQL queries into a single unit of work. If one query fails, the entire transaction can be rolled back.

5. Create the users table if it doesn’t exist:

   try {
       $db->exec("CREATE TABLE IF NOT EXISTS users (
                       id INTEGER PRIMARY KEY,
                       username TEXT NOT NULL,
                       email TEXT NOT NULL,
                       age INTEGER
                   )");
       $db->commit();
   } catch (PDOException $e) {
       $db->rollBack();
       echo "Table creation failed: " . $e->getMessage();
       exit;
   }
   

   This block attempts to create the users table if it doesn’t exist in the database. If the table creation is successful, it commits the transaction. If there is an error, the transaction is rolled back, an error message is displayed, and the script exits.

6. Prepare an SQL statement:

   $sql = "INSERT INTO users (username, email, age) VALUES (:username, :email, :age)";
   

   This line prepares an SQL statement to insert a new record into the users table. The statement uses placeholders (:username, :email, :age) that will be replaced with actual values later.

7. Sample data for insertion:

   $data = [
       'username' => 'JohnDoe',
       'email' => 'john.doe@example.com',
       'age' => 30
   ];
   

   This array contains sample data that will be inserted into the users table.

8. Execute the SQL statement:

   try {
       $stmt = $db->prepare($sql);
       $stmt->bindParam(':username', $data['username'], PDO::PARAM_STR);
       $stmt->bindParam(':email', $data['email'], PDO::PARAM_STR);
       $stmt->bindParam(':age', $data['age'], PDO::PARAM_INT);
       $stmt->execute();
   
       echo "New record created successfully";
   } catch (PDOException $e) {
       echo "Error: " . $e->getMessage();
   }
   

   This block prepares the SQL statement using the prepare() method, binds the values from the $data array to the placeholders in the statement, and then executes it. If the insertion is successful, a success message is displayed.

Here are the basic steps to use SQLite with PHP:

1. Enable the SQLite extension: Before you can use SQLite with PHP, you need to enable the SQLite extension in your PHP configuration file (php.ini).
2. Connect to the SQLite database: To connect to an SQLite database from PHP, you can use the sqlite_open() function or the PDO (PHP Data Objects) extension.
3. Execute SQL queries: Once you are connected to the database, you can execute SQL queries using the sqlite_query() function or the PDO extension. You can use SQL to create tables, insert data, update data, delete data, and retrieve data from the database.
4. Close the database connection: When you are finished working with the database, you should close the database connection using the sqlite_close() function or the PDO close() method.

  Here is another example code snippet that demonstrates connecting to an SQLite database, creating a table, and inserting data:

// Connect to the database
$db = sqlite_open('mydatabase.sqlite');

// Create a table
sqlite_query($db, 'CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)');

// Insert some data
sqlite_query($db, "INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')");
sqlite_query($db, "INSERT INTO users (name, email) VALUES ('Jane Doe', 'jane@example.com')");

// Close the database connection
sqlite_close($db);

  This is just a basic example, and there are many other things you can do with SQLite and PHP, such as using prepared statements to prevent SQL injection attacks, working with transactions, and more.