crud operations in php using mysqli : Create, Read, Update, Delete (CRUD) in PHP using mysqli Step by step Example.
crud operations in php using mysqli
PHP CRUD Operations with MySQLi Extension – CRUD is an acronym for Create, Read, Update, and Delete. CRUD operations are basic data manipulation for database.
Create a simple CRUD(Create, Read, Update, and Delete) application using PHP MySQLi with MySQL database.
files structure
create crud operations in php using mysqli files structure.
first_project_in_php/ ├── index.php ├── memberCrud.php ├── memberAction.php ├── DB.class.php ├── bootstrap/ │ └── bootstrap.min.css ├── css/ │ └── style.css └── images/
In this Basic OOP PHP CRUD Operation using PHP/MySQLi with Bootstrap tutorial we will learn about php from Scratch.
Create Database Table
First, we will see the Database Part.
SQL creates a members table
CREATE TABLE `members` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`email_address` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`mobile` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`created` datetime NOT NULL,
`updated_at` datetime NOT NULL,
`confirms` enum('1','0') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1' COMMENT '1=Active, 0=Inactive',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Don’t Miss : CRUD operations in PHP with Bootstrap
Database Class
DB.class.php
db)){
$conn = new mysqli($this->dbHost, $this->dbUsername, $this->dbPassword, $this->dbName);
if($conn->connect_error){
die("Failed to connect with MySQL: " . $conn->connect_error);
}else{
$this->db = $conn;
}
}
}
public function getRows($table, $controls = array()){
$sql = 'SELECT ';
$sql .= array_key_exists("select",$controls)?$controls['select']:'*';
$sql .= ' FROM '.$table;
if(array_key_exists("where",$controls)){
$sql .= ' WHERE ';
$i = 0;
foreach($controls['where'] as $key => $value){
$pre = ($i > 0)?' AND ':'';
$sql .= $pre.$key." = '".$value."'";
$i++;
}
}
if(array_key_exists("order_by",$controls)){
$sql .= ' ORDER BY '.$controls['order_by'];
}else{
$sql .= ' ORDER BY id DESC ';
}
if(array_key_exists("start",$controls) && array_key_exists("limit",$controls)){
$sql .= ' LIMIT '.$controls['start'].','.$controls['limit'];
}elseif(!array_key_exists("start",$controls) && array_key_exists("limit",$controls)){
$sql .= ' LIMIT '.$controls['limit'];
}
$result = $this->db->query($sql);
if(array_key_exists("return_type",$controls) && $controls['return_type'] != 'all'){
switch($controls['return_type']){
case 'count':
$data = $result->num_rows;
break;
case 'single':
$data = $result->fetch_assoc();
break;
default:
$data = '';
}
}else{
if($result->num_rows > 0){
while($row = $result->fetch_assoc()){
$data[] = $row;
}
}
}
return !empty($data)?$data:false;
}
public function insert($table, $data){
if(!empty($data) && is_array($data)){
$columns = '';
$values = '';
$i = 0;
if(!array_key_exists('created',$data)){
$data['created'] = date("Y-m-d H:i:s");
}
if(!array_key_exists('updated_at',$data)){
$data['updated_at'] = date("Y-m-d H:i:s");
}
foreach($data as $key=>$val){
$pre = ($i > 0)?', ':'';
$columns .= $pre.$key;
$values .= $pre."'".$this->db->real_escape_string($val)."'";
$i++;
}
$query = "INSERT INTO ".$table." (".$columns.") VALUES (".$values.")";
$insert = $this->db->query($query);
return $insert?$this->db->insert_id:false;
}else{
return false;
}
}
public function update($table, $data, $controls){
if(!empty($data) && is_array($data)){
$colvalSet = '';
$whereSql = '';
$i = 0;
if(!array_key_exists('updated_at',$data)){
$data['updated_at'] = date("Y-m-d H:i:s");
}
foreach($data as $key=>$val){
$pre = ($i > 0)?', ':'';
$colvalSet .= $pre.$key."='".$this->db->real_escape_string($val)."'";
$i++;
}
if(!empty($controls)&& is_array($controls)){
$whereSql .= ' WHERE ';
$i = 0;
foreach($controls as $key => $value){
$pre = ($i > 0)?' AND ':'';
$whereSql .= $pre.$key." = '".$value."'";
$i++;
}
}
$query = "UPDATE ".$table." SET ".$colvalSet.$whereSql;
$update = $this->db->query($query);
return $update?$this->db->affected_rows:false;
}else{
return false;
}
}
public function delete($table, $controls){
$whereSql = '';
if(!empty($controls)&& is_array($controls)){
$whereSql .= ' WHERE ';
$i = 0;
foreach($controls as $key => $value){
$pre = ($i > 0)?' AND ':'';
$whereSql .= $pre.$key." = '".$value."'";
$i++;
}
}
$query = "DELETE FROM ".$table.$whereSql;
$delete = $this->db->query($query);
return $delete?true:false;
}
}
CRUD Operations
memberAction.php
Please enter your name.';
}
if(empty($email_address) || !filter_var($email_address, FILTER_VALIDATE_EMAIL)){
$errorMsg .= 'Please enter a valid email_address.
';
}
if(empty($mobile) || !preg_match("/^[-+0-9]{6,20}$/", $mobile)){
$errorMsg .= 'Please enter a valid mobile number.
';
}
$memberDetails = array(
'name' => $name,
'email_address' => $email_address,
'mobile' => $mobile
);
$membersInfo['memberDetails'] = $memberDetails;
if(empty($errorMsg)){
if(!empty($_POST['id'])){
$condition = array('id' => $_POST['id']);
$update = $db->update($tblName, $memberDetails, $condition);
if($update){
$membersInfo['confirms']['type'] = 'success';
$membersInfo['confirms']['msg'] = 'Member data has been updated successfully.';
unset($membersInfo['memberDetails']);
}else{
$membersInfo['confirms']['type'] = 'error';
$membersInfo['confirms']['msg'] = 'Some problem occurred, please try again.';
$redirectURL = 'memberCrud.php';
}
}else{
$insert = $db->insert($tblName, $memberDetails);
if($insert){
$membersInfo['confirms']['type'] = 'success';
$membersInfo['confirms']['msg'] = 'Member data has been added successfully.';
unset($membersInfo['memberDetails']);
}else{
$membersInfo['confirms']['type'] = 'error';
$membersInfo['confirms']['msg'] = 'Some problem occurred, please try again.';
$redirectURL = 'memberCrud.php';
}
}
}else{
$membersInfo['confirms']['type'] = 'error';
$membersInfo['confirms']['msg'] = 'Please fill all the mandatory fields.
'.$errorMsg;
$redirectURL = 'memberCrud.php';
}
$_SESSION['membersInfo'] = $membersInfo;
}elseif(($_REQUEST['action_type'] == 'delete') && !empty($_GET['id'])){
$condition = array('id' => $_GET['id']);
$delete = $db->delete($tblName, $condition);
if($delete){
$membersInfo['confirms']['type'] = 'success';
$membersInfo['confirms']['msg'] = 'Member data has been deleted successfully.';
}else{
$membersInfo['confirms']['type'] = 'error';
$membersInfo['confirms']['msg'] = 'Some problem occurred, please try again.';
}
$_SESSION['membersInfo'] = $membersInfo;
}
header("Location:".$redirectURL);
exit();
?>
Bootstrap Library
Read & Delete Records
index.php
getRows('members');
if(!empty($membersInfo['confirms']['msg'])){
$confirmsMsg = $membersInfo['confirms']['msg'];
$confirmsMsgType = $membersInfo['confirms']['type'];
unset($_SESSION['membersInfo']['confirms']);
}
?>
Create & Update Records
memberCrud.php
$_GET['id'],
);
$controls['return_type'] = 'single';
$memberDetails = $db->getRows('members', $controls);
}
$memberDetails = !empty($membersInfo['memberDetails'])?$membersInfo['memberDetails']:$memberDetails;
unset($_SESSION['membersInfo']['memberDetails']);
$actionLabel = !empty($_GET['id'])?'Edit':'Add';
if(!empty($membersInfo['confirms']['msg'])){
$confirmsMsg = $membersInfo['confirms']['msg'];
$confirmsMsgType = $membersInfo['confirms']['type'];
unset($_SESSION['membersInfo']['confirms']);
}
?>
Member
I hope you get an idea about crud operations in php using mysqli.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.