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);Code language: PHP (php)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);'Code language: PHP (php)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);Code language: PHP (php)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);Code language: PHP (php)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();Code language: PHP (php)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
]);Code language: PHP (php)Step 6. Optionally get the last inserted id if the table has an auto-increment id:
$id = $pdo->lastInsertId();Code language: PHP (php)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();
}Code language: PHP (php)How it works.
First, load the configuration from the config.php file:
require_once 'config.php';Code language: PHP (php)The config.php contains the $db file path to the SQLite database:
<?php
$db = './database/my.db';Code language: PHP (php)Second, construct the data source name (dsn) to the SQLite database:
$dsn = "sqlite:$db";Code language: PHP (php)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();
}Code language: PHP (php)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();
}Code language: PHP (php)Fifth, create a new instance of the PDO class to open a new connection to the SQLite database file:
$pdo = new \PDO($dsn);Code language: PHP (php)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);Code language: PHP (php)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);Code language: PHP (php)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();Code language: PHP (php)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.dbCode language: PHP (php)Step 3. Format the query output:
.header on
.mode column
.nullvalue nullCode language: PHP (php)Step 4. Query data from the projects table:
select * from projects;Code language: PHP (php)Output:
project_id project_name
---------- ----------------
1 PHP SQLite Demo
2 Mastering SQLiteCode language: PHP (php)Step 5. Query data from the tasks table:
select * from tasks;Code language: PHP (php)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 2Code language: PHP (php)The output indicates that the script has successfully inserted data into the tables.
Summary
- Use
PreparedStatementto execute anINSERTstatement to insert a new row into a table.
