Follow

Welcome to TechYari!

Tutorials on webdesign, web development and more...
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.  
Security is very essential to any web application. SQL injection is a technique where attacker tries to execute malicious scripts on application's database using several security flaws. The easiest way to do this is sending the SQL script via get or post request that do not have proper validation and security filtering on server.In this post we will look at how to prevent sql injection using two main techniques.

how to prevent sql injection in php


Lets assume we have a login page having two form fields, one for username and another for password. We will be using POST method to submit this data to the server. When user clicks on Sign in button all the data will be submitted to server and authenticate.php script will take care of validation.

Below is the HTML code for the typical login form:


<form action="authenticate.php" method="POST">  
    <input type="text" name="username" placeholder="Username" required autofocus/><br/>
    <input type="password" name="password" placeholder="Password" required/><br/>
    <input type="submit" value="Sign in"/>
</form>


There are several ways you can prevent SQL injection while submitting the data from login form to server:

1. Using PDO
2. Using MySqli
3. Escaping special characters

Let's talk about each one of them :

Prepared Statements with PDO and MySqli

You can use PDO to achieve this. Using PDO is considered the best option however it requires the PDO extension to be enabled on you server. Most of the servers running PHP 5.3 or later have this option enabled and if not you can check with you hosting provider and ask them to enable the extension.

Using PDO : Below is an example of using PDO to prevent SQL injection.



 <?
 // define database related variables
   $database = 'your_database_here';
   $host = 'localhost';
   $user = 'your_db_username';
   $pass = 'your_db_password';

   // 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";
   }

    // Create placeholder variables
 $username = "";
 $password = "";
 
 // Check if the values are set and assign to variables
 if(isset($_POST['username'])){
  $username = $_POST['username'];
 }
 if (isset($_POST['password'])) {
  $password = $_POST['password'];

 }
 
 // Check if user has entered some values
 if($username!="" && $password!=""){

      // Create query
   $q = 'SELECT * FROM users WHERE username=:username AND password=:password';
      // Prepare query using PDO
   $query = $dbh->prepare($q);
      // Execute query
   $query->execute(array(':username' => $username, ':password' => $password));


   if($query->rowCount() == 0){
    
    // Send back to login page with an error message

   }else{
    
    // Add user to session and take him to home page.
   }
 }else{

   // Back to login page with error message. 

 }

   

  ?> 

Using MySqli : Below is an example of using MySqli to prevent SQL injection.




 <?
 // define database related variables
   $database = 'your_database_here';
   $host = 'localhost';
   $user = 'your_db_username';
   $pass = 'your_db_password';

  $mysqli = new mysqli($host, $user, $pass, $database);
  
  if ($mysqli->connect_errno) {
    echo "Failed to connect to database: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
    }

    // Create placeholder variables
 $username = "";
 $password = "";
 
 // Check if the values are set and assign to variables
 if(isset($_POST['username'])){
  $username = $_POST['username'];
 }
 if (isset($_POST['password'])) {
  $password = $_POST['password'];

 }
 
 // Check if user has entered some values
 if($username!="" && $password!=""){
            
            // Prepare statement
   $stmt = $mysqli->prepare('SELECT * FROM users WHERE username=? AND password=?');
   if (!$stmt->bind_param("i", $username,$password)) {
        echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
   }

   // Execute statement
   if (!$stmt->execute()) {
        echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
   }
       
       // Get result 
            $result = $stmt->get_result(); // This method requies mysqlnd drives. pleas eamke sure it is available or else you will get errors.
            
            // Check number of rows returned from database.
            if($result->num_rows > 0){

             //fetch the data and login user

            }else{

             // Back to login page with error as username and password does not match.
            }

  }else{

         // Back to login page with error message. 
  }

 
  ?> 

We have seen both ways of submitting data which can save you from sql injection. We also talked about escaping special charatacters which can be used to prevent sql injection as well. Below is an example of how you can escape special characters while getting data from our login page.

Using  mysql_real_escape_string to escape special characters



  // Create placeholder variables
 $username = "";
 $password = "";
 
 // Check if the values are set and assign to variables
 if(isset($_POST['username'])){
  // check to see if there are any special characters, remove them and assign safe value to username variable
  $username = mysql_real_escape_string($_POST['username']);
 }
 if (isset($_POST['password'])) {
  // check to see if there are any special characters, remove them and assign safe value to password variable
  $password = mysql_real_escape_string($_POST['password']);

 }

That's all for today. If you have any queries or suggestions feel free to comment below. Cheers :)
In this tutorial I am going to show you how to build a rating system in php using jQuery, Ajax and MySql. We will be using jQuery to show rating stars and we will also make ajax request when user clicks on a star to rate an article. MySql databse is being used to store our article and related rating data for each article.

To start we need to create two tables, one for storing articles and another for storing ratings for those articles by different users. In order to keep it simple I am leaving the user part. So we will have anonymous ratings ( Just to keep it simple ).


Download Source Live Demo


Setting Up Tables for article and ratings


Below is the script to create article and article rating table. Run this script in your database and populate article table with few entries manually to test.


CREATE TABLE article(
   id INT NOT NULL AUTO_INCREMENT,
   text VARCHAR(200) NOT NULL,
   PRIMARY KEY (id)
);

CREATE TABLE article_rating(
   article_id INT NOT NULL,
   rating INT NOT NULL
);

Configuring PHP to connect to database (database-config.php).


Create a PHP file and use below code to connect to MySql from PHP. Change variable values according to your environment.


<?php
   // define database related variables
   $database = 'your_database_name_here';
   $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";
   }
   
?>

Inserting user ratings to database via PHP (rate.php).


To rate each article we will store article id and article rating to previously created article_rating table. Here we receive article id and rating from an ajax GET request.


<?php 
 require 'database-config.php';

 $id = "";
 $rating = "";
 
 if(isset($_GET['id'])){
  $id = $_GET['id'];
 }
 if (isset($_GET['rating'])) {
  $rating = $_GET['rating'];

 }
 
 

 $q = 'INSERT INTO article_rating(article_id,rating) VALUES (:id,:rating)';

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

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


 if($query->rowCount() != 0){
  echo "rated successfully";
 }else{
  echo "There was an error";
 }


?>

PHP code to get articles and their ratings from database.


Create an index.php file and paste following code at top section before html tag. The code listed below gets all the articles and their ratings from the database and saves it into an array. We will be using this array to show them later (index.php).


<?PHP 
    
      require 'database-config.php';
      // Get all the articles from the database
      $q = 'SELECT * FROM article';
      $query = $dbh->prepare($q);
      $query->execute();


    // Iterate for each article returned via query  
    $articles = array(); 
    
    while($row = $query->fetch(PDO::FETCH_ASSOC)){
         $data = "";
         $id = $row['id'];
         $text = $row['text'];
         $average = 0;
         
         // Get total count of rating for an article
         $q1 = 'SELECT count(*) FROM article_rating where article_id=:id';
         $query1 = $dbh->prepare($q1);
         $query1->execute(array(':id' => $id));
         $row1 = $query1->fetch(PDO::FETCH_ASSOC);
         $count = $row1['count(*)'];
         
         if($count > 0){
             // Get all ratings for an article
             $q2 = 'SELECT rating FROM article_rating where article_id=:id';
             $query2 = $dbh->prepare($q2);
             $query2->execute(array(':id' => $id));
             
             // Sum up the ratings 
             $rating = 0; 
             while($row2 = $query2->fetch(PDO::FETCH_ASSOC)){
               $rating = $rating + $row2['rating'];
             } 
             
             //Calculate average rating by deviding the sum by total ratings and rounding the result. 
             $average = ceil($rating/$count);
         }else{

             $average = 0;
         }
         
         $data = array($id,$text,$average);
         //Store each article and related rating in articles array
         $articles[] = $data;
    }
   
?>

Code to show articles on homepage (index.php).


Now use following code between body tags to show articles in your index.php file. I am using twitter bootstrap for alignment purpose. Please include that as well in your index.php file to get desired results or you can also style them yourself.


       <?php for($i = 0; $i<sizeof($articles);$i++){ ?>

                      <div class="article row" article-id="<?php echo $articles[$i][0] ?>">
                        <div class="id col-md-2"><?php echo $articles[$i][0] ?></div>
                        <div class="text col-md-7"><?php echo $articles[$i][1] ?></div>
                        <div class="rating col-md-3 <?php echo $articles[$i][2] ?>"><?php echo $articles[$i][2] ?></div>
                      </div>

                     <?php } ?> 

Styling star ratings (style.css).


Create a css file and paste below code, save with any name and include the file in your index.php file created earlier


.info{text-align: center;}
.row{padding: 10px;
background: beige;
margin-bottom: 5px;
font-size: 16px;
font-weight: 600;}
h2{padding: 10px;}
.gray{color:rgb(189, 187, 187)}
.red{color:red;}

Creating star icons for rating, applying hovers styles for stars and making ajax request when user selects a rating. (main.js)


Create a javascript file and paste below code, you also need to include this file in your index.php file.
Here we achieve three things
1. We apply star icons using bootstrap classes based on rating numbers.
2. When user hovers over a star to rate an article we highlight the stars by checking the star position and show him a nice effect using jquery
3. Lastly when user selects a rating by clicking on a star we make an ajax request with jquery and insert the data into our article_rating table.


$(document).ready(function(){

 // Apply style based on ratings from database

 $('.5').html('<span class="glyphicon glyphicon-heart" rating="1"></span><span class="glyphicon glyphicon-heart" rating="2"></span><span class="glyphicon glyphicon-heart" rating="3"></span><span class="glyphicon glyphicon-heart" rating="4"></span><span class="glyphicon glyphicon-heart" rating="5"></span>');
  $('.4').html('<span class="glyphicon glyphicon-heart" rating="1"></span><span class="glyphicon glyphicon-heart" rating="2"></span><span class="glyphicon glyphicon-heart" rating="3"></span><span class="glyphicon glyphicon-heart" rating="4"></span><span class="glyphicon glyphicon-heart gray" rating="5"></span>');
   $('.3').html('<span class="glyphicon glyphicon-heart" rating="1"></span><span class="glyphicon glyphicon-heart" rating="2"></span><span class="glyphicon glyphicon-heart" rating="3"></span><span class="glyphicon glyphicon-heart gray" rating="4"></span><span class="glyphicon glyphicon-heart gray" rating="5"></span>');
    $('.2').html('<span class="glyphicon glyphicon-heart" rating="1"></span><span class="glyphicon glyphicon-heart" rating="2"></span><span class="glyphicon glyphicon-heart gray" rating="3"></span><span class="glyphicon glyphicon-heart gray" rating="4"></span><span class="glyphicon glyphicon-heart gray" rating="5"></span>');
     $('.1').html('<span class="glyphicon glyphicon-heart" rating="1"></span><span class="glyphicon glyphicon-heart gray" rating="2"></span><span class="glyphicon glyphicon-heart gray" rating="3"></span><span class="glyphicon glyphicon-heart gray" rating="4"></span><span class="glyphicon glyphicon-heart gray" rating="5"></span>');
      $('.0').html('<span class="glyphicon glyphicon-heart gray" rating="1"></span><span class="glyphicon glyphicon-heart gray" rating="2"></span><span class="glyphicon glyphicon-heart gray" rating="3"></span><span class="glyphicon glyphicon-heart gray" rating="4"></span><span class="glyphicon glyphicon-heart gray" rating="5"></span>');



 // Appy style on hover by user

 $("span[rating='1']").hover(function(){
  console.log("selected 1");
  $(this).addClass('red');
  },function() {
        $( this ).removeClass('red');
     }
 );

 $("span[rating='2']").hover(function(){
  console.log("selected 1");
  $(this).prev().addClass('red');
  $(this).addClass('red');
  },function() {
   $( this ).prev().removeClass('red');
        $( this ).removeClass('red');
     }
 );

 $("span[rating='3']").hover(function(){
  console.log("selected 1");
  $(this).prev().prev().addClass('red');
  $(this).prev().addClass('red');
  $(this).addClass('red');
  },function() {
   $( this ).prev().prev().removeClass('red');
   $( this ).prev().removeClass('red');
        $( this ).removeClass('red');
        
     }
 );

 $("span[rating='4']").hover(function(){
  console.log("selected 1");
  $(this).prev().prev().prev().addClass('red');
  $(this).prev().prev().addClass('red');
  $(this).prev().addClass('red');
  $(this).addClass('red');
  },function() {
   $( this ).prev().prev().prev().removeClass('red');
   $( this ).prev().prev().removeClass('red');
   $( this ).prev().removeClass('red');
        $( this ).removeClass('red');
        
     }
 );

 $("span[rating='5']").hover(function(){
  console.log("selected 1");
  $(this).prev().prev().prev().prev().addClass('red');
  $(this).prev().prev().prev().addClass('red');
  $(this).prev().prev().addClass('red');
  $(this).prev().addClass('red');
  $(this).addClass('red');
  },function() {
   $( this ).prev().prev().prev().prev().removeClass('red');
   $( this ).prev().prev().prev().removeClass('red');
   $( this ).prev().prev().removeClass('red');
   $( this ).prev().removeClass('red');
        $( this ).removeClass('red');
        
     }
 );
 
    // Making ajax request when user selects a rating for an article.
 $('.glyphicon').click(function(){

  var rating = $(this).attr('rating');
        var id = $(this).parent().parent().attr('article-id');
  console.log(rating);
  console.log(id);

  $.ajax({
       url: "rate.php",
       type: 'GET',
       data: {id : id,rating:rating},
       success: function(data) {  
       console.log(data); 
       location.reload();
   },
      error: function(e) {
      console.log(e); 
  }
 });
 })

});

That's it we have build a rating system in php using jQuey and Ajax. If you run into any issues you can download the source code from here
I hope you enjoyed this article. Please share your thoughts and queries using comment box below the article.

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.