Crewow | Free Web Development Tutorials: Export to Excel

Source is here

In software systems or web based systems developed by us, we have to secure some pages from unauthorized access and for that purpose we will protect these pages. These pages can be for Admin members or registered members and only those can see them. For giving members rights we will often give them username and password and when they give these values, they are redirected to secret pages.

PHP easy sign-in using PDO prepared statement tutorial will teach you how to make a log in system using PDO (PHP Data Objects) queries. This tutorial contains very basics and easy steps so anyone can understand it and implement it. Source code for sign-in tutorial is also available for you. We will use bootstrap as CSS Framework with PHP and MySQL. We will also understand how to protect pages from unauthorized access if someone knows the link and directly type in browser. This tutorial will teach you following

How to make a Sign in Form.
How to create a Members Table.
How to make a Connection Page
How to check a User Details for Authentication.
How to redirect a Registered User.
How to protect a User Page.
How to make a User Session Page.
How to make a Logout Page.

Source Code Zip Folder contains following files and folders, below is the explanation of each.

sql.txt contains full database structure along with sample data.
index.php is file from where you can login to system
AdminIndex.php is a page which will open after successful login.
CheckLogin.php is a file which will check the username and password and redirect to secret pages like AdminIndex.php
configPDO.php is a file which will create connection with database.
NavButtons.php is a file which contains menu list for this system.
Footer.php is a file which contains footer bar and forms for sign in, password update and server information addition.
Logout.php is a file which will destroy the session and logout the user to index.php page.
UserSessionAdmin.php is a file which included for protection of pages from unauthorized users.
assets folder contains necessary css, icon, images and js files.
OPEN-TICKET-EXCEL.php is a file which will export data in Excel.

We use Jquery Bootstrap plugin for sign in form. You can learn more aboutjqBootstrapValidation.

We use following for developing PHP easy sign-in using PDO prepared statement tutorial in which EXPORT To EXCEL option using PDO also included.

PHP as Server Side Scripting Language
MySQL as a Database.
Bootstrap as CSS Framework.
JQuery as a service.
jqBootstrapValidation for validating sign-in form.
phpMyAdmin for creating database and tables.
Sublime Text 2+ as a code editor.
XAMPP as a package for Apache web server, PHP and MySQL.
Google Chrome as a browser for testing.

First we will make a sign-in button in index.php page and use data-toggle as modal and give href value as #signin

<a href="#signin" data-toggle="modal" class"btn btn-primary btn-large">Sign In</a>

When we press sign in button, modal will be open which will display below form for sign in. Form code is in footer.php page and its a part of index.php page.

<div id="signin" class="modal hide fade" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
            <div class="modal-header">
              <button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
              <h3 id="myModalLabel">Sign In Form</h3>
            </div>
            <div class="modal-body">
<form class="form-horizontal" method="post" action="CheckLogin.php">
            <div class="control-group">
              <label class="control-label" for="inputName">User Name</label>
              <div class="controls">
                <input type="text" name="inputName"  id="inputName" placeholder="User Name" required="required">
              </div>
            </div>
            <div class="control-group">
              <label class="control-label" for="inputPassword">Password</label>
              <div class="controls">
                <input type="password" name="inputPassword" id="inputPassword" placeholder="Password" maxlength="15" minlength="6"  required="required">
              </div>
            </div>
            <div class="control-group">
              <div class="controls">
                <button type="submit" class="btn">Sign in</button>
              </div>
            </div>
          </form>

            </div>
          </div>

Before sending username and password to CheckLogin.php we will create signinpdo database using sql.txt file which contains below tables create queries and sample data for demo and test.

members table contains members details like MemId, UserName, Password, Type, Region.
ttmain table contains fields like SrNo, TTDescription, TTCity, TTEntryDate, TTEntryTime, TTEntryBy, TTClosedDate, TTClosedTime, TTClosedBy, Status.

configPDO.php file use a PDO (PHP Data Objects) for connection to MySQL Databaseto avoid SQL injections.

<?php
// mysql hostname
$hostname = 'localhost';
// mysql username
$username = 'root';
// mysql password
$password = '';
// Database Connection using PDO
try {
$dbh = new PDO("mysql:host=$hostname;dbname=signinpdo", $username, $password);
    }
catch(PDOException $e)
    {
    echo $e->getMessage();
    }
?>

CheckLogin.php page compare username and password with MySQL Members table username and password and if its successful redirects to protected page named AdminIndex.php. If username and password not authenticated and unsuccessful it will redirect user to index.php page.

Important Note: This tutorial is about sign-in, so if you want to protect passwords please use md5 function or any other way. Storing plain passwords in database might be risky.

<?php
// Start Session because we will save some values to session varaible.
session_start();
// include connection file
include("configPDO.php");
// Define $myusername and $mypassword
$UserName=$_POST['inputName']; 
$Password=$_POST['inputPassword']; 
// We Will prepare SQL Query
    $STM = $dbh->prepare("SELECT Type,Region FROM members WHERE UserName = :UserName AND Password = :Password");
// bind paramenters, Named paramenters alaways start with colon(:)
    $STM->bindParam(':UserName', $UserName);
    $STM->bindParam(':Password', $Password);
// For Executing prepared statement we will use below function
    $STM->execute();
// Count no. of records 
$count = $STM->rowCount();
//just fetch. only gets one row. So no foreach loop needed :)
$row  = $STM -> fetch();
// User Redirect Conditions will go here
 if($count==1)
 
 {
     // Save type and other information in Session for future use.
  $_SESSION[type]=$row[0];
  $_SESSION[Region]=$row[1];
  $_SESSION[myusername]=$UserName;
  
  // if user type is ACTAdmin only then he can access protected page.
  if($row[0] == 'ACTAdmin')  { header( "location:http://localhost/SimpleSignInPDO/AdminIndex.php");  }
  else    { header( "location:http://localhost/SimpleSignInPDO/index.php");  }

 }
 else 
 {
 header("location:http://localhost/SimpleSignInPDO/index.php");
 }
// Closing MySQL database connection 
    $dbh = null;
?>

For protection of pages we include UserSessionAdmin.php file at the top of each protected page.

<?php
include('UserSessionAdmin.php');
?>

Below is code for UserSessionAdmin.php. We use Type of the user in this file to protect pages.

<?php
session_start();
if($_SESSION[type]!='ACTAdmin'){
header('location:index.php');
exit();
}
include('configPDO.php');
?>

Thanks for reading. Enjoy and share with friends.

We hope you will find PHP easy sign-in using PDO prepared statement tutorial very helpful and easy. PDO (PHP Data Objects) Prepared statements used in this tutorial along with Bootstrap CSS Framework. For any query/suggestions please post a comment on ourFacebook Page.

This tutorial will show you how to make pagination easily with PDO (PHP Data Objects) in PHP. Export to Excel additional feature is also available in this tutorial.

Source

First we will create test database for pagination using below query.

--
-- Below query create PaginationTestDb Database
--
CREATE DATABASE  `PaginationTestDb` ;

Now we will create table in PaginationTestDb using below code. More sample data is available in CreateTableandDatasample.sql file which is available in Source Code.

CREATE TABLE IF NOT EXISTS `statstracker` (
  `SrNo` int(11) NOT NULL AUTO_INCREMENT,
  `ServerName` varchar(15) NOT NULL,
  `HiMemUti` double NOT NULL,
  `AvgMemUti` double NOT NULL,
  `HiCpuUti` double NOT NULL,
  `AvgCpuUti` double NOT NULL,
  `HiIOPerSec` double NOT NULL,
  `AvgIOPerSec` double NOT NULL,
  `HiDiskUsage` double NOT NULL,
  `AvgDsikUsage` double NOT NULL,
  `EntryBy` varchar(15) NOT NULL,
  `EntryDate` date NOT NULL,
  PRIMARY KEY (`SrNo`),
  UNIQUE KEY `SrNo` (`SrNo`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15;
-- Dumping data for table `statstracker`
INSERT INTO `statstracker` (`SrNo`, `ServerName`, `HiMemUti`, `AvgMemUti`, `HiCpuUti`, `AvgCpuUti`, `HiIOPerSec`, `AvgIOPerSec`, `HiDiskUsage`, `AvgDsikUsage`, `EntryBy`, `EntryDate`) VALUES
(1, '10.86.0.44', 98, 98, 98, 98, 98, 98, 98, 98, 'furqan.aziz', '2013-11-04'),
(2, '10.86.0.75', 98, 96, 97, 90, 97, 94, 98, 90, 'furqan.aziz', '2013-11-05'),
(3, '10.86.0.44', 95, 90, 92, 89, 93, 92, 94, 88, 'furqan.aziz', '2013-11-05'),
(4, '10.86.0.66', 97, 97, 93, 88, 94, 92, 93, 93, 'furqan.aziz', '2013-11-05'),
(5, '10.86.0.61', 12, 12, 12, 12, 12, 12, 12, 12, 'furqan.aziz', '2013-11-05'),
(6, '10.86.0.44', 98, 98, 98, 98, 98, 98, 98, 98, 'furqan.aziz', '2013-11-04'),
(7, '10.86.0.75', 98, 96, 97, 90, 97, 94, 98, 90, 'furqan.aziz', '2013-11-05'),
(8, '10.86.0.44', 95, 90, 92, 89, 93, 92, 94, 88, 'furqan.aziz', '2013-11-05'),
(9, '10.86.0.66', 97, 97, 93, 88, 94, 92, 93, 93, 'furqan.aziz', '2013-11-05'),
(10, '10.86.0.61', 12, 12, 12, 12, 12, 12, 12, 12, 'furqan.aziz', '2013-11-05'),
(11, '10.86.0.44', 98, 98, 98, 98, 98, 98, 98, 98, 'furqan.aziz', '2013-11-04'),
(12, '10.86.0.75', 98, 96, 97, 90, 97, 94, 98, 90, 'furqan.aziz', '2013-11-05'),
(13, '10.86.0.44', 95, 90, 92, 89, 93, 92, 94, 88, 'furqan.aziz', '2013-11-05'),
(14, '10.86.0.66', 97, 97, 93, 88, 94, 92, 93, 93, 'furqan.aziz', '2013-11-05'),
(15, '10.86.0.61', 12, 12, 12, 12, 12, 12, 12, 12, 'furqan.aziz', '2013-11-05');

In this tutorial we have following files and resources. After you have download source code and unzip source, you will create database and table using above queries or using CreateTableandDatasample.sql. then you will put PAGINATIONPDO folder inside htdocs or www root and then test in browser.

CreateDatabase.txt contains create test database query.
CreateTableandDatasample.sql contains create table query and sample insert data query.
index.php is main file which has pagination code.
ExportToExcel.php is used to export all data in excel for specific user.
img folder contains export to excel PNG file.
css folder contains pagination.css file which have pagination style, table style and export to excel position style.
css folder contains Extra.css file having more styles Thanks to MIS ALGORITMOS.

Here is index.php file code

 <!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <title>Simple Pagination Demo using PDO Query</title>
        <!-- CSS File -->
    <link href="css/pagination.css" rel="stylesheet">
  </head>
 <body>
<?php
 // mysql hostname
 $hostname = 'localhost';
 // mysql username
 $username = 'root';
 // mysql password
 $password = '';
 // Database Connection using PDO with try catch method. 
 try { $dbh = new PDO("mysql:host=$hostname;dbname=PaginationTestDb", $username, $password); }
 // In case of error PDO exception will show error message.
 catch(PDOException $e) {    echo $e->getMessage();    }
 // No. of adjacent pages shown on each side
 $adjacents = 2;
 // We will assign variable here for entry By. you can use your variables here.
 $EntryBy = "furqan.aziz";
 // We Will prepare SQL Query
    $STM = $dbh->prepare("SELECT ServerName FROM statstracker WHERE EntryBy = :EntryBy");
 // bind paramenters, Named paramenters alaways start with colon(:)
    $STM->bindParam(':EntryBy', $EntryBy);
 // For Executing prepared statement we will use below function
    $STM->execute();
 // Count no. of records
 $Records = $STM->rowCount();
 // Your File Name will be the same like your php page name which is index.php
 $targetpage = "index.php";
 // Below is setting for no. of records per page.
 $limit = 10; 
 $page = $_GET['page'];
 if($page) 
 //First Item to dipaly on this page
  $start = ($page - 1) * $limit;    
 else
 //if no page variable is given, set start to 0
  $start = 0;        
 // Get data using PDO prepare Query.
 $STM2 = $dbh->prepare("SELECT `SrNo`, `ServerName`, `HiMemUti`, `AvgMemUti`, `HiCpuUti`, `AvgCpuUti`, `HiIOPerSec`, `AvgIOPerSec`, `HiDiskUsage`, `AvgDsikUsage`, `EntryBy` FROM statstracker WHERE EntryBy = :EntryBy ORDER BY SrNo LIMIT $start, $limit");
 // bind paramenters, Named paramenters alaways start with colon(:)
    $STM2->bindParam(':EntryBy', $EntryBy);
 // For Executing prepared statement we will use below function
    $STM2->execute();
 // We will fetch records like this and use foreach loop to show multiple Results later in bottom of the page.
  $STMrecords = $STM2->fetchAll();
 // Setup page variables for display. If no page variable is given, default to 1.
 if ($page == 0) $page = 1;
 //previous page is page - 1     
 $prev = $page - 1;
 //next page is page + 1     
 $next = $page + 1;
 //lastpage is = total Records / items per page, rounded up.      
 $lastpage = ceil($Records/$limit);
 //last page minus 1 
 $lpm1 = $lastpage - 1;      
 //Now we apply our rules and draw the pagination object. We're actually saving the code to a variable in case we want to draw it more than once.
 $pagination = "";
 if($lastpage > 1)
 { 
  $pagination .= "<div class='pagination'>";
  //previous button
  if ($page > 1) 
   $pagination.= "<a href='$targetpage?page=$prev'>Previous</a>";
  else
   $pagination.= "<span class='disabled'>Previous</span>"; 
  
  //pages 
  if ($lastpage < 7 + ($adjacents * 2)) //not enough pages to bother breaking it up
  { 
   for ($counter = 1; $counter <= $lastpage; $counter++)
   {
    if ($counter == $page)
     $pagination.= "<span class='current'>$counter</span>";
    else
     $pagination.= "<a href='$targetpage?page=$counter'>$counter</a>";     
   }
  }
  elseif($lastpage > 5 + ($adjacents * 2)) //enough pages to hide some
  {
   //close to beginning; only hide later pages
   if($page < 1 + ($adjacents * 2))  
   {
    for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
    {
     if ($counter == $page)
      $pagination.= "<span class='current'>$counter</span>";
     else
      $pagination.= "<a href='$targetpage?page=$counter'>$counter</a>";     
    }
    $pagination.= "...";
    $pagination.= "<a href='$targetpage?page=$lpm1'>$lpm1</a>";
    $pagination.= "<a href='$targetpage?page=$lastpage'>$lastpage</a>";  
   }
   //in middle; hide some front and some back
   elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
   {

    $pagination.= "<a href='$targetpage?page=1'>1</a>";
    $pagination.= "<a href='$targetpage?page=2'>2</a>";
    $pagination.= "...";
    for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
    {
     if ($counter == $page)
      $pagination.= "<span class='current'>$counter</span>";
     else
      $pagination.= "<a href='$targetpage?page=$counter'>$counter</a>";     
    }
    $pagination.= "...";
    $pagination.= "<a href='$targetpage?page=$lpm1'>$lpm1</a>";
    $pagination.= "<a href='$targetpage?page=$lastpage'>$lastpage</a>";  
   }
   //close to end; only hide early pages
   else
   {
    $pagination.= "<a href='$targetpage?page=1'>1</a>";
    $pagination.= "<a href='$targetpage?page=2'>2</a>";
    $pagination.= "...";
    for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
    {
     if ($counter == $page)
      $pagination.= "<span class='current'>$counter</span>";
     else
      $pagination.= "<a href='$targetpage?page=$counter'>$counter</a>";     
    }
   }
  }
  
  //next button
  if ($page < $counter - 1) 
   $pagination.= "<a href='$targetpage?page=$next'>Next</a>";
  else
   $pagination.= "<span class='disabled'>Next</span>";
  $pagination.= "</div>\n";  
 }
 //Below is a start of table in which we will show records using foreach loop.
 echo "<table class='mytableP'>";
 // For Exporting Records to Excel we will send $EntryBy in link and will gate it on ExportToExcel page for stats for this user. 
 echo"<tr><th th colspan=11>Simple Pagination Demo using PDO Quries</div></th></tr>";
 echo"<a href='ExportToExcel.php?val=$EntryBy' target=_blank><img src='img/e2e.png' alt='Export To Excel' border='' class='e2e' /></a>";
 echo"<tr><th>Sr#</th><th>Server</th><th>Hi Memory Utilization</th><th>Avg Memory Utilization</th><th>Hi CPU Utilization</th><th>Avg CPU Utilization</th><th>Hi I/O Utilization</th><th>Avg I/O Utilization</th><th>Hi Disk Usage</th><th>Avg Disk Usage</th><th>By</th></tr>";
    // We use foreach loop here to echo records.
 foreach($STMrecords as $r)
        {
   echo "<tr>";
      echo "<td>" .$r[0] ."</td>";
         echo "<td>" .$r[1] ."</td>";
      echo "<td>" .$r[2] ."</td>";
      echo "<td>" .$r[3] ."</td>";
      echo "<td>" .$r[4] ."</td>";
      echo "<td>" .$r[5] ."</td>";
      echo "<td>" .$r[6] ."</td>";
      echo "<td>" .$r[7] ."</td>";
      echo "<td>" .$r[8] ."</td>";
      echo "<td>" .$r[9] ."</td>";
      echo "<td>" .$r[10] ."</td>";
    echo "</tr>";  
  }
 echo "</table>";
 // For showing pagination below the table we will echo $pagination here after </table>. For showing above the table we will echo $pagination before <table>
 echo $pagination;
 // Closing MySQL database connection   
    $dbh = null;
 ?>
  </body>
</html>

Below is the css Style which is used for pagination.

div.pagination {text-align:center;margin:3px;padding:3px;}
div.pagination a {margin-right:2px;border:1px solid #2C2C2C;text-decoration:none;color:#fff;background:#2C2C2C;padding:2px 5px;}
div.pagination a:hover,div.pagination a:active {border:1px solid #AAD83E;color:#FFF;background:#AAD83E;}
div.pagination span.current {margin-right:2px;border:1px solid #AAD83E;font-weight:700;background:#AAD83E;color:#FFF;padding:2px 5px;}
div.pagination span.disabled {margin-right:2px;border:1px solid #f3f3f3;color:#ccc;padding:2px 5px;}

For table we used below style code

.mytableP {border:none;width:100%;color:#666;margin:0;padding:0;}
.mytableP th {font:bold 11px Calibri,Cambria,Verdana, Arial;color:#FFFFFF;border-right:1px solid #C1DAD7;border-bottom:1px solid #C1DAD7;border-top:1px solid #C1DAD7;letter-spacing:1px;text-transform:uppercase;text-align:left;background:#99CC33 repeat;padding:2px 2px 2px 3px;}
.mytableP td {border-right:1px solid #CCC;border-bottom:1px solid #CCC;border-top:1px solid #CCC;border-left:1px solid #CCC;font:12px Calibri,Cambria,Verdana, Arial;color:#333;padding:2px 2px 2px 3px;}

For showing Export to Excel icon in right bottom of the page.

.e2e { position:fixed; bottom:0; right:0;}

For Export to Excel File please check source file and read comments inside. This tutorial is based on phpeasystep tutorial which is written using mysql_query (deprecated function). I used PDO (PHP Data Objects ) and also provide export to excel option for this along with table style. For More Tutorials please visit http://crewow.com/

Saturday, 23 November 2013

PHP Easy Sign-in Using PDO Prepared Statement Tutorial

Tuesday, 5 November 2013

Easy Pagination with PDO in PHP plus Export to Excel Tutorial