Monday 28 October 2013

PHP PDO to Connect MySQL Database Tutorial

This tutorial will show you how to connect to MySQL Database using PDO (PHP Data Objects) which defines a lightweight, consistent interface for accessing databases in PHP.

Website : Crewow Website




  1. First we will create sample table. Below is a code for Sample MySQL table named category, you can copy it and paste it in your query window inside database say shopdb and run the query to create table.
    • --
      -- Table structure for table `category`
      --
      CREATE TABLE IF NOT EXISTS `category` (
        `SrNo` int(11) NOT NULL AUTO_INCREMENT,
        `Category` varchar(50) NOT NULL,
        `SubCategory` varchar(100) NOT NULL,
        PRIMARY KEY (`SrNo`)
      ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;
      --
      -- Dumping data for table `category`
      --
      INSERT INTO `category` (`SrNo`, `Category`, `SubCategory`) VALUES
      (1, 'Electronics', 'Mobile'),
      (2, 'Electronics', 'Tablet'),
      (3, 'Electronics', 'IPad'),
      (4, 'Electronics', 'ITab'),
      (5, 'Electronics', 'Television'),
      (6, 'Electronics', 'Camera'),
      (7, 'Electronics', 'Laptop'),
      (8, 'Electronics', 'LCD'),
      (9, 'Electronics', 'Computer'),
      (10, 'Books', 'Magazines'),
      (11, 'Books', 'Journals'),
      (12, 'Books', 'Course Books'),
      (13, 'Garments', 'Kids Clothes');
  2. Now its time to connect to MySQL Database using PHP Data Objects. Create new PHP File and write following lines for connection to MySQL Database.
    • <?php
      // mysql hostname
      $hostname = 'localhost';
      // mysql username
      $username = 'username';
      // mysql password
      $password = 'password';
      // Database Connection using PDO
      $dbh = new PDO("mysql:host=$hostname;dbname=shopdb", $username, $password);
      // $dbh is a Database Handle
      // new is to create PDO Object
      // mysql is a Database type
      // host=$hostname;dbname=shopdb",$username,$password is a connection string
      // Closing MySQL database connection
          $dbh = null;
      ?>
  3. If there are any connection errors, a PDOException object will be thrown. You can catch the exception if you want to handle the error condition. we will use try/catch statement.
    • <?php
      // mysql hostname
      $hostname = 'localhost';
      // mysql username
      $username = 'username';
      // mysql password
      $password = 'password';
      // Database Connection using PDO
      try {
      $dbh = new PDO("mysql:host=$hostname;dbname=shopdb", $username, $password);
      // Closing MySQL database connection
          $dbh = null;
          }
      catch(PDOException $e)
          {
          echo $e->getMessage();
          }
      //If we will not use catch statement, then in case of error zend engine terminate the script and display a back trace. This back trace will likely reveal the full database connection details, including the username and password.  
      ?>
  4. The connection remains active for the lifetime of that PDO object. To close the connection, you will assign NULL to the variable($dbh) that holds the object.
    • <?php
      // Closing MySQL database connection
          $dbh = null;  
      ?>
  5. Now we will Select data from MYSQL Database using prepared statement. These statements helps to protect from SQL Injection. There are two methods to use prepared statements. one is named and second in unnamed. we will use one of them here.
    • <?php
      // mysql hostname
      $hostname = 'localhost';
      // mysql username
      $username = 'username';
      // mysql password
      $password = 'password';
      // Database Connection using PDO
      try {
      $dbh = new PDO("mysql:host=$hostname;dbname=shopdb", $username, $password);
      // Define Variables
          $SrNo = 6;
          $Category = 'Electronics';
      // We Will prepare SQL Query
          $STM = $dbh->prepare("SELECT * FROM category WHERE SrNo = :SrNo AND Category = :Category");
      // bind paramenters, Named paramenters alaways start with colon(:)
          $STM->bindParam(':SrNo', $SrNo);
          $STM->bindParam(':Category', $Category);
      // For Executing prepared statement we will use below function
          $STM->execute();
      // we will fetch records like this and use foreach loop to show multiple Results
          $STMrecords = $STM->fetchAll();
          foreach($STMrecords as $row)
              {
              echo $row['SrNo'].'-'.$row['Category'].'-'.$row['SubCategory'];
              }
      // Closing MySQL database connection   
          $dbh = null;
          }
      catch(PDOException $e)
          {
          echo $e->getMessage();
          }  
      ?>
You can also check Connections and Connection management for more details about PDO Connection.

Best CSS Frameworks Collection For Rapid Web Development

This collection contains best Frameworks are in use now a days. 
Now you have to choose one for designing your website in very quick time.
 All these frameworks are free for use. Enjoy!


For Detail Tutorial please visit : Link

Friday 18 October 2013

PHP-MySQL Ajax Jquery-UI Autocomplete in Bootstrap Tutorial


This tutorial will show you how to use Jquery UI Autocomplete in Bootsrap via PHP, MySQL and AJAX. You can use it anywhere else too easily.
  1. You will learn to make Jquery UI autocomplete working in Bootstrap via PHP, MySQL and Ajax.
  2. First create a simple form using below code or you can use your own search code. Please note id of input is searchg which will use later.
    • <form  class="navbar-form pull-left">
      <input class="span4" id="searchg" type="text" placeholder="Search">
      <button class="btn" type="button">Search</button>
      </form>
      
  3. Now Download JQuery UI from here and unzip it and put it in assets folder of bootstrap or anywhere you want. and put following lines before the </body> tag
    • <!--Jquery UI required links-->
      <script src="assets/js/jquery.js"></script>
      <script src="assets/ui/jquery.ui.core.js"></script>
      <script src="assets/ui/jquery.ui.widget.js"></script>
      <script src="assets/ui/jquery.ui.position.js"></script>
      <script src="assets/ui/jquery.ui.autocomplete.js"></script>
  4. Now put following Jquery UI css link before the </head> tag.
    • <!--Jquery UI css link-->
      <link rel="stylesheet" href="assets/ui/themes/ui-darkness/jquery.ui.all.css">
      
  5. Under Jquery UI required links you will write following code for showing autocomplete on input. We will get data from php page Get_Categories.php using JSON and use searchg id.
    • <script type="text/javascript">
      $(document).ready(function()
      {
                $.ajax({
                     url: 'Get_Categories.php',
                     type: 'POST',
                     dataType: 'json',
                     success: function(data){
                           $('#searchg').autocomplete(
                           {
                                 source: data,
                                 minLength: 1   
                           });
                     }
                });  
      });
      </script>
  6. Now on Get_Categories.php page you will write following code to get results from database and output JSON String.
    • <?php
      /*** mysql hostname ***/
      $hostname = 'localhost';
      /*** mysql username ***/
      $username = 'username';
      /*** mysql password ***/
      $password = 'password';
      // Database Connection
      $dbh = new PDO("mysql:host=$hostname;dbname=mysql", $username, $password);
      // Query to get the usable locations
          $locale = $_GET['term'];
      $sql = "SELECT DISTINCT Category FROM `category` WHERE `Category` LIKE '%$locale%'";
      // Deifne array for products
       $Product_array = array();
         foreach ($dbh->query($sql) as $row)
              {
              $result = $row[0];
         // use arrray_push to store results
        array_push($Product_array, $result);
              }
      // put files in $jason and then echo it for getting in your page using Ajax.        
      $json = json_encode($Product_array);
      echo $json; 
      ?>
  7. Below is a code for MySQL Table, you can copy it and paste it in your query window inside database.
    • --
      -- Table structure for table `category`
      --
      CREATE TABLE IF NOT EXISTS `category` (
        `SrNo` int(11) NOT NULL AUTO_INCREMENT,
        `Category` varchar(50) NOT NULL,
        `SubCategory` varchar(100) NOT NULL,
        PRIMARY KEY (`SrNo`)
      ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;
      --
      -- Dumping data for table `category`
      --
      INSERT INTO `category` (`SrNo`, `Category`, `SubCategory`) VALUES
      (1, 'Electronics', 'Mobile'),
      (2, 'Electronics', 'Tablet'),
      (3, 'Electronics', 'IPad'),
      (4, 'Electronics', 'ITab'),
      (5, 'Electronics', 'Television'),
      (6, 'Electronics', 'Camera'),
      (7, 'Electronics', 'Laptop'),
      (8, 'Electronics', 'LCD'),
      (9, 'Electronics', 'Computer'),
      (10, 'Books', 'Magazines'),
      (11, 'Books', 'Journals'),
      (12, 'Books', 'Course Books'),
      (13, 'Garments', 'Kids Clothes');
Above tutorial is very useful for making autocomple function in web pages. You can also use some features of this tutorials. For more information please visit Crewow Website

Thursday 17 October 2013

PHP Simple Sign-in Tutorial



This tutorial is about simple sign-in php code for beginners. You can also protect pages from unauthorized persons.
  1. You will learn to make one database table, one form , checking php page, user session page, config page and secret page.
  2. First create a simple form using below code or you can use your own sign in form.
    • <form  method="post" action="CheckLogin.php">
      <input type="email" name="inputEmail"  id="inputEmail" placeholder="Email">
      <input type="password" name="inputPassword" id="inputPassword" placeholder="Password" maxlength="15" minlength="6"  >
      <button type="submit" >Sign in</button>
      </form>
  3. Now you will run below SQL Code to make a simple table in your database.
    • CREATE TABLE IF NOT EXISTS `members` (
      `id` int(4) NOT NULL AUTO_INCREMENT,
      `name` varchar(65) NOT NULL DEFAULT '',
      `email` varchar(65) NOT NULL DEFAULT '',
      `password` varchar(65) NOT NULL DEFAULT '',
      `Type` varchar(10) NOT NULL,
      PRIMARY KEY (`id`)
      ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
  4. Now its time to connect to the database, so we will make database connection file config.php
    • <?php
      $con = mysql_connect("localhost","root","yourpaswword");
      if (!$con)
      {
      die('Could not connect: ' . mysql_error());
      }
      mysql_select_db("YourDbName", $con);
      ?>
  5. Now when form submitted it will go to CheckLogin.php page and here is code for page.
    • <?php
      // Start Session because we will save some values to session varaible.
      session_start();
      ob_start();
      // include config file for php connection
       include("config.php");
      // memebers table name
      $tbl_name="members";
      // Define $myusername and $mypassword
      $myusername=$_POST['inputEmail']; 
      $mypassword=$_POST['inputPassword']; 
      // To protect MySQL injection
      $myusername = stripslashes($myusername);
      $mypassword = stripslashes($mypassword);
      $myusername = mysql_real_escape_string($myusername);
      $mypassword = mysql_real_escape_string($mypassword);
      below query will check username and password exists in system or not
      $sql="SELECT * FROM $tbl_name WHERE email='$myusername' AND password='$mypassword'";
      $result=mysql_query($sql);
      // Mysql_num_row is used for counting table records
      $count=mysql_num_rows($result);
      // If result matched $myusername and $mypassword, table record must be equal to 1  
      if($count==1)
      {
       $sql2=mysql_query("SELECT Type,email,name FROM $tbl_name WHERE email='$myusername'");
       $row=mysql_fetch_row($sql2);
       $_SESSION[type]=$row[0];
       $_SESSION[myusername]=$row[1];
       $_SESSION[name]=$row[2];
      //Depending on type of user we will redirect to various pages  
       if($row[0] == 'abc')  { header( "location:http://localhost/abc.php");  }
       else if($row[0] == 'xyz')  { header( "location:http://localhost/xyz.php");  }
       else if($row[0] == 'Admin')  { header( "location:http://localhost/admin.php");  }
       else    {   header( "location:http://localhost/index.php");  }
      }
      else
      {
       header("location:http://localhost/index.php");
      }
      ob_end_flush();
      ?>
  6. Now on user pages like abc.php, xyz.php or admin.php your will write below lines on top of the page to make them protected.
    • <?php
      include('UserSession_xyz.php');
      ?>
  7. Below is code for UserSession_xyz.php. you will also make other pages for other users.
    • <?php
      session_start();
      if($_SESSION[type]!='xyz'){
      header('location:index.php');
      exit();
      }
      include('config.php');
      ?>
Above tutorial is very useful for making websites pages secure and make user Sing-In System. You can also use some features of this tutorials. For more information please visit Website: Crewow

Monday 14 October 2013

Jquery Raty Usage via PHP

JQuery Raty is a best star rating plug-in. it can be used easily with PHP via Jquery Post. This tutorial will show how to do it. Enjoy!

  1. You need to download JQuery Raty from their website. JQury is also required for this plug-in.
  2. Now Create folder name js and put jquery raty minified version inside it and put following link on your page under jquery. Also you have to put all images of stars inside img folder.
    •     <!--For Raty-->
          <script type="text/javascript" src="js/jquery.raty.min.js"></script>
  3. Now you will show stars for any image, just write brlow line under any image. here p1236 is an id for product.
    •  <div id= "p1236 " class= "star "> </div>
  4. Now you will write below code to activate JQuery Raty inside <script type="text/javascript"> </script> tags.
    • $(function() {
      //Below is a path to img folder.
            $.fn.raty.defaults.path = 'js/img';  
      //Below will activate Raty on div where class is star.   
            $('.star').raty({
       half  : true,
              number: 5,
              score : 0,
              click: function(score, evt) {
      //Below will get id value and store in variable pid    
        var pid=$(this).prop('id');
      //Below will post score and id value to raty1.php page behind the scenes. 
            $.post('raty1.php',{score:score, pid:pid},
                          function(data){
                             
                  });
                }
           });
      });
  5. Now on Rat1.php page you can get values of score and id using following code. you can also write mysql query to save these values in database.
    • <?php
      $score = $_POST['score'];
      $pid = $_POST['pid'];
      ?>
Above tutorial is very useful for making websites which require rating function. You can also use some features of this tutorials. For more information please visit Crewow Website