Simple CRUD in php with SQLite Database

Brief Introduction:

SQLite is a relational database management system which is contained in a C programming library.
SQLite is not a client–server database engine. It is embedded into the end program. SQLite engine is not a standalone process like other databases, you can link it statically or dynamically as per your requirement with your application. The SQLite accesses its storage files directly like “my_sqlite_db_file.db” and SQLite database format or extension is “.db”. A large number of programming language is supports like C, C++, JAVA, PHP, C#, Python, GO etc.

SQLite Commands:

DDL – Data Definition Language:

CREATE Creates new table or Database
ALTER Modifies existing table.
DROP Deletes existing table or database.

DML – Data Manipulation Language:

INSERT Creates a record
UPDATE Modifies records
DELETE Deletes records

DQL – Data Query Language:

SELECT Retrieves records from tables

NOTE: All the rows of SQLite database tables has UNIQUE ID as like primary ID that name is rowid. So, you can make unique id or primary id with auto increment as like you want otherwise you can use by default rowid.

Let’s Get Started

I am going to show a simple CRUD( Create, Read, Update, Delete ) program in PHP.

I am giving the project folder name “sqlit_crud” you can make as you like.

First Make sure your php sqlite3 extension is enable

sqlite3 enable

sqlite3 enable

And now making the Database Connection file “db_connection.php”

<?php
// Database name
$database_name = "my_sqlite.db";

// Database Connection
$db = new SQLite3($database_name);
?>

Database connection of SQLite is a simple object of SQLite3 Class. Interesting thing you no need to create the database file it will make automatically when you make an object of SQLite3 Class. So, now we made the Database Connection where $db is the handler of Object of SQLite Class.

Okay, now we need to make a table into the Database. I am making a table “students” with two fields name as string and email as string and by default rowid will be the Primary ID.

NOTE: No need to define the rowid into Query of Creating table.

Finally “db_connection.php”

<?php
// Database name
$database_name = "my_sqlite.db";

// Database Connection
$db = new SQLite3($database_name);

// Create Table "students" into Database if not exists 
$query = "CREATE TABLE IF NOT EXISTS students (name STRING, email STRING)";
$db->exec($query);
?>

Now we need a form to insert data into table so, I am making a page “insert.php” and form in this page with post method and self action.

<!DOCTYPE html>
<html>
<head>
	<title>Insert Data</title>
</head>
<body>
	<div style="width: 500px; margin: 20px auto;">

		<!-- showing the message here-->
		<div><?php echo $message;?></div>

		<table width="100%" cellpadding="5" cellspacing="1" border="1">
			<form action="insert.php" method="post">
			<tr>
				<td>Name:</td>
				<td><input name="name" type="text"></td>
			</tr>
			<tr>
				<td>Email:</td>
				<td><input name="email" type="text"></td>
			</tr>
			<tr>
				<td><a href="list.php">See Data</a></td>
				<td><input name="submit_data" type="submit" value="Insert Data"></td>
			</tr>
			</form>
		</table>
	</div>
</body>
</html>

I am checking the form is submitted or not if submitted then inserting the data into students table and setting the success or error message according to query status.

Finally “insert.php”

<?php
$message = "";
if( isset($_POST['submit_data']) ){
	// Includs database connection
	include "db_connect.php";

	// Gets the data from post
	$name = $_POST['name'];
	$email = $_POST['email'];

	// Makes query with post data
	$query = "INSERT INTO students (name, email) VALUES ('$name', '$email')";
	
	// Executes the query
	// If data inserted then set success message otherwise set error message
	if( $db->exec($query) ){
		$message = "Data inserted successfully.";
	}else{
		$message = "Sorry, Data is not inserted.";
	}
}
?>
<!DOCTYPE html>
<html>
<head>
	<title>Insert Data</title>
</head>
<body>
	<div style="width: 500px; margin: 20px auto;">

		<!-- showing the message here-->
		<div><?php echo $message;?></div>

		<table width="100%" cellpadding="5" cellspacing="1" border="1">
			<form action="insert.php" method="post">
			<tr>
				<td>Name:</td>
				<td><input name="name" type="text"></td>
			</tr>
			<tr>
				<td>Email:</td>
				<td><input name="email" type="text"></td>
			</tr>
			<tr>
				<td><a href="list.php">See Data</a></td>
				<td><input name="submit_data" type="submit" value="Insert Data"></td>
			</tr>
			</form>
		</table>
	</div>
</body>
</html>
insert form

insert form

Okay, now I am going to make the list page “list.php” where all the records will show.

<?php

// Includs database connection
include "db_connect.php";

// Makes query with rowid
$query = "SELECT rowid, * FROM students";

// Run the query and set query result in $result
// Here $db comes from "db_connection.php"
$result = $db->query($query);

?>
<!DOCTYPE html>
<html>
<head>
	<title>Data List</title>
</head>
<body>
	<div style="width: 500px; margin: 20px auto;">
		<a href="insert.php">Add New</a>
		<table width="100%" cellpadding="5" cellspacing="1" border="1">
			<tr>
				<td>Name</td>
				<td>Email</td>
				<td>Action</td>
			</tr>
			<?php while($row = $result->fetchArray()) {?>
			<tr>
				<td><?php echo $row['name'];?></td>
				<td><?php echo $row['email'];?></td>
				<td>
					<a href="update.php?id=<?php echo $row['rowid'];?>">Edit</a> | 
					<a href="delete.php?id=<?php echo $row['rowid'];?>"  confirm('Are you sure?');">Delete</a>
				</td>
			</tr>
			<?php } ?>
		</table>
	</div>
</body>
</html>
list

list

Now I am going to show how to UPDATE the row data in “update.php”.

<?php
$message = ""; // initial message 

// Includs database connection
include "db_connect.php";

// Updating the table row with submited data according to rowid once form is submited 
if( isset($_POST['submit_data']) ){

	// Gets the data from post
	$id = $_POST['id'];
	$name = $_POST['name'];
	$email = $_POST['email'];

	// Makes query with post data
	$query = "UPDATE students set name='$name', email='$email' WHERE rowid=$id";
	
	// Executes the query
	// If data inserted then set success message otherwise set error message
	// Here $db comes from "db_connection.php"
	if( $db->exec($query) ){
		$message = "Data is updated successfully.";
	}else{
		$message = "Sorry, Data is not updated.";
	}
}

$id = $_GET['id']; // rowid from url
// Prepar the query to get the row data with rowid
$query = "SELECT rowid, * FROM students WHERE rowid=$id";
$result = $db->query($query);
$data = $result->fetchArray(); // set the row in $data
?>

<!DOCTYPE html>
<html>
<head>
	<title>Update Data</title>
</head>
<body>
	<div style="width: 500px; margin: 20px auto;">

		<!-- showing the message here-->
		<div><?php echo $message;?></div>

		<table width="100%" cellpadding="5" cellspacing="1" border="1">
			<form action="" method="post">
			<input type="hidden" name="id" value="<?php echo $id;?>">
			<tr>
				<td>Name:</td>
				<td><input name="name" type="text" value="<?php echo $data['name'];?>"></td>
			</tr>
			<tr>
				<td>Email:</td>
				<td><input name="email" type="text" value="<?php echo $data['email'];?>"></td>
			</tr>
			<tr>
				<td><a href="list.php">Back</a></td>
				<td><input name="submit_data" type="submit" value="Update Data"></td>
			</tr>
			</form>
		</table>
	</div>
</body>
</html>
update form

update form

Now going to show how to delete record from table according to rowid in “delete.php”.

<?php

// Includs database connection
include "db_connect.php";

$id = $_GET['id']; // rowid from url

// Prepar the deleting query according to rowid
$query = "DELETE FROM students WHERE rowid=$id";

// Run the query to delete record
if( $db->query($query) ){
	$message = "Record is deleted successfully.";
}else {
	$message = "Sorry, Record is not deleted.";
}

echo $message;
?>
<a href="list.php">Back to List</a>
deleting confirmation

deleting confirmation

Conclusion:

It was a simple CRUD in php with SQLite Database. If you want you can download the source code from Download Source Code or from here this one Class based https://github.com/sinha2773/crud_sqlitedb

I am Bakul Sinha Full Stack Software Developer. Currently working as a Senior Software Engineer at Byteshake Limited (UK Based Company). I have been working on Web Application Development, Website Design and Development last 5 years and Android Apps Development last 1 year.
3 comments on “Simple CRUD in php with SQLite Database
    • Well, If you download the source code then you will see a SQLite database “my_sqlite.db” (this is actually a file in the downloaded folder) and after saving the data are stored in the SQLite(my_sqlite.db) database.
      let me know if you have any confusion yet.

      Thanks
      Bakul Sinha

Leave a Reply

Your email address will not be published. Required fields are marked *