Summary: in this tutorial, we will show you how to use PHP PDO to insert data into SQLite tables.
We will use the projects and tasks table that we created in the creating table tutorial.
To insert data into a table, you follow these steps:
- Connect to the SQLite database by creating a new PDO instance with the path to the SQLite database file.
- Compose an INSERT statement. If you want to supply the values for the
INSERT
statement, you use the named placeholders in the form:name
whichname
is the parameter that you will provide the value. - Call the
prepare()
method of the PDO object to prepare theINSERT
statement for execution. This method returns aPDOStatement
object. - Bind values to the parameters by calling the
bindValue()
method of thePDOStatement
object. - Execute the statement by calling the
execute()
method of thePDOStatement
object. - In case the table has a generated autoincrement column as the primary key, you can get the inserted id by calling the
lastInsertId()
method of the PDO object.
The following SQLiteInsert
class has two methods for inserting data into tables. The insertProject()
method inserts a new project into the projects
table and the insertTask()
method inserts a new task into the tasks
table.
<?php
namespace App;
/**
* PHP SQLite Insert Demo
*/
class SQLiteInsert {
/**
* PDO object
* @var \PDO
*/
private $pdo;
/**
* Initialize the object with a specified PDO object
* @param \PDO $pdo
*/
public function __construct($pdo) {
$this->pdo = $pdo;
}
/**
* Insert a new project into the projects table
* @param string $projectName
* @return the id of the new project
*/
public function insertProject($projectName) {
$sql = 'INSERT INTO projects(project_name) VALUES(:project_name)';
$stmt = $this->pdo->prepare($sql);
$stmt->bindValue(':project_name', $projectName);
$stmt->execute();
return $this->pdo->lastInsertId();
}
/**
* Insert a new task into the tasks table
* @param type $taskName
* @param type $startDate
* @param type $completedDate
* @param type $completed
* @param type $projectId
* @return int id of the inserted task
*/
public function insertTask($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 = $this->pdo->prepare($sql);
$stmt->execute([
':task_name' => $taskName,
':start_date' => $startDate,
':completed_date' => $completedDate,
':completed' => $completed,
':project_id' => $projectId,
]);
return $this->pdo->lastInsertId();
}
}
Code language: PHP (php)
In the insertTask()
method, instead of binding values, we pass the inserted values as an array to the execute()
method.
We use the following code in the index.php file to insert projects and their associated tasks into the projects and tasks table.
<?php
require 'vendor/autoload.php';
use App\SQLiteConnection;
use App\SQLiteInsert;
$pdo = (new SQLiteConnection())->connect();
$sqlite = new SQLiteInsert($pdo);
// insert a new project
$projectId = $sqlite->insertProject('PHP SQLite Demo');
// insert some tasks for the project
$sqlite->insertTask('Prepare the sample database schema', '2016-06-01', '2016-06-01', 1, $projectId);
$sqlite->insertTask('Create new tables ', '2016-05-01', null, 0, $projectId);
$sqlite->insertTask('Insert some sample data', '2016-05-01', '2016-06-02', 1, $projectId);
// insert a second project
$projectId = $sqlite->insertProject('Mastering SQLite');
// insert the tasks for the second project
$sqlite->insertTask('Go to sqlitetutorial.net', '2016-06-01', null, 0, $projectId);
$sqlite->insertTask('Read all the tutorials.', '2016-06-01', null, 0, $projectId);
$sqlite->insertTask('Use Try It page to practice the SQLite commands.', '2016-06-01', null, 0, $projectId);
$sqlite->insertTask('Develop a simple SQLite-based application', '2016-06-15', null, 0, $projectId);
Code language: PHP (php)
In this tutorial, we have shown you how to use the PDOStatement to insert data into the SQLite tables.