PHP Professional এবং Zend Certified PHP Engineer (ZCPE ) Course এ সীমিত সংখ্যক আসন বাকি আছে। আগ্রহীদেরকে অতিসত্বর মাসুদ আলম স্যার এর সাথে যোগাযোগ করার জন্য অনুরোধ করা যাচ্ছে। স্যার এর মোবাইল নম্বর : ০১৭২২ ৮১ ৭৫ ৯১

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_connect.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_connect.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_connect.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_connect.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 6 years.

26 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

    • Hi Amanul Islam, You have to enable ‘SQLite3’ from ‘php.ini’. Please see on top “Let’s Get Started” section you will see there in an image. “extension=php_sqlite3.dll” this line must be enabled (to be removed semicolon at the beginning of line) for the SQLite3 extension. Comment here if you are still in the problem.

      Thanks
      Bakul Sinha

    • Please check the SQLite3 extension from php.ini file if it is not enabled then enabled first then try to run the CRUD.

      Thanks

  1. Thank you very much for sharing this! Have been putting off learning sqlite and you made it very easy to grasp within minutes.

    Once question if I may: Do you know of simple way to protect (encrypt) the my_sqlite.db file?

    Not looking for commercial strength, just to keep casual user from changing the table contents or maybe seeing what it contains.

    Thanks again!

    • I found this:

      https://www.phpclasses.org/package/7077-PHP-Access-encrypted-SQLite-database-files.html

      Seems like it should work with your tutorial, but just can’t seem to get things working together.

      • Hi Terry,

        Thanks for your valuable comments. Yes, you are correct I just checked your reference link it should work together. I’ll get back to you with solution asap.

        Thanks
        Bakul Sinha

  2. I changed the email’s input type “input” to “textarea” and tried to store large text in email field, but an error occurred.
    I want to save a big article in database
    data couldn’t stored.
    how to fix it??

    • Well, it should work if you put textarea correctly like

      <textarea name="email"></textarea>

      Can you check your code the name attribute is correct or not. Please note if you would like to change the name attribute then you must change all query and database schema (table field name). if you need any further help let me know.

      Thanks
      Bakul Sinha

  3. I didn’t change name attribute.
    It works when i give few text input.
    but it can’t store a large article.
    bro i want to sent screenshot to you. please give me your email address.

  4. In the beginning of the tutorial it says that the name of the first file is db_connection.php. – I had to change this filename to db_connect.php for it to work.

    • You are correct. Thank you for your valuable comments. I have corrected the file name db_connection.php to db_connect.php

      Thanks
      Bakul Sinha

Leave a Reply

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

No announcement available or all announcement expired.