CRUD Operation using PHP and SQLite3 Database Tutorial
This tutorial tackles how to CRUD(create, read, update and delete) which is also the same as shown, add, edit and delete on SQLite3 using PHP. SQLite as per site description is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.
In this tutorial, I will be using XAMPP as my local server to run my PHP Script. The output user-interface that I will create has only a simple design because we will only focus to the main idea of this tutorial which is creating an CRUD Operation using PHP and SQLite Database.
So let's get started...
Enabling SQLite3
First, we are going to enable SQLite 3 in our PHP.
- Open localhost server folder ex XAMPP, etc and find php.ini. For XAMPP, it is located in xampp/php/php.ini.
- Open php.ini and enable sqlite3 by removing the semicolon of the ff line.
- Save changes and Restart Server, ex XAMPP, etc.
Creating our SQLite Database and Connection
After enabling SQLite3
, we are going to create a new sqlite database as well as a table.
Create a new file, name it as dbconfig.php
and paste the code below.
- <?php
- //Create a new SQLite3 Database
- $db = new SQLite3('members.db');
- //Create a new table to our database
- $query = "CREATE TABLE IF NOT EXISTS members (firstname STRING, lastname STRING, address STRING)";
- ?>
Displaying our Data in a Table
Next step is to display the data from our created database and table. Create a new file, name it as index.php
and paste the codes below.
- <!DOCTYPE html>
- <html>
- <head>
- <meta charset="utf-8">
- <title>CRUD Operation on SQLite3 Database using PHP</title>
- </head>
- <body>
- <a href="add.php">Add</a>
- <table border="1">
- <thead>
- <th>ID</th>
- <th>Firstname</th>
- <th>Lastname</th>
- <th>Address</th>
- <th>Action</th>
- </thead>
- <tbody>
- <?php
- //include our connection
- include 'dbconfig.php';
- //query from the table that we create
- $sql = "SELECT rowid, * FROM members";
- $query = $db->query($sql);
- while($row = $query->fetchArray()){
- echo "
- <tr>
- <td>".$row['rowid']."</td>
- <td>".$row['firstname']."</td>
- <td>".$row['lastname']."</td>
- <td>".$row['address']."</td>
- <td>
- <a href='edit.php?id=".$row['rowid']."'>Edit</a>
- <a href='delete.php?id=".$row['rowid']."'>Delete</a>
- </td>
- </tr>
- ";
- }
- ?>
- </tbody>
- </table>
- </body>
- </html>
Creating our Add Form and Script
Next, we create our add form as well our add script. Create new file, name it as add.php
and paste the codes below.
- <!DOCTYPE html>
- <html>
- <head>
- <meta charset="utf-8">
- <title>CRUD Operation on SQLite3 Database using PHP</title>
- </head>
- <body>
- <form method="POST">
- <a href="index.php">Back</a>
- <p>
- <label for="firstname">Firstname:</label>
- <input type="text" id="firstname" name="firstname">
- </p>
- <p>
- <label for="lastname">Lastname:</label>
- <input type="text" id="lastname" name="lastname">
- </p>
- <p>
- <label for="address">Address:</label>
- <input type="text" id="address" name="address">
- </p>
- <input type="submit" name="save" value="Save">
- </form>
- <?php
- //include our connection
- include 'dbconfig.php';
- //insert query
- $sql = "INSERT INTO members (firstname, lastname, address) VALUES ('".$_POST['firstname']."', '".$_POST['lastname']."', '".$_POST['address']."')";
- }
- ?>
- </body>
- </html>
Creating our Edit Form and Edit Script
Next, we create our edit form and our edit script. Create a new file, name it as edit.php and paste the codes below.
- <?php
- //include our connection
- include 'dbconfig.php';
- //get the row of selected id
- $sql = "SELECT rowid, * FROM members WHERE rowid = '".$_GET['id']."'";
- $query = $db->query($sql);
- $row = $query->fetchArray();
- ?>
- <!DOCTYPE html>
- <html>
- <head>
- <meta charset="utf-8">
- <title>CRUD Operation on SQLite3 Database using PHP</title>
- </head>
- <body>
- <form method="POST">
- <a href="index.php">Back</a>
- <p>
- <label for="firstname">Firstname:</label>
- <input type="text" id="firstname" name="firstname" value="<?php echo $row['firstname']; ?>">
- </p>
- <p>
- <label for="lastname">Lastname:</label>
- <input type="text" id="lastname" name="lastname" value="<?php echo $row['lastname']; ?>">
- </p>
- <p>
- <label for="address">Address:</label>
- <input type="text" id="address" name="address" value="<?php echo $row['address']; ?>">
- </p>
- <input type="submit" name="save" value="Save">
- </form>
- <?php
- $firstname = $_POST['firstname'];
- $lastname = $_POST['lastname'];
- $address = $_POST['address'];
- //update our table
- $sql = "UPDATE members SET firstname = '$firstname', lastname = '$lastname', address = '$address' WHERE rowid = '".$_GET['id']."'";
- }
- ?>
- </body>
- </html>
Creating our Delete Script
Lastly, we create our delete script. Create a new file, name it as delete.php
and paste the codes below.
- <?php
- //include our connection
- include 'dbconfig.php';
- //delete the row of selected id
- $sql = "DELETE FROM members WHERE rowid = '".$_GET['id']."'";
- $db->query($sql);
- ?>
DEMO
That's it! That ends this tutorial. You can also download the working source code I created for this tutorial. Just click the download button below. I hope this tutorial will help you with what you are looking for.
Explore more on this website for more Tutorials and Free Source Code.
Happy Coding :)Comments
Add new comment
- Add new comment
- 6384 views