Simple CRUD Operations in PHP OOPs and MySQL

In this tutorial, we will create Simple Simple CRUD Operations in PHP OOPs and MySQL database.

if you are a beginner then this tutorial for you. but you should have basic knowledge of PHP and HTML, CSS.

We will start it step by step.

Create a database:

First, we will create a database table below is the SQL code to create the user’s table.

--
-- Table structure for table `users`
--
CREATE TABLE `users` (
  `id` int(10) NOT NULL,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `image` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `users`
  MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;
COMMIT;

--
-- Dumping data for table `users`
--
INSERT INTO `users` (`id`, `name`, `email`, `image`) VALUES
(9, 'monika', 'monika@yahoo.com', '1591176842.jpg'),
(10, 'priyanka', 'priyanka1010@gmail.com', '1591176797.jpg'),
(11, 'Bhuvan', 'bhuvan2020@gmail.com', '1591176756.jpg'),
(12, 'Arun', 'arun@gmail.com', '1591176719.jpg');

In the above code, we have created table users with id, name, email, image columns where id is an auto-incremented primary key. After that dumped some values into the table.

Now, we can start coding, on a text editor I am using Sublime Text for a text editor.

Create index.php file:

In this file, we will use bootstrap for UI and include the necessary files for it. then create a form to create a user and a table to show details of all users into it.

We are using basic bootstrap syntax from Boostrap official website

Here is complete code if an index file:

<?php 
include 'functions.php';
?>
<!doctype html>
<html lang="en">
  <head> 
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no"> 
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
    <title>Simple CRUD Operations in PHP OOPs and MySQL</title>
  </head>
  <body>

<div class="container-fluid">
    <h2 class="text-center">Simple CRUD Operations in PHP OOPs and MySQL</h2>
    <div class="w-100 card my-3 shadow">
      <div class="card-body">
        <h5 class="card-title">Create User </h5>
         <form action="" method="post" enctype="multipart/form-data">
          <div class="row">
            <div class="form-group col-md-6">
              <label for="name">Name</label>
              <input type="text" class="form-control" id="name" name="name" required>
            </div>
            <div class="form-group col-md-6">
              <label for="email">Email address</label>
              <input type="email" class="form-control" id="email" name="email" required>
            </div>
          </div>
          
          <div class="form-group">
            <label for="image">User image</label>
            <input type="file" class="form-control-file" id="image" name="user_image" required>
          </div>
          <button type="submit" class="btn btn-primary" name="create">Submit</button>
        </form>
      </div>
    </div>
    
    <div class="w-100 card my-3 shadow">
      <div class="card-body">
        <h5 class="card-title">User Table </h5>
          <table class="table table-striped">
            <thead>
              <tr>
                <th >#</th>
                <th >Name</th>
                <th >Email</th>
                <th >Image</th>
                <th >Action</th>
              </tr>
            </thead>
            <tbody> 
               <?php 
                 $result = $usr->get_all_users(); 
              while ($row = mysqli_fetch_array($result)){
              ?>
                <tr>
                  <th><?= $row['id'] ?></th>
                  <td><?= $row['name'] ?></td>
                  <td><?= $row['email'] ?></td>
                  <td> <img src="images/<?= $row['image'] ?>" width="70px">  </td>
                  <td>
                    <a href="index.php?edit=<?= $row['id'] ?>" class="btn btn-info">Edit</a>
                    <a href="index.php?delete=<?= $row['id'] ?>" class="btn btn-danger">Delete</a>
                  </td>
                </tr>
              <?php } ?>
            </tbody>
          </table>
      </div>
    </div>
  
</div>

<?php if (isset($_GET['edit'])): 
  $id = (is_numeric($_GET['edit'])) ? $_GET['edit'] : 0;
  $user = mysqli_fetch_array($usr->get_user_by_id($id));
  if (!is_null($user)) {
?>
<div class="modal fade" id="model_form" tabindex="-1" role="dialog" aria-labelledby="exampleModalLabel" aria-hidden="true">
  <div class="modal-dialog modal-dialog-centered" role="document">
    <div class="modal-content">
      <div class="modal-header border-bottom-0">
        <h5 class="modal-title" id="exampleModalLabel">Edit</h5>
        <button type="button" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <div class="modal-body">
        
        <div class="from_edit">
         <form action="" method="post" enctype="multipart/form-data">
          <div class="form-group">
            <label for="name">Name</label>
            <input type="text" class="form-control" id="name" name="name" value="<?= $user['name']; ?>" required>
            <input type="hidden" name="user_id" value="<?php if (isset($_GET['edit'])){ echo $_GET['edit']; } ?>">
          </div>
          <div class="form-group">
            <label for="email">Email address</label>
            <input type="email" class="form-control" id="email" name="email" value="<?= $user['email']; ?>" required>
          </div>
          <div class="form-group">
            <label for="image">User image</label>
            <input type="file" class="form-control-file" id="image" name="user_image"  >
            <img src="images/<?= $user['image']; ?>" alt="" width="100px">
          </div>
          <button type="submit" class="btn btn-primary" name="update">Submit</button>
        </form>
       </div>
      </div>
       
    </div>
  </div>
</div> 
<?php  } endif ?> 
    <script src="https://code.jquery.com/jquery-3.5.1.slim.min.js" ></script>
    <script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js" ></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js"></script>
    <script>
      $(document).ready(function () {
       <?php 
       if (isset($_GET['edit'])) {
            echo "$('#model_form').modal('show'); ";
        }
        ?>
        //
      });
    </script>
  </body>
</html>

In the above code, we have included functions.php file which contained conditional statements on which application will perform tasks. Then We have created user form after it we have all users table with action column which has two actions edit and delete.

We are fetching all user’s data form the database and looping it to show it into the table.

Delete operation is performed using GET parameter where we are passing id of user in delete variable to delete it.

Create functions.php file:

In this file, we will create object of User class and this object is used to perform CRUD operations.

funtions.php file also contains conditional statements for performing different tasks.

Here is the code of functions.php

<?php  
include 'User.php';  
$usr = new User();

if (isset($_POST['create'])) {
	
	$name = $_POST["name"];
	$email = $_POST["email"];
	$image = $usr->upload_file($_FILES["user_image"]);  
	$query ="INSERT INTO users (name, email, image) VALUES ('$name','$email','$image')";  
	 if($usr->execute_query($query)){
	echo "<script>alert('Data Inserted'); window.open('index.php','_self')</script>";
	}else{
	echo "<script>alert('Unable to insert'); window.open('index.php','_self')</script>";
	}
}
if (isset($_POST['update'])) {
	
	$name = $_POST["name"];
	$email = $_POST["email"];
	$id = $_POST["user_id"]; 

	if (empty($_FILES['user_image']['name'])) {
		$query ="UPDATE users set name='$name',email='$email' where id='$id'"; 
	}else{
		$image = $usr->upload_file($_FILES["user_image"]);  
		$query ="UPDATE users set name='$name',email='$email', image='$image' where id='$id'"; 
	}
	if($usr->execute_query($query)){
	echo "<script>alert('Data Updated'); window.open('index.php','_self')</script>";
	}else{
	echo "<script>alert('Unable to update'); window.open('index.php','_self')</script>";
	}
	 
}

if (isset($_GET['delete'])) {
	  $id = (is_numeric($_GET['delete'])) ? $_GET['delete'] : 0;
	$query ="DELETE from users where id='$id'"; 
	if($usr->execute_query($query)){
	echo "<script>alert('Data deleted'); window.open('index.php','_self')</script>";
	}else{
	echo "<script>alert('Unable to delete'); window.open('index.php','_self')</script>";
	}
}

?>   

Here we are getting form values and passing to Users object for executing queries.

Create User.php file:

In the constructor function, we are creating a database connection.

upload_file() is a function to upload image file into images directory to store the image file so create images folder.

<?php 
class User
{
   public $db = null;
    public function __construct()
    {
    	 $this->db = mysqli_connect('localhost', 'root', 'password', 'your_db_name'); 
    } 
    public function execute_query($query)
    {
    	return $this->db->query($query);
    }
    public function get_all_users()
    {
    	return $this->db->query("Select * from users ORDER BY id DESC");
    }
    public function get_user_by_id($id)
    {
    	return $this->db->query("Select * from users where id='$id'");
    }
    function upload_file($file) {  
	   if(isset($file))  
	   {  
	        $extension = explode('.', $file["name"]);  
	        $new_name = time() . '.' . $extension[1];  
	        $destination = './images/' . $new_name;  
	        move_uploaded_file($file['tmp_name'], $destination);  
	        return $new_name;  
	   }  
	}  
}
?>

Leave a Reply

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