{"id":924,"date":"2016-05-16T08:41:16","date_gmt":"2016-05-16T01:41:16","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=924"},"modified":"2024-10-28T21:36:47","modified_gmt":"2024-10-28T14:36:47","slug":"insert","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-php\/insert\/","title":{"rendered":"SQLite PHP: Inserting Data"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to insert data into an SQLite table from PHP using PDO<\/p>\n\n\n\n

This tutorial begins where the “Creating table in SQLite database from PHP<\/a>” tutorial left off.<\/p>\n\n\n\n

To insert data into an SQLite table in PHP, you follow these steps:<\/p>\n\n\n\n

Step 1. Connect to the SQLite database<\/a> by creating a new PDO<\/code> instance with the path to the SQLite database file:<\/p>\n\n\n

$pdo = new<\/span> \\PDO($dsn);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
If you want to pass values to the INSERT<\/code> statement, you can use named placeholders (:parameter<\/code>). For example:<\/p>\n\n\n
$sql = 'INSERT INTO table_name(c1,c2)\n        VALUES(:c1, :c2);'<\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, :c1<\/code> and :c2<\/code> are placeholders for columns c1<\/code> and c2<\/code>. When you execute the statement, you need to provide two values corresponding to :c1<\/code> and :c2<\/code>.<\/p>\n\n\n\n
Step 3. Call the prepare()<\/code> method of the PDO<\/code> object to prepare the INSERT<\/code> statement for execution:<\/p>\n\n\n
$stmt = $pdo->prepare($sql);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
The prepare()<\/code> method returns a PDOStatement<\/code> object.<\/p>\n\n\n\n
Step 4. Bind values to parameters by calling the bindValue()<\/code> method of the PDOStatement<\/code> object:<\/p>\n\n\n
$stmt->bindValue(':c1'<\/span>, value1);\n$stmt->bindValue(':c2'<\/span>, value2);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
If you don’t want to bind values immediately, you can pass the values when executing the INSERT<\/code> statement.<\/p>\n\n\n\n
Step 5. Execute the statement by calling the execute()<\/code> method of the PDOStatement<\/code> object:<\/p>\n\n\n
$stmt->execute();<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
To execute the INSERT<\/code> statement without calling the bindValue()<\/code> method, you can pass the values directly to the execute()<\/code> method:<\/p>\n\n\n
$stmt->execute([\n   ':c1'<\/span> => value1,\n   ':c2'<\/span> => value2\n]);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
$id = $pdo->lastInsertId();<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Inserting data into tables in PHP<\/h2>\n\n\n\n
We’ll show you how to insert a new row into the projects<\/code> and tasks<\/code> tables. <\/p>\n\n\n\n
Step 1. Create a new file called insert.php<\/code> in the project directory.<\/p>\n\n\n\n
Step 2. Add the following code to the insert.php<\/code> file:<\/p>\n\n\n
<?php<\/span> \n\nrequire_once<\/span> 'config.php'<\/span>;\n\n$dsn = \"sqlite:$db\"<\/span>;\n\nfunction<\/span> insert_project<\/span>($pdo, $projectName)<\/span>  <\/span>{\n    $sql = 'INSERT INTO projects(project_name) VALUES(:project_name)'<\/span>;\n    $stmt = $pdo->prepare($sql);\n    $stmt->bindValue(':project_name'<\/span>, $projectName);\n    $stmt->execute();\n    return<\/span> $pdo->lastInsertId();\n}\n\nfunction<\/span> insert_task<\/span>($pdo, $taskName,  $startDate, $completedDate, $completed, $projectId)<\/span> <\/span>{\n\n    $sql = 'INSERT INTO tasks(task_name,start_date,completed_date,completed,project_id) \n            VALUES(:task_name,:start_date,:completed_date,:completed,:project_id)'<\/span>;\n\n    $stmt = $pdo->prepare($sql);\n\n    $stmt->execute([\n        ':task_name'<\/span> => $taskName,\n        ':start_date'<\/span> => $startDate,\n        ':completed_date'<\/span> => $completedDate,\n        ':completed'<\/span> => $completed,\n        ':project_id'<\/span> => $projectId,\n    ]);\n\n    return<\/span> $pdo->lastInsertId();\n}\n\ntry<\/span> {\n    \/\/ connect to database<\/span>\n    $pdo = new<\/span> \\PDO($dsn);\n\n    \/\/ insert the first project<\/span>\n    $projectId =  insert_project($pdo, 'PHP SQLite Demo'<\/span>);\n\n    \/\/ insert tasks for the first project<\/span>\n    insert_task($pdo, 'Prepare the sample database schema'<\/span>, '2016-06-01'<\/span>, '2016-06-01'<\/span>, 1<\/span>, $projectId);\n    insert_task($pdo, 'Create new tables '<\/span>, '2016-05-01'<\/span>, null<\/span>, 0<\/span>, $projectId);\n    insert_task($pdo, 'Insert some sample data'<\/span>, '2016-05-01'<\/span>, '2016-06-02'<\/span>, 1<\/span>, $projectId);\n\n    \/\/ insert the second project<\/span>\n    $projectId = insert_project($pdo, 'Mastering SQLite'<\/span>);\n\n    \/\/ insert tasks for the second project<\/span>\n    insert_task($pdo,'Go to sqlitetutorial.net'<\/span>, '2016-06-01'<\/span>, null<\/span>, 0<\/span>, $projectId);\n    insert_task($pdo,'Read all the tutorials.'<\/span>, '2016-06-01'<\/span>, null<\/span>, 0<\/span>, $projectId);\n    insert_task($pdo,'Use Try It page to practice the SQLite commands.'<\/span>, '2016-06-01'<\/span>, null<\/span>, 0<\/span>, $projectId);\n    insert_task($pdo,'Develop a simple SQLite-based application'<\/span>, '2016-06-15'<\/span>, null<\/span>, 0<\/span>, $projectId);\n \n} catch<\/span> (\\PDOException $e) {\n    echo<\/span> $e->getMessage();\n}<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, load the configuration from the config.php<\/code> file:<\/p>\n\n\n
require_once<\/span> 'config.php'<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
The config.php<\/code> contains the $db<\/code> file path to the SQLite database:<\/p>\n\n\n
<?php<\/span>\n\n$db = '.\/database\/my.db'<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, construct the data source name (dsn<\/code>) to the SQLite database:<\/p>\n\n\n
$dsn = \"sqlite:$db\"<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
function<\/span> insert_project<\/span>($pdo, $projectName)<\/span>  <\/span>{\n    $sql = 'INSERT INTO projects(project_name) VALUES(:project_name)'<\/span>;\n    $stmt = $pdo->prepare($sql);\n    $stmt->bindValue(':project_name'<\/span>, $projectName);\n    $stmt->execute();\n\n    return<\/span> $pdo->lastInsertId();\n}<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
The insert_project<\/code> function inserts a new row into the projects<\/code> table and returns the project id.<\/p>\n\n\n\n
Fourth, define a function that inserts a new row into the tasks<\/code> table:<\/p>\n\n\n
function<\/span> insert_task<\/span>($pdo, $taskName,  $startDate, $completedDate, $completed, $projectId)<\/span> <\/span>{\n\n    $sql = 'INSERT INTO tasks(task_name,start_date,completed_date,completed,project_id) \n            VALUES(:task_name,:start_date,:completed_date,:completed,:project_id)'<\/span>;\n\n    $stmt = $pdo->prepare($sql);\n\n    $stmt->execute([\n        ':task_name'<\/span> => $taskName,\n        ':start_date'<\/span> => $startDate,\n        ':completed_date'<\/span> => $completedDate,\n        ':completed'<\/span> => $completed,\n        ':project_id'<\/span> => $projectId,\n    ]);\n\n    return<\/span> $pdo->lastInsertId();\n}<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, create a new instance of the PDO<\/code> class to open a new connection to the SQLite database file:<\/p>\n\n\n
 $pdo = new<\/span> \\PDO($dsn);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Sixth, insert a new row into the projects<\/code> table, get the inserted id, and use it to insert some rows into the tasks<\/code> table:<\/p>\n\n\n
$projectId =  insert_project($pdo, 'PHP SQLite Demo'<\/span>);\ninsert_task($pdo, 'Prepare the sample database schema'<\/span>, '2016-06-01'<\/span>, '2016-06-01'<\/span>, 1<\/span>, $projectId);\ninsert_task($pdo, 'Create new tables '<\/span>, '2016-05-01'<\/span>, null<\/span>, 0<\/span>, $projectId);\ninsert_task($pdo, 'Insert some sample data'<\/span>, '2016-05-01'<\/span>, '2016-06-02'<\/span>, 1<\/span>, $projectId);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Seventh, insert a second row into the projects<\/code> table and related tasks into the tasks<\/code> table:<\/p>\n\n\n
$projectId = insert_project($pdo, 'Mastering SQLite'<\/span>);\ninsert_task($pdo,'Go to sqlitetutorial.net'<\/span>, '2016-06-01'<\/span>, null<\/span>, 0<\/span>, $projectId);\ninsert_task($pdo,'Read all the tutorials.'<\/span>, '2016-06-01'<\/span>, null<\/span>, 0<\/span>, $projectId);\ninsert_task($pdo,'Use Try It page to practice the SQLite commands.'<\/span>, '2016-06-01'<\/span>, null<\/span>, 0<\/span>, $projectId);\ninsert_task($pdo,'Develop a simple SQLite-based application'<\/span>, '2016-06-15'<\/span>, null<\/span>, 0<\/span>, $projectId);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, display a message if an error occurs:<\/p>\n\n\n
echo<\/span> $e->getMessage();<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Run the insert.php<\/code> script.<\/p>\n\n\n\n
Verifying data<\/h2>\n\n\n\n
The following steps show you how to query data from the projects<\/code> and tasks<\/code> tables to verify the insertion using the sqlite3<\/code> shell:<\/p>\n\n\n\n
Step 1. Open your terminal and navigate to the database<\/code> directory.<\/p>\n\n\n\n
Step 2.  Connect to the my.db<\/code> database using the sqlite3<\/code> shell:<\/p>\n\n\n
sqlite3 my.db<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Format the query output:<\/p>\n\n\n
.header on\n.mode column\n.nullvalue null<\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 4. Query data from the projects<\/code> table:<\/p>\n\n\n
select * from projects;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
project_id  project_name\n----------  ----------------\n1<\/span>           PHP SQLite Demo\n2<\/span>           Mastering SQLite<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 5. Query data from the tasks<\/code> table:<\/p>\n\n\n
select * from tasks;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
task_id  task_name                                         completed  start_date  completed_date  project_id\n-------  ------------------------------------------------  ---------  ----------  --------------  ----------\n1<\/span>        Prepare the sample database schema                1<\/span>          2016<\/span>-06<\/span>-01<\/span>  2016<\/span>-06<\/span>-01<\/span>      1<\/span>\n2<\/span>        Create new<\/span> tables                                 0<\/span>          2016<\/span>-05<\/span>-01<\/span>  null<\/span>            1<\/span>\n3<\/span>        Insert some sample data                           1<\/span>          2016<\/span>-05<\/span>-01<\/span>  2016<\/span>-06<\/span>-02<\/span>      1<\/span>\n4<\/span>        Go to sqlitetutorial.net                          0<\/span>          2016<\/span>-06<\/span>-01<\/span>  null<\/span>            2<\/span>\n5<\/span>        Read all the tutorials.                           0<\/span>          2016<\/span>-06<\/span>-01<\/span>  null<\/span>            2<\/span>\n6<\/span>        Use<\/span> Try<\/span> It<\/span> page<\/span> to<\/span> practice<\/span> the<\/span> SQLite<\/span> commands<\/span>.  0          2016-06-01  null<\/span>            2\n7        Develop<\/span> a<\/span> simple<\/span> SQLite<\/span>-based<\/span> application<\/span>         0          2016-06-15  null<\/span>            2<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
The output indicates that the script has successfully inserted data into the tables.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use PreparedStatement<\/code> to execute an INSERT<\/code> statement to insert a new row into a table.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Since an error may occur when connecting to the database and inserting data, we wrap these statements in a try-catch<\/a> block.<\/p>\n\n\n\n
Third, define a function<\/a> that inserts a new row into the projects<\/code> table:<\/p>\n\n\n
Step 6. Optionally get the last inserted id if the table has an auto-increment<\/a> id:<\/p>\n\n\n
Step 2. Construct an INSERT<\/code> statement<\/a> for inserting data into a table.<\/p>\n\n\n\n