SQLite PHP: Insert Data Into Tables

Summary: in this tutorial, you will learn how to insert data into an SQLite table from PHP using PDO

This tutorial begins where the “Creating table in SQLite database from PHP” tutorial left off.

To insert data into an SQLite table in PHP, you follow these steps:

Step 1. Connect to the SQLite database by creating a new PDO instance with the path to the SQLite database file:

$pdo = new \PDO($dsn);

Step 2. Construct an INSERT statement for inserting data into a table.

If you want to pass values to the INSERT statement, you can use named placeholders (:parameter). For example:

$sql = 'INSERT INTO table_name(c1,c2)
        VALUES(:c1, :c2);'

In this example, :c1 and :c2 are placeholders for columns c1 and c2. When you execute the statement, you need to provide two values corresponding to :c1 and :c2.

Step 3. Call the prepare() method of the PDO object to prepare the INSERT statement for execution:

$stmt = $pdo->prepare($sql);

The prepare() method returns a PDOStatement object.

Step 4. Bind values to parameters by calling the bindValue() method of the PDOStatement object:

$stmt->bindValue(':c1', value1);
$stmt->bindValue(':c2', value2);

If you don’t want to bind values immediately, you can pass the values when executing the INSERT statement.

Step 5. Execute the statement by calling the execute() method of the PDOStatement object:

$stmt->execute();

To execute the INSERT statement without calling the bindValue() method, you can pass the values directly to the execute() method:

$stmt->execute([
   ':c1' => value1,
   ':c2' => value2
]);

Step 6. Optionally get the last inserted id if the table has an auto-increment id:

$id = $pdo->lastInsertId();

Inserting data into tables in PHP #

We’ll show you how to insert a new row into the projects and tasks tables.

Step 1. Create a new file called insert.php in the project directory.

Step 2. Add the following code to the insert.php file:

<?php 

require_once 'config.php';

$dsn = "sqlite:$db";

function insert_project($pdo, $projectName)  {
    $sql = 'INSERT INTO projects(project_name) VALUES(:project_name)';
    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':project_name', $projectName);
    $stmt->execute();
    return $pdo->lastInsertId();
}

function insert_task($pdo, $taskName,  $startDate, $completedDate, $completed, $projectId) {

    $sql = 'INSERT INTO tasks(task_name,start_date,completed_date,completed,project_id) 
            VALUES(:task_name,:start_date,:completed_date,:completed,:project_id)';

    $stmt = $pdo->prepare($sql);

    $stmt->execute([
        ':task_name' => $taskName,
        ':start_date' => $startDate,
        ':completed_date' => $completedDate,
        ':completed' => $completed,
        ':project_id' => $projectId,
    ]);

    return $pdo->lastInsertId();
}

try {
    // connect to database
    $pdo = new \PDO($dsn);

    // insert the first project
    $projectId =  insert_project($pdo, 'PHP SQLite Demo');

    // insert tasks for the first project
    insert_task($pdo, 'Prepare the sample database schema', '2016-06-01', '2016-06-01', 1, $projectId);
    insert_task($pdo, 'Create new tables ', '2016-05-01', null, 0, $projectId);
    insert_task($pdo, 'Insert some sample data', '2016-05-01', '2016-06-02', 1, $projectId);

    // insert the second project
    $projectId = insert_project($pdo, 'Mastering SQLite');

    // insert tasks for the second project
    insert_task($pdo,'Go to sqlitetutorial.net', '2016-06-01', null, 0, $projectId);
    insert_task($pdo,'Read all the tutorials.', '2016-06-01', null, 0, $projectId);
    insert_task($pdo,'Use Try It page to practice the SQLite commands.', '2016-06-01', null, 0, $projectId);
    insert_task($pdo,'Develop a simple SQLite-based application', '2016-06-15', null, 0, $projectId);
 
} catch (\PDOException $e) {
    echo $e->getMessage();
}

How it works.

First, load the configuration from the config.php file:

require_once 'config.php';

The config.php contains the $db file path to the SQLite database:

<?php

$db = './database/my.db';

Second, construct the data source name (dsn) to the SQLite database:

$dsn = "sqlite:$db";

Third, define a function that inserts a new row into the projects table:

function insert_project($pdo, $projectName)  {
    $sql = 'INSERT INTO projects(project_name) VALUES(:project_name)';
    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':project_name', $projectName);
    $stmt->execute();

    return $pdo->lastInsertId();
}

The insert_project function inserts a new row into the projects table and returns the project id.

Fourth, define a function that inserts a new row into the tasks table:

function insert_task($pdo, $taskName,  $startDate, $completedDate, $completed, $projectId) {

    $sql = 'INSERT INTO tasks(task_name,start_date,completed_date,completed,project_id) 
            VALUES(:task_name,:start_date,:completed_date,:completed,:project_id)';

    $stmt = $pdo->prepare($sql);

    $stmt->execute([
        ':task_name' => $taskName,
        ':start_date' => $startDate,
        ':completed_date' => $completedDate,
        ':completed' => $completed,
        ':project_id' => $projectId,
    ]);

    return $pdo->lastInsertId();
}

Fifth, create a new instance of the PDO class to open a new connection to the SQLite database file:

$pdo = new \PDO($dsn);

Sixth, insert a new row into the projects table, get the inserted id, and use it to insert some rows into the tasks table:

$projectId =  insert_project($pdo, 'PHP SQLite Demo');
insert_task($pdo, 'Prepare the sample database schema', '2016-06-01', '2016-06-01', 1, $projectId);
insert_task($pdo, 'Create new tables ', '2016-05-01', null, 0, $projectId);
insert_task($pdo, 'Insert some sample data', '2016-05-01', '2016-06-02', 1, $projectId);

Seventh, insert a second row into the projects table and related tasks into the tasks table:

$projectId = insert_project($pdo, 'Mastering SQLite');
insert_task($pdo,'Go to sqlitetutorial.net', '2016-06-01', null, 0, $projectId);
insert_task($pdo,'Read all the tutorials.', '2016-06-01', null, 0, $projectId);
insert_task($pdo,'Use Try It page to practice the SQLite commands.', '2016-06-01', null, 0, $projectId);
insert_task($pdo,'Develop a simple SQLite-based application', '2016-06-15', null, 0, $projectId);

Since an error may occur when connecting to the database and inserting data, we wrap these statements in a try-catch block.

Finally, display a message if an error occurs:

echo $e->getMessage();

Step 3. Run the insert.php script.

Verifying data #

The following steps show you how to query data from the projects and tasks tables to verify the insertion using the sqlite3 shell:

Step 1. Open your terminal and navigate to the database directory.

Step 2. Connect to the my.db database using the sqlite3 shell:

sqlite3 my.db

Step 3. Format the query output:

.header on
.mode column
.nullvalue null

Step 4. Query data from the projects table:

select * from projects;

Output:

project_id  project_name
----------  ----------------
1           PHP SQLite Demo
2           Mastering SQLite

Step 5. Query data from the tasks table:

select * from tasks;

Output:

task_id  task_name                                         completed  start_date  completed_date  project_id
-------  ------------------------------------------------  ---------  ----------  --------------  ----------
1        Prepare the sample database schema                1          2016-06-01  2016-06-01      1
2        Create new tables                                 0          2016-05-01  null            1
3        Insert some sample data                           1          2016-05-01  2016-06-02      1
4        Go to sqlitetutorial.net                          0          2016-06-01  null            2
5        Read all the tutorials.                           0          2016-06-01  null            2
6        Use Try It page to practice the SQLite commands.  0          2016-06-01  null            2
7        Develop a simple SQLite-based application         0          2016-06-15  null            2

The output indicates that the script has successfully inserted data into the tables.

Summary #

Use PreparedStatement to execute an INSERT statement to insert a new row into a table.

Was this tutorial helpful?