Welcome to World War Stories where all top stories of the past wars are written for your taste of information and action. We have gathered all the best war stories and opinions from different readers and contributors. Ancient political and war events are also included such as the Naval battles of Horatio Nelson and the 100 years war. We give you the past and the present world military conflicts so that you will be aware of how ugly war is and for every citizen to be able to contemplate the horrors of the past world conflicts. Welcome to World War Stories!

INSERT, UPDATE AND DELETE WITH PDO AND PAGINATION

Developed by: Artworks of Scanhead Brian Rosal of HNU
This tutorial will teach you on how to Add/edit/delete data from database table.
using PDO query in PHP.
And with pagination from Jquery and Bootstrap for the design.


First let's create the following:
1. Open phpmyadmin
2. Then create database and name it as "testdb1".
3. After creating a database name, click the SQL and paste the following code.
4. Download  Bootstrap and make your sure place it in the proper directory of your code
5. Download  jquery.bootpag.min.js 
CREATE TABLE `student` (
  `studno` int(4) NOT NULL,
  `studname` varchar(30) NOT NULL,
  `studcourse` varchar(10) NOT NULL,
  `studage` int(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

let's create our Database Class this program holds our connection and our methods for displaying of records.

db.class.php


 <?php  
 class db {  
      private $conn;  
      private $host;  
      private $user;  
      private $password;  
      private $baseName;  
      private $port;  
      private $Debug;  
   function __construct($params=array()) {  
           $this->conn = false;  
           $this->host = 'localhost'; //hostname  
           $this->user = ''; //username  
           $this->password = ''; //password  
           $this->baseName = 'testdb1'; //name of your database  
           $this->port = '3306';  
           $this->debug = true;  
           $this->connect();  
      }  
      function __destruct() {  
           $this->disconnect();  
      }  
      function connect() {  
           if (!$this->conn) {  
                try {  
                     $this->conn = new PDO('mysql:host='.$this->host.';dbname='.$this->baseName.'', $this->user, $this->password, array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));   
                }  
                catch (Exception $e) {  
                     die('Erreur : ' . $e->getMessage());  
                }  
                if (!$this->conn) {  
                     $this->status_fatal = true;  
                     echo 'Connection BDD failed';  
                     die();  
                }   
                else {  
                     $this->status_fatal = false;  
                }  
           }  
           return $this->conn;  
      }  
      function disconnect() {  
           if ($this->conn) {  
                $this->conn = null;  
           }  
      }  
      function getOne($query) {  
           $result = $this->conn->prepare($query);  
           $ret = $result->execute();  
           if (!$ret) {  
             echo 'PDO::errorInfo():';  
             echo '<br />';  
             echo 'error SQL: '.$query;  
             die();  
           }  
           $result->setFetchMode(PDO::FETCH_ASSOC);  
           $reponse = $result->fetch();  
           return $reponse;  
      }  
      function getAll($query) {  
           $result = $this->conn->prepare($query);  
           $ret = $result->execute();  
           if (!$ret) {  
             echo 'PDO::errorInfo():';  
             echo '<br />';  
             echo 'error SQL: '.$query;  
             die();  
           }  
           $result->setFetchMode(PDO::FETCH_ASSOC);  
           $reponse = $result->fetchAll();  
           return $reponse;  
      }  
      function pagi($query)  //for pagination use with count in sql  
      {  
           $result = $this->conn->prepare($query);  
           $ret = $result->execute();  
           if (!$ret) {  
             echo 'PDO::errorInfo():';  
             echo '<br />';  
             echo 'error SQL: '.$query;  
             die();  
           }  
           $response = $result->fetch();  
           return $response;  
      }  
      function execute($query) {  
           if (!$response = $this->conn->exec($query)) {  
                echo 'PDO::errorInfo():';  
             echo '<br />';  
             echo 'error SQL: '.$query;  
             die();  
           }  
           return $response;  
      }  
 }  
Creating our Data Entry Form with Bootstrap

Testentry.php

 <?php  
 //Artworks of Scanhead with bootstrap  
 include('db.class.php'); // call db.class.php  
 $mydb = new db(); // create a new object, class db()  
 class myData{  
   public $name;  
   public $course;  
   public $age;  
   public function setData($pname, $pcourse, $page)  
   {  
     $this->name = $pname;  
     $this->course = $pcourse;  
     $this->age= $page;  
   }  
 }  
 ?>  
 <!DOCTYPE html>  
 <html>  
 <head>  
   <title></title>  
 <link href="css/bootstrap.min.css" rel="stylesheet" type="text/css" />       
 </head>  
 <body>  
 <?php  
    if (isset($_POST['submit']))   
   {  
     $mname=$_POST['myname'];  
     $mcourse=$_POST['mycourse'];  
     $mage=$_POST['myage'];  
     //creating object now  
     $data= new myData();  
           //db connection and insert into here....  
     $data->setData($mname,$mcourse,$mage);  
           $query = $mydb->execute('INSERT INTO student (studname, studcourse,studage) VALUES ("'.$mname.'","'.$mcourse.'","'.$mage.'")');  
    if (!empty($query))  
            {  
      echo "New record created successfully </br>";  
         echo "Your name is ". $data->name;  
            } else {  
      echo "Error: " ;  
      }  
    }    
 ?>  
 <div class="container">  
  <h2>Vertical (basic) form</h2>  
  <form name="" method="post">  
   <div class="row">  
       <div class="form-group col-lg-4">  
    Name:  
    <input type="text" class="form-control" required placeholder="Enter Name" name="myname">  
    </div>  
   </div>  
   <div class="row">  
    <div class="form-group col-lg-3">  
    Course:  
    <input type="text" class="form-control" required placeholder="Input Course" name="mycourse">  
    </div>  
      </div>  
   <div class="row">  
       <div class="form-group col-lg-1">  
      Age:  
    <input type="text" class="form-control" required placeholder="Input age" name="myage">  
   </div>  
      </div>  
   <input type="submit" name="submit" class="btn btn-default">  
  </form>  
 </div>  
 <a href="index.php">View Records</a>  
 </body>  
 </html>  

Now let's create the Index.php this has pagination using JQuery

Index.php

 <?php  
 //Artworks of Scanhead  HNU 2017  
 include('db.class.php'); // call db.class.php  
 $mydb = new db(); // create a new object, class db()  
 $item_per_page = 7;  
 $get_total_rows = $mydb->pagi('SELECT COUNT(*) FROM student'); // 1 line selection, return 1 line  
 $pages = ceil($get_total_rows[0]/$item_per_page);       
 ?>  
 <!DOCTYPE html>  
 <html>  
 <head>  
 <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />  
 <title>Complete Data L</title>  
 <script src="js/jquery-1.11.3-jquery.min.js"></script>     
 <script type="text/javascript" src="js/jquery.bootpag.min.js"></script>  
 <script type="text/javascript">  
 $(document).ready(function() {  
      $("#results").load("get_records.php"); //initial page number to load  
      $(".paging_link").bootpag({  
        total: <?php echo $pages; ?>  
      }).on("page", function(e, num){  
           e.preventDefault();  
           $("#results").load("get_records.php", {'page':num});  
      });  
 });  
 </script>  
  <link href="css/bootstrap.min.css" rel="stylesheet" type="text/css" />  
  <link href="css/style.css" rel="stylesheet" type="text/css">  
 </head>  
 <body>  
 <div class="container">  
   <div class="panel panel-default">  
     <div class="panel-heading text-center"><h3>List of Students</h3></div>  
 <a href="testentry.php">Add Record</a>  
     <table class="table table-bordered table-hover">  
       <thead>  
         <tr>  
           <th>student no.</th>  
           <th>student name</th>  
           <th>student course</th>  
          </tr>  
       </thead>  
       <tbody id="results">  
       </tbody>  
     </table>  
  <div class="panel-footer text-center">  
       <div class="paging_link"></div>  
     </div>  
   </div>  
 </div>  
 </body>  
 </html>  

The Index.php will call the get_records which will display all the records in the database

get_records.php


 <?php  
 //Artworks of Scanhead  
 include('db.class.php'); // call db.class.php  
 $mydb = new db(); // create a new object, class db()  
 $item_per_page = 7;  
 //sanitize post value  
 if(isset($_POST["page"])){  
      $page_number = filter_var($_POST["page"], FILTER_SANITIZE_NUMBER_INT, FILTER_FLAG_STRIP_HIGH);  
      if(!is_numeric($page_number)){die('Invalid page number!');} //incase of invalid page number  
 }else{  
      $page_number = 1;  
 }  
 //get current starting point of records  
 $position = (($page_number-1) * $item_per_page);  
 //connection artworks of scanhead hnu 2017  
 $conn = $mydb->connect();  
 $results = $conn->prepare("SELECT * FROM student ORDER BY studno DESC LIMIT $position, $item_per_page");  
 $results->execute();  
 ?>  
 <ul class="page_result">  
 <?php  
 while($row = $results->fetch(PDO::FETCH_ASSOC))  
 {  
       echo '<tr>' .   
   '<td>' . $row['studno'] . '</td>' .   
   '<td>' . $row['studname'] . '</td>' .   
   '<td>' . $row['studcourse'] . '</td>' .   
   '<td><a href="editform.php?id=' . $row['studno'] . '">Edit</a></td>'.  
   '<td><a href="delete.php?id=' . $row['studno'] . '">Delete</a></td>'.  
      '</tr>';  
 }  
 ?>  
 </ul>  

Now let us make the Delete.php

Delete.php


 <?php  
 // Artworks of Scanhead HNU 2017  
 // connect to the database  
 include('db.class.php'); // call db.class.php  
 $mydb = new db(); // create a new object, class db()  
 // confirm that the 'id' variable has been set  
 if (isset($_GET['id']) && is_numeric($_GET['id']))  
 {  
 // get the 'id' variable from the URL  
 $id = $_GET['id'];  
 // delete record from database  
 $conn = $mydb->connect();  
 if ($stmt = $conn->prepare("DELETE FROM student WHERE studno = :id LIMIT 1"))  
 {  
 $stmt->bindParam(":id",$id,PDO::PARAM_INT);  
 //$stmt->bind_param("i",$id); mysqli way  
 $stmt->execute();  
 $stmt->null; // closing database in pdo   
 }  
 else  
 {  
 echo "ERROR: could not prepare SQL statement.";  
 }  
 $conn->null;  
 //$mysqli->close(); closing database in mysqli  
 // redirect user after delete is successful  
 header("Location: index.php");  
 }  
 else  
 // if the 'id' variable isn't set, redirect the user  
 {  
 header("Location: index.php");  
 }  
 ?>  

Editform.php

1:  <?php  
2:  // Artworks of Scanhead HNU 2017  
3:  // connect to the database  
4:  include('db.class.php'); // call db.class.php  
5:  $mydb = new db(); // create a new object, class db()  
6:  // confirm that the 'id' variable has been set  
7:  if (isset($_GET['id']) && is_numeric($_GET['id']))  
8:  {  
9:  // get the 'id' variable from the URL  
10:  $id = $_GET['id'];  
11:  $conn = $mydb->connect();  
12:  $User = $mydb->getOne('SELECT * FROM student WHERE studno = "'.$id.'"');  
13:  ?>  
14:  <!DOCTYPE html>  
15:  <html>  
16:  <head>  
17:    <title></title>  
18:  <link href="css/bootstrap.min.css" rel="stylesheet" type="text/css" />       
19:  </head>  
20:  <body>  
21:  <div class="container">  
22:   <h2>Edit this Record</h2>  
23:   <form action="edit.php" method="post">  
24:    <input type="hidden" name="mystudno" value="<?php echo $id; ?>" />  
25:    <div class="row">  
26:        <div class="form-group col-lg-4">  
27:     Name:  
28:     <input type="text" class="form-control" required value="<?php echo $User['studname']; ?>" name="myname">  
29:     </div>  
30:    </div>  
31:    <div class="row">  
32:     <div class="form-group col-lg-3">  
33:     Course:  
34:     <input type="text" class="form-control" required value="<?php echo $User['studcourse']; ?>" name="mycourse">  
35:     </div>  
36:       </div>  
37:    <div class="row">  
38:        <div class="form-group col-lg-1">  
39:       Age:  
40:     <input type="text" class="form-control" required value="<?php echo $User['studage']; ?>" name="myage">  
41:    </div>  
42:       </div>  
43:    <input type="submit" name="submit" class="btn btn-default">  
44:   </form>  
45:  </div>  
46:  <?php  
47:  }  
48:  ?>  
49:  <a href="index.php">View Records</a>  
50:  </body>  
51:  </html>  


Edit.php


1:  <?php  
2:  // Artworks of Scanhead Edit  
3:  include('db.class.php'); // call db.class.php  
4:  $mydb = new db(); // create a new object, class db()  
5:  $conn = $mydb->connect();  
6:  // new data  
7:  $name = $_POST['myname'];  
8:  $course = $_POST['mycourse'];  
9:  $age = $_POST['myage'];  
10:  $id = $_POST['mystudno'];  
11:  $sql = 'UPDATE student   
12:      SET studname=?, studcourse=?, studage=?  
13:            WHERE studno=?';  
14:  $stmt = $conn->prepare($sql);  
15:  $stmt->execute(array($name,$course,$age,$id));  
16:  header("location: index.php");  
17:  ?>  

No comments :