Thursday 28 November 2013

PHP MySQL Easy CSV Data Insertion in Bootstrap using PDO Tutorial

PHP MySQL Easy CSV Data Insertion in Bootstrap using PDO is a tutorial in which you will learn how to import data in MySQL Database via CSV File while using PDO (PHP Data Objects) Prepared statements. We are using Bootstrap as CSS Framework in this tutorial for creating CSV File Upload Form and other functionality. for Demo we will make excel file in the format given in sample excel file.


For Source Code Please Click Here

 Then we will save it as csv (comma separated values) file and press CSV Upload Button and choose test.csv file and Press Save CSV Button. Data will be moved in the temporary table and after upload we will redirected to index.php page where message will be shown for success or failure. In index.php it will also show your uploaded data in a table. We will learn
  • How to make a Form for CSV File Upload.
  • How to create a Temporary Table.
  • How to make a Connection Page
  • How to make an Insertion Page using PDO Queries
  • How to show a success and Warning message.
Source Code 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 upload CSV File and view results.
  • Uploadmypdo.php is file in which code of getting CSV File and Upload using PDO queries written.
  • configPDO.php is a file which will create connection with database.
  • Footer.php is a file which contains footer bar and form for CSV Upload.
  • assets folder contains necessary css, icon, images and js files.
We use following for developing PHP MySQL Easy CSV Data Insertion in Bootstrap using PDO Tutorial.
  • 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.
  1. First we will make a upload CSV button in index.php page and use data-toggle as modal and give href value as #fileupload
    • <a href="#fileupload" data-toggle="modal" class"btn btn-primary btn-large">Upload CSV</a>
      
  2. When we press upload CSV button, modal will be open which will display below form for Upload CSV. Form code is in footer.php page and its a included in index.php page.
    • <div id="fileupload" 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">Upload Data via CSV File</h3>
                  </div>
                  <div class="modal-body">
      <form class="form-horizontal" enctype="multipart/form-data" method="post" action="Uploadmypdo.php">
                  <div class="control-group">
                    <label class="control-label" for="inputName">Save CSV File</label>
                    <div class="controls">
                    <input type="hidden" name="MAX_FILE_SIZE" value="9999999" />
                    <input class="btn btn-info" name="file" type="file" id="file" onchange="showCode()" onblur="showCode()" onclick="showCode" required="required"  />
                    </div>
                  </div>
                  <div class="control-group">
                    <div class="controls">
                      <button type="submit" class="btn">Save CSV</button>
                    </div>
                  </div>
                </form>
      
          </div>
      </div>
  3. Now its time for creating sample database csvsignindb using sql.txt file which contains following table create queries and sample data for demo and test.
    • statstrackertemp table contains fields like ServerName, HiMemUti, AvgMemUti, HiCpuUti, AvgCpuUti, HiIOPerSec, AvgIOPerSec, HiDiskUsage, AvgDsikUsage.
    Table Structure for statstrackertemp Table
  4. 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=csvsignindb", $username, $password);
          }
      catch(PDOException $e)
          {
          echo $e->getMessage();
          }
      ?>
  5. For inserting CSV Data in MySQL Database using PDO (PHP Data Objects) Prepared queries we will write code like below with explanation using comments.
    • <?php 
       session_start();
      // mysql hostname
       $hostname = 'localhost';
      // mysql username
       $username = 'root';
      // mysql password
       $password = '';
      // Get Extension of the File.
       $extension= end(explode(".", basename($_FILES['file']['name'])));
      // isset Determines if a variable is set and is not NULL. Set Size Limit less then 10 MB=10485760 bytes. Extension must be CSV.
       if (isset($_FILES['file']) && $_FILES['file']['size'] < 10485760 && $extension== 'csv')
       {  
      // We will get csv file and save it in a $file
          $file = $_FILES['file']['tmp_name']; 
      //$handle is a valid file pointer to a file successfully opened by fopen(), popen(), or fsockopen(). fopen() used to open file.
          $handle = fopen($file, "r"); 
      // We will use try{} Catch() statements here.
           try { 
      // Database Connection using PDO
        $dbh = new PDO("mysql:host=$hostname;dbname=csvsignindb", $username, $password);
      // Truncate Table before insertion
        $STMR = $dbh->prepare("TRUNCATE TABLE statstrackertemp");
        $STMR->execute();
      // Prepare the statement for the insertion in the table
              $STM = $dbh->prepare('INSERT INTO statstrackertemp (ServerName, HiMemUti, AvgMemUti, HiCpuUti, AvgCpuUti, HiIOPerSec, AvgIOPerSec, HiDiskUsage, AvgDsikUsage) VALUES (?, ?, ?, ?, ?,?, ?, ?, ? )');
      //Check handel is True or False
         if ($handle !== FALSE) 
         {
      // fgets() Gets a line from file pointer and read the first line from $handle and ignore it.   
                fgets($handle);
      // While loop used here and  fgetcsv() parses the line it reads for fields in CSV format and returns an array containing the fields read.
                while (($data = fgetcsv($handle, 1000, ',')) !== FALSE)
          {
      // For Executing prepared statement we will use below function
                   $STM->execute($data);
                }       
      //The file pointed to by handle will be closed.
                fclose($handle);
          
      // Closing MySQL database connection
             $dbh = null; 
      // If data inserted successfully we will redirect this page to index.php and show success message there with code 77083368
          header( "location:index.php?ServerStatsAdded=77083368"); 
          
            
         }
      
           }
      // Exceptions error will be thrown if Database not connected. 
        catch(PDOException $e)
        {
              die($e->getMessage());
           }
      
      
       }
       else 
       {
      // Error mesage id File type is not CSV or File Size is greater then 10MB.
          header( "location:index.php?ServerStatsAdded=37767");
       }
      ?>
  6. After processing of CSV File it will redirected to index.php page where we will show success of failure message using codes return from Uploadmypdo.php
    • if($_GET["ServerStatsAdded"]==77083368)  
      {
      echo "<div class='alert alert-success'>"; 
      echo "<button type='button' class='close' data-dismiss='alert'>×</button>"; 
      echo "Server Stats added in System."; 
      echo "</div>";
      }
      if($_GET["ServerStatsAdded"]==37767)  
      {
      echo "<div class='alert alert-block'>"; 
      echo "<button type='button' class='close' data-dismiss='alert'>×</button>";
      echo "<h4>Warning!</h4>"; 
      echo "Many Reasons for not adding stats. it can be wrong file format, file size greater then 10 MB, data already in system."; 
      echo "</div>"; 
      }
    Thanks for reading. Enjoy and share with friends.
We hope you will find PHP MySQL Easy CSV Data Insertion in Bootstrap using PDO Tutorial very helpful and easy. PDO (PHP Data Objects) Prepared statements used in this tutorial along with Bootstrap CSS Framework for better user experience. For any query/suggestions please post a comment on our Facebook Page.

Saturday 23 November 2013

PHP Easy Sign-in Using PDO Prepared Statement Tutorial


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.
  1. 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>
      
  2. 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>
  3. 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.
    Table Structure for Members Table
  4. 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();
          }
      ?>
  5. 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;
      ?>
  6. For protection of pages we include UserSessionAdmin.php file at the top of each protected page.
    • <?php
      include('UserSessionAdmin.php');
      ?>
  7. 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.

Thursday 14 November 2013

Freebie Complete PHP Project Bootstrap PDO Sign-In Jquery UI Autocomplete Validation


It is a freebie complete project for PHP beginners using PDO (PHP Data Objects) queries, Bootstrap, MySQL Database, JQuery UI, Jquery Validation Plug-in with full source code. Bootstrap CSS framework is used along with JQuery UI Autocomplete. For password change match password option is also good for users they want to design a sign up form. In server stats insertion form only double number allowed using regular expression. Jquery Validation plug-in used in this freebie project which works fine in all major browsers. It is a complete system , you can improve this and share with others. Enjoy it!
Click here for Details

Friday 8 November 2013

Easy PHP Email With Attachments using Swift Mailer Tutorial


This tutorial is about sending email in PHP using Swift Mailer which is flexible and elegant object-oriented approach to send emails with a multitude of features. You can also attach files as Attachments. Enjoy it.
  1. Swift Mailer integrates into any web app written in PHP 5, offering a flexible and elegant object-oriented approach to sending emails with a multitude of features.
    • Send emails using SMTP, sendmail, postfix or a custom Transport implementation of your own.
    • Support servers that require username & password and/or encryption
    • Protect from header injection attacks without stripping request data content
    • Send MIME compliant HTML/multipart emails
    • Use event-driven plugins to customize the library
    • Handle large attachments and inline/embedded images with low memory use
  2. We will include swift mailer using below code.
    • // Swift Mailer library will be required. lib folder is available in source code.
      require_once 'lib/swift_required.php';
  3. Now we will include our connection configuration file like this if we want to include dynamic data from MySQL.
    • // For Dynamic contents we will add our config File
      include("configPDO.php");
      
  4. You will give SMTP Transport method here and provide details using below code for authentication.
    • // Create the Transport. we can put ip instead of smtp.example.org if we are on intranet.
      $transport = Swift_SmtpTransport::newInstance('smtp.example.org', 25)
        ->setUsername('your username')
        ->setPassword('your password')
        ;
  5. You could alternatively use a different transport such as Sendmail or Mail if required.
    • // Sendmail
      $transport = Swift_SendmailTransport::newInstance('/usr/sbin/sendmail -bs');
      // Mail
      $transport = Swift_MailTransport::newInstance();
  6. Now we will create the Mailer using created trasnport. please see below code, It will use later for sending message.
    • // Create the Mailer using your created Transport
      $mailer = Swift_Mailer::newInstance($transport);
  7. Now we will start creating our message. first we will choose our mesage subject.
    • // Subject of the Message
      $message = Swift_Message::newInstance('Subject is Crewow Free Web Tutorials');
  8. Sender Address will be written like this.
    • // Address of Sender will be written in setFrom Function
      $message->setFrom(array('info@crewow.com' => 'Furqan Aziz'));
  9. Receiver emails will be written like this.
    • // Address of the recipients will be written in setTo 
      $message->setTo(array('receiver@domain.org', 'other@domain.org' => 'A name'));
  10. We can add CC and BCC for this message like the below code.
    • // You can add CC like this.
      $message->addCc('info@crewow.com');
      // You can add BCC like this.
      $message->addbCc('someone@crewow.com');
  11. For Dynamic data we can either use query or variables which can be set.
    • // For Dynamic contents you will add your config File
      include("configPDO.php");
  12. Here is sample for just one variable which we will use in body of the message.
    • // Define $field1 and $field2 for named placeholders
      $field1= "Center"; 
      $field2="Active"; 
      We can use query to get results from MySQL Database using prepared query with PDO. Here its for $CToday only
      $STM = $dbh->prepare("SELECT SrNo FROM YourTable WHERE field1 = :field1 AND field2 = :field2");
      // bind paramenters, Named paramenters alaways start with colon(:)
      $STM->bindParam(':field1', $field1);
      $STM->bindParam(':field2', $field2);
      // For Executing prepared statement we will use below function
      $STM->execute();
      // Count no. of records
      $CToday = $STM->rowCount();
    OR
  13. We can set all variables manually or get them using POST method from some form.
    • // We can set variables like this
      $NToday = 200;  //North Today
      $CToday = 300;  //Central Today
      $SToday = 400;  //South Today
      $TotalToday = $NToday + $CToday + $SToday;  //Total Today
  14. Here is example of setting body for simplest message
    • // For Simple message we do like this
      $message->setBody('Here is the message itself');
    OR
  15. We can use HTML and CSS Style to make it attactive and cool
    • // For HTML/PHP message you can do it like this
      $message->setBody(
      '<html>' .
      ' <head><style type=text/css>
      * { margin: 0; padding: 0; }
      body { font: 14px Georgia, serif; }
      
      #page-wrap { width: 1024px; margin: 0 auto; }
      
      table { border-collapse: collapse; width: 100%; }
      td, th { border: 1px solid #0099FF; padding: 10px; }
      
      .slim { width: 88px; background-color: #eee;  }
      .hover { background-color: #eee; }
      </style></head>' .
      ' <body>' .
      ' <label>Dear Crewow Team,</label><br />
      <br />
      Here is our new Tutorial. please check below link after the table<br />
      <br /> <table>
      
       <tr>
       <td class=slim align=center ><strong>Region</strong></td>
          <td class=slim align=center ><strong>Today</strong></td>
       </tr>
       
           <tr>
          <td align=center>North</td>
       <td align=center>'.$NToday.'</td>
       </tr>
        
        <tr>
          <td align=center>Central</td>
       <td align=center>'.$CToday.'</td>
       </tr>
       
          <tr>
          <td align=center>South</td>
       <td align=center>'.$SToday.'</td>
       </tr>
          <tr>
          <td align=center><strong>Grand Total</strong></td>
       <td align=center><strong>'.$TotalToday.'</strong></td>
       </tr>
      </table>
      <br />
      <span style=color:#9966FF:><b><a href=http://crewow.com/>Crewo Website</a></b></span>
      <br /> 
      Best Regards<br />
      <hr>
      CreWow Network | www.crewow.com <br />
      
      <br />' .
      ' </body>' .
      '</html>',
        'text/html' //Mark the content-type as HTML
      );
  16. We can attach files using below code. please read swift mailer documentation for more details.
    • //Below code can be used to attached excel. you can attach any file you want like pdf zip or any other. 
      $message->attach(Swift_Attachment::fromPath('Excel/SampeFile.xls'));
  17. Finally we will send email message via created mailer like this.
    • // Send the message
      $result = $mailer->send($message);
You can use Swift mailer for sending emails. For any query/suggestions please post a comment on our Facebook Page.