Saturday, 15 March 2014

On change query the database using JQUERY - PHP

This is a simple demo to show how to fetch values from database without page refresh in JQUERY / PHP

 Minimal data is provided in the database , since this is just a  demo.

1. Create our database -- ('onselect') [My database name] {You can use your name}
2. Create our first table -- ('country') (id,name)
3. Create our second table states -- ('state') (id,country_id,state) [country_id in this case is the foreign key]
4. Create our third table city -- ('city') (id,state_id,city) [state_id is this case is the foreign key]

And Then add some demo values to all the tables....and you are almost done..

//////////////////////// sql for country table
-- phpMyAdmin SQL Dump
-- version 4.0.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Mar 15, 2014 at 08:56 AM
-- Server version: 5.1.30-community
-- PHP Version: 5.4.16

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `onselect`
--

-- --------------------------------------------------------

--
-- Table structure for table `country`
--

CREATE TABLE IF NOT EXISTS `country` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `country`
--

INSERT INTO `country` (`id`, `name`) VALUES
(1, 'India'),
(2, 'Bangladesh'),
(3, 'Pakistan'),
(4, 'China');

///////////////////// sql for our states table

-- phpMyAdmin SQL Dump
-- version 4.0.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Mar 15, 2014 at 09:01 AM
-- Server version: 5.1.30-community
-- PHP Version: 5.4.16

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `onselect`
--

-- --------------------------------------------------------

--
-- Table structure for table `state`
--

CREATE TABLE IF NOT EXISTS `state` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `country_id` int(11) NOT NULL,
  `state` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

--
-- Dumping data for table `state`
--

INSERT INTO `state` (`id`, `country_id`, `state`) VALUES
(1, 1, 'West Bengal'),
(2, 1, 'Maharastra'),
(3, 2, 'Chitagong'),
(5, 2, 'Dhaka'),
(6, 2, 'Sylhet'),
(7, 3, 'Balochistan'),
(8, 3, 'Khyber-Pakhtunkhwa'),
(9, 4, 'Beijing'),
(10, 4, 'Shanxi Province');


////////////////////////////////// sql for city table

-- phpMyAdmin SQL Dump
-- version 4.0.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Mar 15, 2014 at 09:03 AM
-- Server version: 5.1.30-community
-- PHP Version: 5.4.16

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `onselect`
--

-- --------------------------------------------------------

--
-- Table structure for table `city`
--

CREATE TABLE IF NOT EXISTS `city` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `state_id` int(11) NOT NULL,
  `city` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=22 ;

--
-- Dumping data for table `city`
--

INSERT INTO `city` (`id`, `state_id`, `city`) VALUES
(1, 1, 'Kolkata'),
(2, 1, 'Medinipur'),
(3, 5, 'Rajshahi'),
(4, 5, 'Gazipur'),
(5, 6, 'Sunamganj'),
(6, 6, 'Angajur'),
(7, 2, 'Mumbai'),
(8, 2, 'Pune'),
(9, 2, 'Nagour'),
(10, 2, 'Thane'),
(11, 1, 'Asansol'),
(12, 1, 'Kharagpur'),
(13, 7, 'Quetta'),
(14, 7, 'Khuzdar'),
(15, 8, 'Peshawar'),
(16, 8, 'Abbottabad'),
(17, 8, 'Mingora'),
(18, 9, 'Dongcheng District'),
(19, 9, 'Xicheng District'),
(20, 10, 'Xinghualing District'),
(21, 10, 'Chengqu District');
------------------------------------------------------------------------------------------------------

Now lets start coding for our PHP

 This is our index.php page

 <?php
  require_once 'conn.php';
  include 'nav_func.php';
  $errors=array();
  if(isset($_POST['ADD']))
{
 $par = addslashes(trim($_POST['par']));
 //// checking for only alphbets in php
  if(!empty($par))
  {

   if(only_alpha($par) == true)

    {
      if(min_length($par) == true)
{
     $errors[]=insert_par(0,$par);
}
else{
 $errors[]="<font color='red'>Sorry!! Menu should be atleast 4 characters long</font>";
}
   }
  else{
     $errors[] ="<font color='red'>Only characters are allowed</font>";
   }
 
  }
else{
  $errors[] ="<font color='red'>Enter your desired menu name</font>";
}
 }


 if(isset($_POST['Sub']))
{

  $parent  = $_POST['cat_id'];
  $sub_cat = addslashes(trim($_POST['sub_cat']));

if(!empty($sub_cat))
  {

   if(only_alpha($sub_cat) == true)

    {
  if(min_length($sub_cat) == true)
{
    $errors[]=insert_par($parent,$sub_cat);
}
else{
$errors[]="<font color='red'>Sorry!! Menu should be atleast 4 characters long</font>";
   }
    }
  else{
    $errors[] ="<font color='red'>Only characters are allowed for subcategory</font>";
   }
 
  }
else{
  $errors[] ="<font color='red'>Enter your desired subcategory name</font>";
}

}
?>
<!doctype>
<html>
  <head>
  <title>
     Dynamic Nav Menu From Database
  </title>
<script language="Javascript" type="text/javascript">

        function onlyAlphabets(e, t) {
            try {
                if (window.event) {
                    var charCode = window.event.keyCode;
                }
                else if (e) {
                    var charCode = e.which;
                }
                else { return true; }
                if ((charCode > 64 && charCode < 91) || (charCode > 96 && charCode < 123))
                    return true;
                else
                    return false;
            }
            catch (err) {
                alert(err.Description);
            }
        }

    </script>
  <style>
   body {
  font-family: 'Lucida Grande', 'Helvetica Neue', Helvetica, Arial, sans-serif;
  padding: 20px 50px 150px;
  font-size: 13px;
  text-align: center;
  background: #E3CAA1;
}

ul {
  text-align: left;
  display: inline;
  margin: 0;
  padding: 15px 4px 17px 0;
  list-style: none;
  -webkit-box-shadow: 0 0 5px rgba(0, 0, 0, 0.15);
  -moz-box-shadow: 0 0 5px rgba(0, 0, 0, 0.15);
  box-shadow: 0 0 5px rgba(0, 0, 0, 0.15);
}
ul li {
  font: bold 12px/18px sans-serif;
  display: inline-block;
  margin-right: -4px;
  position: relative;
  padding: 15px 20px;
  background: #fff;
  cursor: pointer;
  -webkit-transition: all 0.2s;
  -moz-transition: all 0.2s;
  -ms-transition: all 0.2s;
  -o-transition: all 0.2s;
  transition: all 0.2s;
}
ul li:hover {
  background: #555;
  color: #fff;
}
ul li ul {
  padding: 0;
  position: absolute;
  top: 48px;
  left: 0;
  width: 150px;
  -webkit-box-shadow: none;
  -moz-box-shadow: none;
  box-shadow: none;
  display: none;
  opacity: 0;
  visibility: hidden;
  -webkit-transiton: opacity 0.2s;
  -moz-transition: opacity 0.2s;
  -ms-transition: opacity 0.2s;
  -o-transition: opacity 0.2s;
  -transition: opacity 0.2s;
}
ul li ul li {
  background: #555;
  display: block;
  color: #fff;
  text-shadow: 0 -1px 0 #000;
}
ul li ul li:hover { background: #666; }
ul li:hover ul {
  display: block;
  opacity: 1;
  visibility: visible;
}
h3{
font-family:Baskerville;
  font-size:16px;
  margin-bottom:20px;
  background-color:red;
  color:#fff;
}
header{
  font-family:Baskerville;
  font-size:40px;
  margin-bottom:20px;
  background-color:red;
  color:#fff;
}
footer{

  font-family:Baskerville;
  font-size:18px;
  margin-top:100px;
  background-color:red;
  color:#fff;
}
.links{
  width:800px;
  margin-top:80px;
  margin-bottom:60px;
}
#top{
float:left;
width:100%;

}
#left{
margin-top:50px;
float:left;
width:300px;

}
#right{
width:200px;
margin-top:50px;
border-left:2px solid green;
}
.selection{
float:left;
margin-top:20px;
font-family:baskervile;
font-size:16px;
 width:800px;

  display:none;
}
.bold{
  color:green;
  font-weight:bold;
}
  </style>
  <script language="Javascript" type="text/javascript">

        function onlyAlphabets(e, t) {
            try {
                if (window.event) {
                    var charCode = window.event.keyCode;
                }
                else if (e) {
                    var charCode = e.which;
                }
                else { return true; }
                if ((charCode > 64 && charCode < 91) || (charCode > 96 && charCode < 123))
                    return true;
                else
                    return false;
            }
            catch (err) {
                alert(err.Description);
            }
        }

    </script>

  </head>
  <body>
  <header>On change query the database using JQUERY</header>
  <center>
<div class='links'>
<center><h3>On Change query database using Jquery</h3></center>

<table id='top'>
<tr><th  align='left'>Select Country:- <?php get_country(); ?></th>
<th  align='left'>Select State:- <select name='state' id='state' disabled><option>Select</option></select></th>
<th  align='left'>Select City:-  <select name='city'  id='city' disabled><option>Select</option></select></th>
</tr>


</table>

<div class='selection'>
You Selection &raquo;
<span class='con'></span>
<span class='sta'></span>
<span class='cit'></span>
</div>

<div class='mid'>
<br/>
<br/>
<br/>

</center>
</div>
<footer>&copy - 2014 .php4allu.blogspot.com production</footer>
<script src='http://code.jquery.com/jquery-1.11.0.min.js'></script>
<script src='myjs.js'></script>
</body>

</html><?php
  require_once 'conn.php';
  include 'nav_func.php';
  $errors=array();
  if(isset($_POST['ADD']))
{
 $par = addslashes(trim($_POST['par']));
 //// checking for only alphbets in php
  if(!empty($par))
  {

   if(only_alpha($par) == true)

    {
      if(min_length($par) == true)
{
     $errors[]=insert_par(0,$par);
}
else{
 $errors[]="<font color='red'>Sorry!! Menu should be atleast 4 characters long</font>";
}
   }
  else{
     $errors[] ="<font color='red'>Only characters are allowed</font>";
   }
 
  }
else{
  $errors[] ="<font color='red'>Enter your desired menu name</font>";
}
 }


 if(isset($_POST['Sub']))
{

  $parent  = $_POST['cat_id'];
  $sub_cat = addslashes(trim($_POST['sub_cat']));

if(!empty($sub_cat))
  {

   if(only_alpha($sub_cat) == true)

    {
  if(min_length($sub_cat) == true)
{
    $errors[]=insert_par($parent,$sub_cat);
}
else{
$errors[]="<font color='red'>Sorry!! Menu should be atleast 4 characters long</font>";
   }
    }
  else{
    $errors[] ="<font color='red'>Only characters are allowed for subcategory</font>";
   }
 
  }
else{
  $errors[] ="<font color='red'>Enter your desired subcategory name</font>";
}

}
?>
<!doctype>
<html>
  <head>
  <title>
     Dynamic Nav Menu From Database
  </title>
<script language="Javascript" type="text/javascript">

        function onlyAlphabets(e, t) {
            try {
                if (window.event) {
                    var charCode = window.event.keyCode;
                }
                else if (e) {
                    var charCode = e.which;
                }
                else { return true; }
                if ((charCode > 64 && charCode < 91) || (charCode > 96 && charCode < 123))
                    return true;
                else
                    return false;
            }
            catch (err) {
                alert(err.Description);
            }
        }

    </script>
  <style>
   body {
  font-family: 'Lucida Grande', 'Helvetica Neue', Helvetica, Arial, sans-serif;
  padding: 20px 50px 150px;
  font-size: 13px;
  text-align: center;
  background: #E3CAA1;
}

ul {
  text-align: left;
  display: inline;
  margin: 0;
  padding: 15px 4px 17px 0;
  list-style: none;
  -webkit-box-shadow: 0 0 5px rgba(0, 0, 0, 0.15);
  -moz-box-shadow: 0 0 5px rgba(0, 0, 0, 0.15);
  box-shadow: 0 0 5px rgba(0, 0, 0, 0.15);
}
ul li {
  font: bold 12px/18px sans-serif;
  display: inline-block;
  margin-right: -4px;
  position: relative;
  padding: 15px 20px;
  background: #fff;
  cursor: pointer;
  -webkit-transition: all 0.2s;
  -moz-transition: all 0.2s;
  -ms-transition: all 0.2s;
  -o-transition: all 0.2s;
  transition: all 0.2s;
}
ul li:hover {
  background: #555;
  color: #fff;
}
ul li ul {
  padding: 0;
  position: absolute;
  top: 48px;
  left: 0;
  width: 150px;
  -webkit-box-shadow: none;
  -moz-box-shadow: none;
  box-shadow: none;
  display: none;
  opacity: 0;
  visibility: hidden;
  -webkit-transiton: opacity 0.2s;
  -moz-transition: opacity 0.2s;
  -ms-transition: opacity 0.2s;
  -o-transition: opacity 0.2s;
  -transition: opacity 0.2s;
}
ul li ul li {
  background: #555;
  display: block;
  color: #fff;
  text-shadow: 0 -1px 0 #000;
}
ul li ul li:hover { background: #666; }
ul li:hover ul {
  display: block;
  opacity: 1;
  visibility: visible;
}
h3{
font-family:Baskerville;
  font-size:16px;
  margin-bottom:20px;
  background-color:red;
  color:#fff;
}
header{
  font-family:Baskerville;
  font-size:40px;
  margin-bottom:20px;
  background-color:red;
  color:#fff;
}
footer{

  font-family:Baskerville;
  font-size:18px;
  margin-top:100px;
  background-color:red;
  color:#fff;
}
.links{
  width:800px;
  margin-top:80px;
  margin-bottom:60px;
}
#top{
float:left;
width:100%;

}
#left{
margin-top:50px;
float:left;
width:300px;

}
#right{
width:200px;
margin-top:50px;
border-left:2px solid green;
}
.selection{
float:left;
margin-top:20px;
font-family:baskervile;
font-size:16px;
 width:800px;

  display:none;
}
.bold{
  color:green;
  font-weight:bold;
}
  </style>
  <script language="Javascript" type="text/javascript">

        function onlyAlphabets(e, t) {
            try {
                if (window.event) {
                    var charCode = window.event.keyCode;
                }
                else if (e) {
                    var charCode = e.which;
                }
                else { return true; }
                if ((charCode > 64 && charCode < 91) || (charCode > 96 && charCode < 123))
                    return true;
                else
                    return false;
            }
            catch (err) {
                alert(err.Description);
            }
        }

    </script>

  </head>
  <body>
  <header>On change query the database using JQUERY</header>
  <center>
<div class='links'>
<center><h3>On Change query database using Jquery</h3></center>

<table id='top'>
<tr><th  align='left'>Select Country:- <?php get_country(); ?></th>
<th  align='left'>Select State:- <select name='state' id='state' disabled><option>Select</option></select></th>
<th  align='left'>Select City:-  <select name='city'  id='city' disabled><option>Select</option></select></th>
</tr>


</table>

<div class='selection'>
You Selection &raquo;
<span class='con'></span>
<span class='sta'></span>
<span class='cit'></span>
</div>

<div class='mid'>
<br/>
<br/>
<br/>

</center>
</div>
<footer>&copy - 2014 .php4allu.blogspot.com production</footer>
<script src='http://code.jquery.com/jquery-1.11.0.min.js'></script>
<script src='myjs.js'></script>
</body>
</html>

-----------------------------------------------------------------------------------------------
And now the last php code our fetch_data.php

 <?php
   if(isset($_POST['country_id']))
   {
      $con_id = (int)($_POST['country_id']);
 include 'conn.php';
 $fetch_state = "select * from state where country_id='".$con_id."' order by state";
 $result      = mysql_query($fetch_state)
 or die(mysql_query());
 $numrows = mysql_num_rows($result);
 if($numrows > 0)
 {
    //echo $numrows;
echo "<option selected disabled>Select</option>";
   while($row=mysql_fetch_array($result))
{
 
   echo "<option data='".$row['state']."' value='".$row['id']."'>".$row['state']."</option>";
}
 }
 else{
    echo "<option selected disabled>No state found under this country</option>";
 }
   }
 
   if(isset($_POST['state_id']))
   {
      $sta_id = (int)($_POST['state_id']);
 include 'conn.php';
 $fetch_state = "select * from city where state_id='".$sta_id."' order by city";
 $result      = mysql_query($fetch_state)
 or die(mysql_query());
 $numrows = mysql_num_rows($result);
 if($numrows > 0)
 {
    echo "<option selected disabled>Select</option>";
   while($row=mysql_fetch_array($result))
{
 
   echo "<option data='".$row['city']."' value='".$row['id']."'>".$row['city']."</option>";
}
 }
 else{
    echo "<option selected disabled>No city found under this country</option>";
 }
   }

?>

////////////////////////////////////  An the JS File for the onchnage event of the select box


$(document).ready(function(){

  $('#country_id').change(function(){
     $('#city').attr('disabled',true);
$('#state').attr('disabled',true);
$('.cit').html('');
$('.sta').html('');
     var country_id   = $(this).val();
var country_name = $('#country_id option:selected').attr('data');

if(country_id > 0)
{
   //alert(country_id);
$('.selection').show('slow');
$('.con').html(country_name).addClass('bold');
   $.post('fetch_data.php',{country_id:country_id},function(data){
  //alert(data);  /// for testing purposes
  $('#state').removeAttr('disabled');
  $('#state').html(data);
});
}

  });

  $('#state').change(function(){
     $('.cit').html('');
     var state_id   = $(this).val();
var state_name = $('#state option:selected').attr('data');

if(state_id > 0)
{
   //alert(country_id);
$('.selection').show('slow');
$('.sta').html(" &raquo; "+state_name).addClass('bold');
   $.post('fetch_data.php',{state_id:state_id},function(data){
  //alert(data);  /// for testing purposes
  $('#city').removeAttr('disabled');
  $('#city').html(data);
});
}
  });

  $('#city').change(function(){
     var city   = $(this).val();
var city = $('#city option:selected').attr('data');

$('.selection').show('slow');
$('.cit').show('slow');
     $('.cit').html(" &raquo; "+city).addClass('bold');
  });


});

-------------------------------------------------------------------------------------------

And last the function page

<?php
  function get_country(){
     $query = "select * from country order by name";
$result = mysql_query($query)
or die(mysql_error());
$numrows = mysql_num_rows($result);
if($numrows > 0)
{
   echo "<select name='country_id' id='country_id'><option selected disabled>Select</option>";
   while($row=mysql_fetch_array($result))
{
  echo "<option data='".$row['name']."' value='".$row['id']."'>".$row['name']."</option>";
}
echo "</select>";
}
   }
?>


And thst's it we are done guys....

If anybod want the zip file just comment with your email i will send it to you guys

Enjoy!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


LIVE DEMO





Posted by at

2 comments:

Thank your for your comment..your submitted coment will be live after admin approval

Subscribe to: Post Comments (Atom)