Follow

Welcome to TechYari!

Tutorials on webdesign, web development and more...

CRUD Operations in PHP Using PDO with MySql Database



By  Vipin Dubey     11/21/2014    Labels:,, 
CRUD (Create, Read, Update and Delete) are one of the most important operations for a web application. In this tutorial, I will show you how to use PHP with PDO and MySql database for CRUD operations. I will be creating a simple quotes app where a user can publish a quote he likes. The homepage will contain a list of quotes with links to Update and Delete each quote.

crud in php with pdo and mysql

Download Source Live Demo



To keep this tutorial simple and focused to CRUD operations I have left validations for user part, it means anyone can update and delete the quotes. If you would like to make it secure you can go through one of the earlier tutorial Role base login in PHP with MySql  and implement session control to make it secure.

Lets start with creating our table first.

Database Table Setup

Create a table called demo_quotes with two columns (id and text). Take a look at below script if you have any doubts, you can directly run this in sql window of phpmydmin.


CREATE TABLE demo_quotes(
   id INT NOT NULL AUTO_INCREMENT,
   text VARCHAR(1000) NOT NULL,
   PRIMARY KEY (id)
);


Connect to database from PHP using PDO

Use below script to connect to database using PDO. Create a file named database-config.php and paste below code, we will import this file wherever we need to perform database related operations.


<?php
   // define database related variables
   $database = 'techyari_demos';
   $host = 'localhost';
   $user = 'root';
   $pass = '';

   // try to conncet to database
   $dbh = new PDO("mysql:dbname={$database};host={$host};port={3306}", $user, $pass);

   if(!$dbh){

      echo "unable to connect to database";
   }
   
?>



Creating the Home Page

We will create a file called index.php. This file will serve following purpose :

1. Display a form to post new quotes.
2. Show a list of posts from database by performing READ operation and display edit and delete links with each quote.
3. Display an editable form when user clicks on edit link for any of the quotes in the list.
4. Show appropriate errors if any based on url parameter.

First of all we will include database-config.php at top of this file as we need to perform database related operations.

Next we need to define an associative array for our errors so that we can display errors based on url parameters being received from crud.php script. 

Next we check for action and id parameters in url, if present we display the edit form and if not then we display the homepage.

Below is the code for index.php (homepage).


  <?php 
  // Import database connection script
  require 'database-config.php';
  ?>

  <!DOCTYPE html>
  <html lang="en">
  <head>
  <meta charset="utf-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>CRUD example in PHP with MySql database</title>
  <link href="css/style.css" rel="stylesheet">

  <!-- HTML5 Shim and Respond.js IE8 support of HTML5 elements and media queries -->
  <!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
    <!--[if lt IE 9]>
      <script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
      <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
      <![endif]-->
    </head>
    <body>  
      <div class="title"> <h2>CRUD example in PHP with MySql database</h2></div>
      <?php 

                                 // Set default error messages 
      $errors = array(
        1=>"Please provide values for all the fields"
        );

                                 // check if url contains an error code as a parameter
      $error_id = isset($_GET['err']) ? (int)$_GET['err'] : 0;

                                 // show error if exists 
      if ($error_id == 1) {
        echo '<p class="text-danger">'.$errors[$error_id].'</p>';
      }


  /* check if url contains any action parameters and load the parts accordingly

    Load index page with a form filed and all quotes if action parameter is not

    present. Load edit quote form if action parameter is present*/


    $action= "";
    $id="";

    if(isset($_GET['action']) && isset($_GET['id'])){

        $action = $_GET['action'];

         $id = $_GET['id'];
                                  }

    if($action=="edit" && $id!=""){ 

    /* if an edit action is present, get the quote with help of id parameter
                                       
    display the quote in an editable form and bind crud.php (update action) 

    using hidden input tag. */


    $q = 'SELECT * FROM demo_quotes WHERE id = :id';

    $query = $dbh->prepare($q);

    $query->execute(array(':id' => $id));

    $row = $query->fetch(PDO::FETCH_ASSOC);

    ?>

    <div class="article-form">
      <form action="crud.php" method="POST">  
        <textarea name="text" maxlength="300"><?php echo $row['text'];?></textarea>
        <input type="hidden" name="id" value="<?php echo $id; ?>" />
        <input type="hidden" name="action" value="update" /><br/>
        <input type="submit" value="Update"/>
      </form>
    </div>

    <?php }else{ ?>

    <!-- This section displays an HTML form to publish posts. It also lists all the posts
                                       
      from the database in a table with EDIT and DELETE links to each of them. 

      This part of code will only run if there is no action parameter present in the URL. 
    -->

    <div class="article-form">
      <form action="crud.php" method="POST">  
        <textarea name="text" maxlength="300" placeholder="Input text here" required autofocus></textarea><br/>
        <input type="submit" value="publish"/>
      </form>
    </div>
     <br/>
     <br/>
    <div class="articles">
      <table>
        <thead>
          <th colspan="3">All Quotes</th>
        </thead>
          <tbody>

            <?php 

              $q1 = 'SELECT * FROM demo_quotes';

              $query1 = $dbh->prepare($q1);

              $query1->execute();

              if($query1->rowCount() == 0){
                  echo "<tr><td colspan='3'>No data available</td></tr>";
                }else{
                  while($row1 = $query1->fetch(PDO::FETCH_ASSOC)){

                      /* Here we print each quote in a row with EDIT and DELETE links. We pass the quote ID as a parameter
                                                          
                         to crud.php script and perfom edit and delete operation on an item */ 
                        
                        echo'<tr><td>' . $row1['text'] . '</td><td><a href="index.php?action=edit&id='.$row1['id']. '" class="edit">Edit</a></td><td><a href="crud.php?action=delete&id=' .$row1['id']. '" class="delete">Delete</a></td></tr>';
                    }
                  }
            ?>

          </tbody>
      </table>
    </div>

    <?php } ?>



  </body>
</html>


Styling the Home Page with CSS

We need to make our homepage look a bit cleaner. Copy and paste below style rules in a style.css file and link it properly to your index.php file.


/* Styling for home page */
.title{text-align: center;}
textarea{width: 400px;padding: 20px;}
.article-form{text-align: center; padding: 20px;width: 400px;margin: auto;}
.article-form input{
 float:right;padding: 5px;border: none;
 background: lightblue;
 color: #ffffff;
 font-weight: bold;
 font-size: inherit;
 margin-right: -40px;
}
.articles{max-width:100%;}
table{margin:auto;width: 600px;}
thead{background: lightblue}
th,td{padding:5px;border-bottom: 1px solid rgb(143, 207, 240);}
.edit{
 text-decoration: none;
 background: rgb(211, 184, 34);
 color: white;
 padding: 3px 7px;
 font-weight: 500;
}
.delete{
 text-decoration: none;
 background: rgb(211, 74, 34);
 color: white;
 padding: 3px 7px;
 font-weight: 500;

}


Performing  Create, Update and Delete Operations 

In order to insert, update and delete data we will create a crud.php file, this file will perform following operations

1. Check GET/POST request and retrieve action value ( Update/Delete ).
2. UPDATE a quote.
3. DELETE a quote.
4. INSERT a new quote.

At the beginning of this file we have a variable named action. this variable will hold the data passed by GET or POST request as action parameter. We will compare action variable value to decide which operation needs to be performed. The default would be an insert operation.

below is the code for CRUD operations


<?php 
 require 'database-config.php'; // import database connection script
    
    // Initialize action variable with empty string
    $action = "";
    
    // Check if we have action parameter with GET or POST request and set action value accordingly
 if(isset($_GET['action'])){
  $action = $_GET['action'];
 }

 if(isset($_POST['action'])){
  $action = $_POST['action'];
 }

 // Perform an UPDATE operation when action variable contains update

    if($action == "update"){
     $id = "";
        $text = "";
     if(isset($_POST['id'])){
   $id = $_POST['id'];
  }

  if(isset($_POST['text'])){
   $text = $_POST['text'];
  }

  if($text!="" && $id!=""){

   $q = 'UPDATE demo_quotes SET text=:text WHERE id=:id';

   $query = $dbh->prepare($q);

   $query->execute(array(':text' => $text, ':id' => $id));

   header('Location: index.php'); // Return to index page

  }else{

   header('Location: index.php?err=1'); // Return to index page with an error
  }

  

    }elseif($action == "delete"){    // Perform DELETE operation when action variable contains update
     $id = "";
     if(isset($_GET['id'])){
   $id = $_GET['id'];
  }

  if($id!=""){

   $q = 'DELETE from demo_quotes WHERE id=:id';

   $query = $dbh->prepare($q);

   $query->execute(array(':id' => $id));

   header('Location: index.php'); // Return to index page
  }else{

   header('Location: index.php?err=1'); // Return to index page with an error
  }
    }else{               // Perform an INSERT operation when action is not equal to update/delte

        $text = "";

  if(isset($_POST['text'])){
   $text = $_POST['text'];
  }

  if($text!=""){

   $q = 'INSERT INTO demo_quotes(text) VALUES (:text)';

   $query = $dbh->prepare($q);

   $query->execute(array(':text' => $text));

   header('Location: index.php'); // Return to index page
  }else{

   header('Location: index.php?err=1'); // Return to index page with an error
  }

    }


?>

This is it. We have created a simple website capable to perform CRUD ( Create, Read, Update, Delete) operations using PDO with MySql database. Leave a comment if you have any queries or suggestions.  

About Vipin Dubey

A web developer, blogger, amateur designer and an open source enthusiast.

No comments:

Post a Comment


Contact Form

Name

Email *

Message *

Labels

Follow TechYari on

Translate

About TechYari

Tech Yari is a Technical Blog featuring articles on Web Development, Web Design, Search Engine Optimization, Social Media Marketting and Programming.