Skip to main content

SQLite PHP:将数据插入表中

我们将使用在创建表教程中创建的项目和任务表。

要向表中插入数据,请执行以下步骤:

下面的SQLiteInsert类有两种将数据插入表的方法。insertProject()方法将新项目插入项目表,insertTask()方法将新任务插入任务表。

<?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();
}

}

在insertTask()方法中,我们将插入的值作为数组传递给execute()方法,而不是绑定值。

我们在索引中使用以下代码。php文件,将项目及其相关任务插入项目和任务表。

SQLite PHP Insert Demo

<?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);

在本教程中,我们向您展示了如何使用PDO语句将数据插入SQLite表。