CRUD Operation using PHP and SQLite3 Database Tutorial

Submitted by nurhodelta_17 on Friday, August 13, 2021 - 15:46.

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.

  1. Open localhost server folder ex XAMPP, etc and find php.ini. For XAMPP, it is located in xampp/php/php.ini.
  2. Open php.ini and enable sqlite3 by removing the semicolon of the ff line.
    enable sqlite extension
  3. 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.

  1. <?php
  2. //Create a new SQLite3 Database
  3. $db = new SQLite3('members.db');
  4.  
  5. //Create a new table to our database 
  6. $query = "CREATE TABLE IF NOT EXISTS members (firstname STRING, lastname STRING, address STRING)";
  7. $db->exec($query);
  8.  
  9. ?>

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.

  1. <!DOCTYPE html>
  2. <html>
  3. <head>
  4. 	<meta charset="utf-8">
  5. 	<title>CRUD Operation on SQLite3 Database using PHP</title>
  6. </head>
  7. <body>
  8. <a href="add.php">Add</a>
  9. <table border="1">
  10. 	<thead>
  11. 		<th>ID</th>
  12. 		<th>Firstname</th>
  13. 		<th>Lastname</th>
  14. 		<th>Address</th>
  15. 		<th>Action</th>
  16. 	</thead>
  17. 	<tbody>
  18. 		<?php
  19. 			//include our connection
  20. 			include 'dbconfig.php';
  21.  
  22. 			//query from the table that we create
  23. 			$sql = "SELECT rowid, * FROM members";
  24. 			$query = $db->query($sql);
  25.  
  26. 			while($row = $query->fetchArray()){
  27. 				echo "
  28. 					<tr>
  29. 						<td>".$row['rowid']."</td>
  30. 						<td>".$row['firstname']."</td>
  31. 						<td>".$row['lastname']."</td>
  32. 						<td>".$row['address']."</td>
  33. 						<td>
  34. 							<a href='edit.php?id=".$row['rowid']."'>Edit</a>
  35. 							<a href='delete.php?id=".$row['rowid']."'>Delete</a>
  36. 						</td>
  37. 					</tr>
  38. 				";
  39. 			}
  40. 		?>
  41. 	</tbody>
  42. </table>
  43. </body>
  44. </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.

  1. <!DOCTYPE html>
  2. <html>
  3. <head>
  4. 	<meta charset="utf-8">
  5. 	<title>CRUD Operation on SQLite3 Database using PHP</title>
  6. </head>
  7. <body>
  8. <form method="POST">
  9. 	<a href="index.php">Back</a>
  10. 	<p>
  11. 		<label for="firstname">Firstname:</label>
  12. 		<input type="text" id="firstname" name="firstname">
  13. 	</p>
  14. 	<p>
  15. 		<label for="lastname">Lastname:</label>
  16. 		<input type="text" id="lastname" name="lastname">
  17. 	</p>
  18. 	<p>
  19. 		<label for="address">Address:</label>
  20. 		<input type="text" id="address" name="address">
  21. 	</p>
  22. 	<input type="submit" name="save" value="Save">
  23. </form>
  24. <?php
  25. 	if(isset($_POST['save'])){
  26. 		//include our connection
  27. 		include 'dbconfig.php';
  28.  
  29. 		//insert query
  30. 		$sql = "INSERT INTO members (firstname, lastname, address) VALUES ('".$_POST['firstname']."', '".$_POST['lastname']."', '".$_POST['address']."')";
  31. 		$db->exec($sql);
  32.  
  33. 		header('location: index.php');
  34.  
  35. 	}
  36. ?>
  37. </body>
  38. </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.

  1. <?php
  2. 	//include our connection
  3. 	include 'dbconfig.php';
  4.  
  5. 	//get the row of selected id
  6. 	$sql = "SELECT rowid, * FROM members WHERE rowid = '".$_GET['id']."'";
  7. 	$query = $db->query($sql);
  8. 	$row = $query->fetchArray();
  9.  
  10. ?>
  11. <!DOCTYPE html>
  12. <html>
  13. <head>
  14. 	<meta charset="utf-8">
  15. 	<title>CRUD Operation on SQLite3 Database using PHP</title>
  16. </head>
  17. <body>
  18. <form method="POST">
  19. 	<a href="index.php">Back</a>
  20. 	<p>
  21. 		<label for="firstname">Firstname:</label>
  22. 		<input type="text" id="firstname" name="firstname" value="<?php echo $row['firstname']; ?>">
  23. 	</p>
  24. 	<p>
  25. 		<label for="lastname">Lastname:</label>
  26. 		<input type="text" id="lastname" name="lastname" value="<?php echo $row['lastname']; ?>">
  27. 	</p>
  28. 	<p>
  29. 		<label for="address">Address:</label>
  30. 		<input type="text" id="address" name="address" value="<?php echo $row['address']; ?>">
  31. 	</p>
  32. 	<input type="submit" name="save" value="Save">
  33. </form>
  34. <?php
  35. 	if(isset($_POST['save'])){
  36. 		$firstname = $_POST['firstname'];
  37. 		$lastname = $_POST['lastname'];
  38. 		$address = $_POST['address'];
  39.  
  40. 		//update our table
  41. 		$sql = "UPDATE members SET firstname = '$firstname', lastname = '$lastname', address = '$address' WHERE rowid = '".$_GET['id']."'";
  42. 		$db->exec($sql);
  43.  
  44. 		header('location: index.php');
  45. 	}
  46. ?>
  47. </body>
  48. </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.

  1. <?php
  2. 	//include our connection
  3. 	include 'dbconfig.php';
  4.  
  5. 	//delete the row of selected id
  6. 	$sql = "DELETE FROM members WHERE rowid = '".$_GET['id']."'";
  7. 	$db->query($sql);
  8.  
  9. 	header('location: index.php');
  10. ?>

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 :)

Tags

Comments

Submitted byoleteacheron Tue, 04/10/2018 - 18:43

Thank you for your tutorial on SQLite! You make it simple to understand. In future if you can (and using this tutorial as base), encrypting / decrypting the database using openssl would be great. I see some using mcrypt but that no longer works in PHP 7.2+ since removed after PHP 7.1. Thanks again.

Add new comment

About text formats