Surfe.be - passive income

PHP CRUD COMPLETE TUTORIAL WITH MYSQLi

Today we are going to show the procedure of PHP CRUD system with MySQLi. In this tutorial we will complete it. First before starting our tutorial we will know why will you use mysqli ?The mysqli extension, or as it is sometimes known, the MySQL improved extension, was developed to take advantage of new features found in MySQL systems versions 4.1.3 and newer. The mysqli extension is included with PHP versions 5 and later

The mysqli extension has a number of benefits, the key enhancements over the mysql extension being: Support for Prepared Statements . Support for Multiple Statements . Support for Transactions . Enhanced debugging capabilities . MySQLi uses the standard form of the SQL language. MySQLi can work with multiple languages such as C, C++, JAVA, PHP etc.

MySQLi has many advantages over MySQL. Some of these are given as follows −

  1. MySQLi supports both procedural interfaces and object oriented interfaces while MySQL supports only procedural interfaces.
  2. MySQLi supports stored procedure but MySQL does not.
  3. MySQLi supports prepared statements but MySQL does not.
  4. Transactions are supported through API in MySQLi but they ae handled hrough queries only in SQL.

PHP CRUD WITH MYSQLi

Before starting our project first you have to go to your localhost phpmyadmin to create our project Database whose name will be php_crud . Also create a table whose name will be tbl_user along with two filed , one is id and other is name

CONNECT DATABASE

To connect database first create a config.php file. Write those following code to this file 

define("DB_HOST", "localhost");
define("DB_USER", "root");
define("DB_PASS", "");
define("DB_NAME", "php_crud");

Then create a file name will be Database.php  asd write those following code to this file 

Class Database{
    public $host   = DB_HOST;
    public $user   = DB_USER;
    public $pass   = DB_PASS;
    public $dbname = DB_NAME;
    
    
    public $link;
    public $error;
    
    public function __construct(){
        $this->connectDB();
    }
    
    private function connectDB(){
    $this->link = new mysqli($this->host, $this->user, $this->pass, $this->dbname);
  
    if(!$this->link){
        $this->error ="Connection fail".$this->link->connect_error;
        return false;
    }
   }
        
    public function select($query){
        $result = $this->link->query($query) or die($this->link->error.__LINE__);
        if($result->num_rows > 0){
            return $result;
        } else {
            return false;
        }
    }       
    public function insert($query){
    $insert_row = $this->link->query($query) or die($this->link->error.__LINE__);
    if($insert_row){
        return $insert_row;
    } else {
        return false;
    }
  }  
    public function update($query){
    $update_row = $this->link->query($query) or die($this->link->error.__LINE__);
    if($update_row){
        return $update_row;
    } else {
        return false;
    }
  }  
   public function delete($query){
    $delete_row = $this->link->query($query) or die($this->link->error.__LINE__);
    if($delete_row){
        return $delete_row;
    } else {
        return false;
    }
  }
}

Then create a file Helper.php file and paste the following code

class Helper{
public function validation($data){
      $data = trim($data);
      $data = stripcslashes($data);
      $data = htmlspecialchars($data);
      return $data;
   }
}

READ DATA

First include Database.php file and create database object to access method which are created inside the Database.php file. Create index.php file and write following code. 

<?php
   include 'Helper.php';
   $db = new Database(); // Creating database object;
?>
<table class="table table-stripped">
		<th>Serial</th>
		<th>Name</th>
		<th>Action</th>

   <?php

        $sql = "select * from tbl_user";
        $data = $db->select($sql); // access select() method which is created inside database.php file
        if ($data) {
          $i = 0;
          while ( $output= $data->fetch_assoc()) {
            $i++;
     ?>
		<tr>
			<td><?php echo $i;?></td>
			<td><?php echo  $data['name'];?></td>
			<td>
			<a class="btn bnt-primary" href="edit.php?id=<?php echo  $data['id'];?>">Edit</a> | <!-- Assign id to edit.php to edit specific user -->
           <a class="btn bnt-primary" href="?id=<?php echo  $data['id'];?>">Delete</a> |
			</td>
		</tr>
	<?php } }else { ?>
		<tr><td colspan="5"><h2>No user data found..</h2></td></tr>
		<?php } ?>

	</table>

INSERT DATA

First include Database.php file and create database object to access method which are created inside the Database.php file. Create index.php file and write following code. 

<?php include('config.php'); ?>
<?php include('Database.php');

$db = new Database();
$helper = new Helper();

?>

     <?php
         if ($_SERVER['REQUEST_METHOD'] == "POST" && isset($_POST['submit'])){
               $name = $helper->validation($_POST['name']); //Accessing helper class method to access validation() method
               $name = mysqli_real_escape_string($db->link,$_POST['name']);

            if (empty($name)) {
              echo "<span style='color:red; font-size:18px;'> please fill out this field first</span>";
             }
           else{
                  $sql = "INSERT INTO `tbl_user` (`id`, `name`) VALUES (NULL, '$name')";
                    $user = $db->insert($sql);
                       if ($user){
                         echo  "Success";
                       }else{
                        echo  "Sorry try again";
                    }
                }
        }
     ?>
<form action="" method="post">
    Name
    <input type="text" name="name" placeholder="Enter your name" />
    <input type="submit" name="submit" class="btn btn-info" value="Submit">
</form>

UPDATE DATA

For updating data you first create a file update.php. Keep in mind that , for updating data first you have to print data in the input form . How we can do it? it is little bit difficult , to clearly understand see the following step.

Now time to edit data. To edit data we already catch specific id when we read data in index page. For update data we have to print updated data in hmtl form first. So create a page like edit.php and paste the following code. 

We got the data in our input filed to update. Now just write the query to update data in edit.php file in any place.

if ($_SERVER['REQUEST_METHOD'] == "POST" && isset($_POST['submit'])) {
$name = mysqli_real_escape_string($db->link,$_POST['name']);
$name = $helper->validation($_POST['name']);

    if (empty($name)) {
       echo "Enter name first";
      }
   else{
     $query = "update tbl_user
                 set
                 name= '$name'
                 where id = '$userid '"; //here id mean which you have caught from $_GET
                   $data = $db->update($query);
                   if ($data) {
                    echo 'data updated successfully';
                   }
                   else{
                    echo "Sorry try again";
                   }
               }
}

DELETE DATA

Now we are in our last part . So doing it , go to your select.php file & write those following code at the top of this page

if (isset($_GET["id"]) ){
       $deleteId = preg_replace('/[^-a-zA-Z0-9_]/', '',$_GET['id']); //Filtering data
          $query = "delete tbl_user where id = "$deleteId";
                   $data = $db->delete($query);
                   if ($data) {
                    echo 'data deleted successfully';
                   }
                   else{
                    echo "Sorry try again";
                   }
               }
}

Hope you will understand all these procedure. If you like this tutorial please leave a comments and share with your friends. If you find any error of this tutorial , please share this with me.

Leave a comments

LET'S SOCIALITE

Recent Tweets

RECOMMENDED POST