jQuery Dependent DropDown List - Countries and States with product base
1. create sql like this
-- phpMyAdmin SQL Dump
-- version 4.2.11
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Jul 02, 2015 at 10:29 PM
-- Server version: 5.6.21
-- PHP Version: 5.6.3
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: `countrystate`
--
-- --------------------------------------------------------
--
-- Table structure for table `country`
--
CREATE TABLE IF NOT EXISTS `country` (
`id` int(4) NOT NULL,
`name` text NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
--
-- Dumping data for table `country`
--
INSERT INTO `country` (`id`, `name`) VALUES
(1, 'india'),
(2, 'usa'),
(3, 'france');
-- --------------------------------------------------------
--
-- Table structure for table `product`
--
CREATE TABLE IF NOT EXISTS `product` (
`id` int(5) NOT NULL,
`countryID` int(5) NOT NULL,
`stateID` int(5) NOT NULL,
`p_name` text NOT NULL,
`p_quantity` text NOT NULL,
`price` text NOT NULL,
`total` text NOT NULL,
`img_path` text NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
--
-- Dumping data for table `product`
--
INSERT INTO `product` (`id`, `countryID`, `stateID`, `p_name`, `p_quantity`, `price`, `total`, `img_path`) VALUES
(1, 1, 1, 'parle', '2', '100', '200', 'a'),
(2, 1, 2, 'tiger', '10', '200', '20000', 'Desert.jpg');
-- --------------------------------------------------------
--
-- Table structure for table `state`
--
CREATE TABLE IF NOT EXISTS `state` (
`id` int(4) NOT NULL,
`countryID` int(4) NOT NULL,
`name` text NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
--
-- Dumping data for table `state`
--
INSERT INTO `state` (`id`, `countryID`, `name`) VALUES
(1, 1, 'haryana'),
(2, 1, 'utter pradesh'),
(3, 2, 'lowa'),
(4, 2, 'new york'),
(5, 2, 'california'),
(6, 3, 'france comte'),
(7, 3, 'france delhi');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `country`
--
ALTER TABLE `country`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `product`
--
ALTER TABLE `product`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `state`
--
ALTER TABLE `state`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `country`
--
ALTER TABLE `country`
MODIFY `id` int(4) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=4;
--
-- AUTO_INCREMENT for table `product`
--
ALTER TABLE `product`
MODIFY `id` int(5) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=3;
--
-- AUTO_INCREMENT for table `state`
--
ALTER TABLE `state`
MODIFY `id` int(4) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=8;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2. create index.php
<?php
require_once("dbcontroller.php");
$db_handle = new DBController();
$query ="SELECT * FROM country";
$results = $db_handle->runQuery($query);
?>
<html>
<head>
<head>
<style>
body{width:610px;}
.frmDronpDown {border: 1px solid #F0F0F0;background-color:#C8EEFD;margin: 2px 0px;padding:40px;}
.demoInputBox {padding: 10px;border: #F0F0F0 1px solid;border-radius: 4px;background-color: #FFF;width: 50%;}
.row{padding-bottom:15px;}
</style>
<script src="https://code.jquery.com/jquery-2.1.1.min.js" type="text/javascript"></script>
<script>
function getState(val) {
$.ajax({
type: "POST",
url: "get_state.php",
data:'country_id='+val,
success: function(data){
$("#state-list").html(data);
}
});
}
function selectCountry(val) {
$("#search-box").val(val);
$("#suggesstion-box").hide();
}
</script>
<script>
/*$(document).ready(function() {
$("#sub").click(function(){
alert("hello anup");
var country=$("#country-list").val();
alert(country);
var state=$("#state-list").val();
alert(state);
var pname=$("#p_name").val();
alert(pname);
var pqnt=$("#p_qnt").val();
alert(pqnt);
var p_price=$("#p_price").val();
alert(p_price);
var total=$("#total").val();
alert(total);
var datastring= 'country1='+country +'&state1='+state +'&pname1='+pname +'&pqnt1='+ pqnt + '&p_price1='+p_price + '&total='+total;
$.ajax({
type:"POST",
url:"action.php",
data:datastring,
cache:true,
success: function(response){
alert("success");
}
});
return false;
});
});*/
</script>
<script>
function calculate() {
var qnt = document.getElementById('p_qnt').value;
var amt = document.getElementById('p_price').value;
var result = document.getElementById('result');
var myResult = qnt * amt;
result.value = myResult;
}
</script>
<script>
$(document).ready(function (e) {
$("#uploadForm").on('submit',(function(e) {
e.preventDefault();
$.ajax({
url: "upload.php",
type: "POST",
data: new FormData(this),
contentType: false,
cache: false,
processData:false,
success: function(data)
{
$("#targetLayer").html(data);
},
error: function()
{
}
});
}));
});
</script>
</head>
<body>
<div class="frmDronpDown">
<form action="upload.php" method="post" id="uploadForm" enctype="multipart/form-data">
<div class="row">
<label>Country:</label><br/>
<select name="country" id="country-list" class="demoInputBox" onChange="getState(this.value);">
<option value="">Select Country</option>
<?php
foreach($results as $country) {
?>
<option value="<?php echo $country["id"]; ?>"><?php echo $country["name"]; ?></option>
<?php
}
?>
</select>
</div>
<div class="row">
<label>State:</label><br/>
<select name="state" id="state-list" class="demoInputBox">
<option value="">Select State</option>
</select>
</div>
<span>Product Name</span><input type="text" id="p_name" class="p_name1" name="p_name2"></br>
<span>Product Quantity</span><input type="text" id="p_qnt" class="p_qnt1" name="p_qnt2" oninput="calculate()"></br>
<span>Product price</span><input type="text" id="p_price" class="p_price1" name="p_price2" value="100" disabled="disabled"></br>
<span>total</span><input type="text" id="result" class="total1" name="total3"></br>
<!--<span>Image</span><input type="file" id="img" class="img1" name="img2"></br>
<input type="submit" class="sub1" id="sub" name="sub2">-->
<input name="userImage" type="file" class="inputFile" />
<input type="submit" value="Submit" class="btnSubmit" />
</form>
</div>
<div id="targetLayer">nvgf</div>
</body>
</html>
3 create database query file.
<?php
class DBController {
private $host = "localhost";
private $user = "root";
private $password = "";
private $database = "countrystate";
function __construct() {
$conn = $this->connectDB();
if(!empty($conn)) {
$this->selectDB($conn);
}
}
function connectDB() {
$conn = mysql_connect($this->host,$this->user,$this->password);
return $conn;
}
function selectDB($conn) {
mysql_select_db($this->database,$conn);
}
function runQuery($query) {
$result = mysql_query($query);
while($row=mysql_fetch_assoc($result)) {
$resultset[] = $row;
}
if(!empty($resultset))
return $resultset;
}
function numRows($query) {
$result = mysql_query($query);
$rowcount = mysql_num_rows($result);
return $rowcount;
}
}
?>
4. create get_state.php for query and selection
<?php
require_once("dbcontroller.php");
$db_handle = new DBController();
if(!empty($_POST["country_id"])) {
$query ="SELECT * FROM state WHERE countryID = '" . $_POST["country_id"] . "'";
$results = $db_handle->runQuery($query);
?>
<option value="">Select State</option>
<?php
foreach($results as $state) {
?>
<option value="<?php echo $state["id"]; ?>"><?php echo $state["name"]; ?></option>
<?php
}
}
?>
5. create upload.php file for insert data into database
<?php
if(is_array($_FILES)) {
if(is_uploaded_file($_FILES['userImage']['tmp_name'])) {
$sourcePath = $_FILES['userImage']['tmp_name'];
$targetPath = "images/".$_FILES['userImage']['name'];
$imagename=$_FILES['userImage']['name'];
if(move_uploaded_file($sourcePath,$targetPath)) {
?>
<img src="<?php echo $targetPath; ?>" width="100px" height="100px" />
<?php
}
}
}
?>
<script type="text/javascript">
var p_name2="<?php echo $_POST['p_name2']; ?>";
alert(p_name2);
var country="<?php echo $_POST['country']; ?>";
alert(country);
var state="<?php echo $_POST['state']; ?>";
alert(state);
var p_qnt2="<?php echo $_POST['p_qnt2']; ?>";
alert(p_qnt2);
var p_price2="<?php echo $_POST['p_price2']; ?>";
alert(p_price2);
var total3="<?php echo $_POST['total3']; ?>";
alert(p_price2);
</script>
<?php
$pname = $_POST['p_name2'];
$country= $_POST['country'];
$state= $_POST['state'];
$pqnt= $_POST['p_qnt2'];
$p_price= $_POST['p_price2'];
$total=$_POST['total3'];
//database connection
$con=mysql_connect('localhost','root','');
mysql_select_db('countrystate',$con);
mysql_query("insert into product set countryID='$country', stateID='$state', p_name='$pname', p_quantity='$pqnt', price='$p_price', total='$total', img_path='$imagename'");
?>