Tutorial: Basic Create, Update Delete (CRUD) Operations in PHP Using OOP and MYSQL Database.
Today i am going to be teaching how we can create basic CRUD operations in PHP using OOP, Object oriented Programming is an advance way of writing programs that follows a structured which all programmers can easily read and debug. So is recommended to use OOP in writing our programs. In our Previous Tutorials, we have taken our time to explain all the basics of OOP.
What Will I learn?
• You will learn how to create database schemas in MYSQL database using PHPMYADMIN database client
• You will learn how to connect to database using PDO connector
• You will also learn use to write SQL queries to insert, update and delete data in the database
• How to declare a class, Method and Objects
• How to Instantiate the object of a class
• How to call a method to perform an action
Requirements:
• Xampp Sever
• Text editor like Dreamweaver or NeatBeans IDE
• Basic Knowledge of OOP
Difficulty:
• Intermediate
Tutorial Contents: Simple Create, Update and Delete Operations (CRUD) are one of the most important operations every web Application must have. Here we will learn how we can insert a record into a database, update or edit the record and as well delete the record.
We will be using Object Oriented PHP for this Operation, because OOP is an advance method of programming that help us organised and structured our code in a way that every programmer all over the world can read and debug our code, unlike the procedural way of writing codes that did not follow any specific format.
Creating MYSQL database and table:
First we have to create our database and our users table inside the database. If you have your xampp server set up already and is running on port 80. Goto your browser and open the following URL http://localhost/phpmyadmin and execute below queries to create necessary database and tables. You can also do it manually by creating database and tables using phpmyadmin. Here we are creating database named crud and only one table named crud table to store staff information. The staff information have four records, name, profession ,organization and salary
Copy and paste the below querry into your phpmyadmin
CREATE TABLE IF NOT EXISTS crud (
ID int(11) NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL,
profession varchar(50) NOT NULL,
organization varchar(20) NOT NULL,
salary varchar(20) NOT NULL,
PRIMARY KEY (userID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=51 ;
After creating the database crud and table crud, phpmyadmin will look like this
Now, we have to start creating our files which will be in our project folder located at C:/xampp/htdocs/crud directory
dbconnect.php:
This is our database connection class that will return the database connection object which will be used for any database connection in our app. Here, I have used PDO (php data object) which is a more better than mysqli and it prevents sql injection and can be use in multiple database system
dbconnect.php
public function __construct() {
$this->conn=NULL;
}
public function dbConnection()
{
// $this->conn = null;
try
{
$this->conn = new PDO("mysql:host=" . $this->host . ";dbname=" . $this->db_name, $this->username, $this->password);
$this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $exception)
{
echo "Connection error: " . $exception->getMessage();
}
return $this->conn;
}
}
?>
The screenshot for the dbconnect.php
The above code is used to get the xampp server credentials and also to connect to the server . The file will be included whenever we want to connect to the server
class.crud.php:
This file will be used to write all our functions that will be used to querry the database to insert a record, update and delete a record. The class will have four methods namely: Showstaff(),insertData(), updateData(), and deleteData().
class.crud.php
require_once 'dbconnect.php';class crud
{
private $conn;
public function __construct()
{
$database = new Database();
$db = $database->dbConnection();
$this->conn = $db;
}
public function Showstaff()
{
try {
$stmt = $this->conn->prepare("SELECT * FROM crud order by ID DESC");
$stmt->execute();
return $stmt;
}
catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}
}// ends here
public function insertData($name,$profession,$organization,$salary)
{
try
{
$stmt = $this->conn->prepare("INSERT INTO crud(name,profession,organization,salary,created_at)
VALUES(:name,:profession, :organization,:salary,NOW())");
$stmt->bindparam(":name", $name);
$stmt->bindparam(":profession", $profession);
$stmt->bindparam(":organization", $organization);
$stmt->bindparam(":salary", $salary);
$stmt->execute();
return $stmt;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
}
public function deleteData($ID)//method that deletes post
{
$sql = "DELETE FROM crud WHERE ID='$ID'";
$this->conn->exec($sql);
if($this->conn)
{
return TRUE;
} else {
return FALSE;
}
}
public function updateData($name,$profession,$organization,$salary,$ID)
{
try
{
$stmt = $this->conn->prepare('UPDATE crud SET name=:name,profession =:profession,organization=:organization,salary=:salary,created_at=NOW() WHERE ID=:ID');
$stmt->bindparam(":ID", $ID);
$stmt->bindparam(":name", $name);
$stmt->bindparam(":profession", $profession);
$stmt->bindparam(":organization", $organization);
$stmt->bindparam(":salary", $salary);
$stmt->execute();
return $stmt;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
}
}
Screenshot for class.crud.php
view_addStaff.php:
This is a view file where the user of the app is expected to enter his input, the input will be sent to php using post method. I use Input validations to ensure that the user enter the expected data and also to ensure that empty strings are not submitted to our database. I have also use bootstraps library in the project so that we can have a better user interface.
v_addStaff.php
$obj= new crud();$error = false;
if ( isset($_POST['submit']) ) {
// clean user inputs to prevent sql injections
$name = trim($_POST['name']);
$profession = trim($_POST['job']);
$organization = trim($_POST['organization']);
$salary = trim($_POST['salary']);
// basic name validation
if (empty($name)) {
$error = true;
$nameError = "Please enter your full name.";
}
if (empty($profession)) {
$error = true;
$professionError= "Please enter your profession.";
}
if (empty($organization)) {
$error = true;
$organizationError = "Please Enter your organization.";
}
if ( empty($salary) ) {
$error = true;
$salaryError = "Please enter your salary";
}
if( !$error )
{
$crud = $obj->insertData($name, $profession, $organization, $salary);
if ($crud) {
$errTyp = "success";
$errMSG = "Record Successfully Added";
header("Location:index.php");
} else {
$errTyp = "danger";
$errMSG = "Something went wrong, try again later...";
}
}
}
?>
(html comment removed: Optional theme )
<div class="navbar-header">
<a class="navbar-brand" href="index.php" >Home </a>
<a class="navbar-brand" href="v_addStaff.php">Add Staff</a>
</div>
</div>
?>
<div class="form-group">
<div class="alert alert-<?php echo ($errTyp=="success") ? "success" : $errTyp; ?>">
<span class="glyphicon glyphicon-info-sign"></span> <?php echo $errMSG; ?>
</div>
</div>
<?php
}
?>
<table class="table table-bordered table-responsive">
<tr>
<td><label class="control-label">Name.</label></td>
<td><input class="form-control" type="text" name="name" placeholder="Enter Name" /></td>
<?php
if ( isset($nameError) ) { ?>
<span class="text-danger"><?php echo $nameError; ?></span>
<?php
}
?>
</tr>
<tr>
<td><label class="control-label">Profession.</label></td>
<td><input class="form-control" type="text" name="job" placeholder="Your Profession" /></td>
<?php
if ( isset($professionError) ) { ?>
<span class="text-danger"><?php echo $professionError; ?></span>
<?php
}
?>
</tr>
<tr>
<td><label class="control-label">Organization.</label></td>
<td><input class="form-control" type="text" name="organization" placeholder="Your Organization" /></td>
<?php
if ( isset($organizationError) ) { ?>
<span class="text-danger"><?php echo $organizationError; ?></span>
<?php
}
?>
</tr>
<tr>
<td><label class="control-label">Salary.</label></td>
<td><input class="form-control" type="text" name="salary" placeholder="Your Salary" /></td>
<?php
if ( isset($salaryError) ) { ?>
<span class="text-danger"><?php echo $salaryError; ?></span>
<?php
}
?>
</tr>
<tr>
<td colspan="2"><button type="submit" name="submit" class="btn btn-default">
<span class="glyphicon glyphicon-save"></span> save
</button>
</td>
</tr>
</table>
(html comment removed: Latest compiled and minified JavaScript )
Screenshots of the above code
index.php:
Index.php file is the landing page of the app, it is the page that will load when the app is launched, it will populate all the information we have in the database and there user can decide to edit or delete any record after confirmation
require_once 'class.crud.php';
$obj= new crud();
$showStaff= $obj->Showstaff();
?>
crud <div class="navbar-header">
<a class="navbar-brand" href="index.php" >Home </a>
<a class="navbar-brand" href="v_addStaff.php">Add Staff</a>
</div>
</div>
Staff Record
| Name | Profession | Organization | Salary | Action | Action |
|---|---|---|---|---|---|
(html comment removed: Latest compiled and minified JavaScript ) |
Edit Staff.php:
This is the edit staff record link, that if a user click, it will open a page populating all the existing staff record and they can now change the information to their desired. It will also get the user input using post and it will pass it to our updateData() method by calling the method
editStaff.php
require_once 'update_user_info.php';
$db = new update_user_info();
// json response array
$response = array("error" => FALSE);
if (isset($_POST['name']) && isset($_POST['email']) && isset($_POST['password']) && isset($_POST['gender']) && isset($_POST['age'])) {
// receiving the post params
$name = $_POST['name'];
$email = $_POST['email'];
$password = $_POST['password'];
$gender = $_POST['gender'];
$age = $_POST['age'];
// check if user is already existed with the same email
if ($db->CheckExistingUser($email)) {
// user already existed
$response["error"] = TRUE;
$response["error_msg"] = "User already existed with " . $email;
echo json_encode($response);
} else {
// create a new user
$user = $db->StoreUserInfo($name, $email, $password, $gender, $age);
if ($user) {
// user stored successfully
$response["error"] = FALSE;
$response["user"]["name"] = $user["name"];
$response["user"]["email"] = $user["email"];
$response["user"]["gender"] = $user["gender"];
$response["user"]["age"] = $user["age"];
echo json_encode($response);
} else {
// user failed to store
$response["error"] = TRUE;
$response["error_msg"] = "Unknown error occurred in registration!";
echo json_encode($response);
}
}
} else {
$response["error"] = TRUE;
$response["error_msg"] = "Required parameters (name, email, password, gender or age) is missing!";
echo json_encode($response);
}
?>
deleteStaff.php:
This is the delete Record file, A user click on delete from the index page and after confirmation, the deleteStaff.php will call our deleteData method to execute the query and the record is deleted from the database
deleteStaff.php
<?phprequire_once ("class.crud.php");
$obj = new crud();
if(isset($_GET['delete_id']))
{
$ID=$_GET['delete_id'];
if ($obj->deleteData($ID))
{
?>
<script>
alert('Record Successfully Deleted ...');
window.location.href='index.php';
</script>
<?php
}
}
So finally our App Basic Create, Update and Delete (CRUD) Operations in PHP Using OOP andMYSQL is completed.
Posted on Utopian.io - Rewarding Open Source Contributors
Your contribution cannot be approved because it does not follow the Utopian Rules.
Need help? Write a ticket on https://support.utopian.io.
Chat with us on Discord.
[utopian-moderator]
the problem of the structuring is not my fault. it is from utopian. I made the post from steemit and it was ok. i even checked busy and it was also ok. you need to reconsider