Good day,
I need help with my ajax based dynamic drop down list. Right now, ajax.php pulls up data depending on value at a single column. I need it to pull up data based on value at certain columns. I am new to php and its going over my knowledge. Below included what I could do so far.
What I need, is to pull up value from database-table1-column6 depending on value at database-table1-column2,3,4,5
Here are the codes:
Ajax.php
`
<?php
class AJAX {
private $database = NULL;
private $_query = NULL;
private $_fields = array();
public $_index = NULL;
const DB_HOST = "localhost";
const DB_USER = "root";
const DB_PASSWORD = "";
const DB_NAME = "2";
public function __construct(){
$this->db_connect(); // Initiate Database connection
$this->process_data();
}
/*
* Connect to database
*/
private function db_connect(){
$this->database = mysql_connect(self::DB_HOST,self::DB_USER,self::DB_PASSWORD);
if($this->database){
$db = mysql_select_db(self::DB_NAME,$this->database);
} else {
echo mysql_error();die;
}
}
private function process_data(){
$this->_index = ($_REQUEST['index'])?$_REQUEST['index']:NULL;
$id = ($_REQUEST['id'])? (int)$_REQUEST['id']:NULL;
switch($this->_index){
case 'device':
$this->_query = "SELECT dataSource.id, dataSource.device_id, reference.device_name
FROM dataSource
RIGHT JOIN reference
ON dataSource.device_id=reference.id
WHERE catch=0
GROUP BY device_name
ORDER BY id";
$this->_fields = array('id','device_id','device_name');
break;
case 'model':
$this->_query = "SELECT dataSource.id, dataSource.device_id, dataSource.model_id, reference.model_name
FROM dataSource
RIGHT JOIN reference
ON dataSource.model_id=reference.id
WHERE dataSource.device_id = $id
GROUP BY model_name
ORDER BY id";
$this->_fields = array('id','model_id','model_name');
break;
case 'quality':
$this->_query = "SELECT dataSource.id, dataSource.model_id, dataSource.quality_id, reference.quality_name
FROM dataSource
RIGHT JOIN reference
ON dataSource.quality_id=reference.id
WHERE dataSource.model_id = $id
GROUP BY quality_name
ORDER BY id";
$this->_fields = array('id','quality_id', 'quality_name');
break;
case 'currency':
$this->_query = "SELECT dataSource.id, dataSource.quality_id, dataSource.currency_id, reference.currency_name
FROM dataSource
RIGHT JOIN reference
ON dataSource.currency_id=reference.id
WHERE dataSource.quality_id = $id
GROUP BY currency_name
ORDER BY id";
$this->_fields = array('id','currency_id', 'currency_name');
break;
case 'amount':
$this->_query = "SELECT dataSource.id, dataSource.currency_id, dataSource.amount_id, reference.amount_name
FROM dataSource
RIGHT JOIN reference
ON dataSource.amount_id=reference.id
WHERE dataSource.currency_id = $id
GROUP BY amount_name
ORDER BY amount_id";
$this->_fields = array('id','amount_id', 'amount_name');
break;
default:
break;
}
$this->show_result();
}
public function show_result(){
echo '<option value="">Select '.$this->_index.'</option>';
$query = mysql_query($this->_query);
while($result = mysql_fetch_array($query)){
$entity_id = $result[$this->_fields[1]];
$enity_name = $result[$this->_fields[2]];
echo "<option value='$entity_id'>$enity_name</option>";
}
}
}
$obj = new AJAX;
?>
`
Index.php
<?php include('config.php'); ?>
<html xmlns="http://www.w3.org/1999/xhtml"><head profile="http://gmpg.org/xfn/11">
<head>
<title>device currency amount Dependent Dropdown using Ajax</title>
<link rel="stylesheet" href="css/common.css" type="text/css" media="screen"/>
<link rel="stylesheet" href="css/index.css" type="text/css" media="screen"/>
<link rel="stylesheet" href="css/index.css" type="text/css" media="print"/>
<script type="text/javascript" src="jquery-1.10.1.min.js"></script>
<script type="text/javascript" src="jquery.validate.min.js"></script>
<script type="text/javascript">
$(document).ready(function(){
load_options('','device');
});
function load_options(id,index){
$("#loading").show();
if(index=="currency"){
$("#amount").html('<option value="">Select amount</option>');
}
$.ajax({
url: "ajax.php?index="+index+"&id="+id,
complete: function(){$("#loading").hide();},
success: function(data) {
$("#"+index).html(data);
}
})
}
</script>
<script type="text/javascript">
$(document).ready(function () {
$('#topup').validate({ // initialize the plugin
rules:
{
email: {
required: true,
email: true
},
phone: {
required: true,
minlength: 5
},
device:{required: true },
model:{required: true },
quality:{required: true },
currency:{required: true },
amount:{required: true }
}
});
});
</script>
</head>
<body>
<div id="getstarted">
<h1 title="Let's get started to make cheapest international call with Small and Smart communication.">Get started</h1>
<form id="topup" method="post" action="process.php">
<input type="email" name="email" id="email" placeholder="Enter your email address" autofocus required/><br />
<input type="phone" name="phone" id="phone" placeholder="Enter your mobile number as +447410XX" required/><br />
<select id="device" name="device" onChange="load_options(this.value,'model');" required>
<option value="">Select device</option>
</select>
<br>
<select id="model" name="model" onChange="load_options(this.value,'quality');" required>
<option value="">Select model</option>
</select>
<br>
<select id="quality" name="quality" onChange="load_options(this.value,'currency');" required>
<option value="">Select quality preference</option>
</select>
<br>
<select id="currency" name="currency" onChange="load_options(this.value,'amount');" required>
<option value="">Select currency</option>
</select>
<br>
<select id="amount" name="amount" required>
<option value="">Select amount</option>
</select>
<img src="" alt="Loading..." id="loading" align="absmiddle" style="display:none;"/>
<input type="submit" name="submit" value="Proceed to step-2"/>
</form>
</div>
</body>
</html>
Database- table1: dataSource
CREATE TABLE IF NOT EXISTS `datasource` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`device_id` varchar(10) NOT NULL,
`model_id` varchar(50) NOT NULL,
`quality_id` varchar(50) NOT NULL,
`currency_id` varchar(3) NOT NULL,
`amount_id` int(2) NOT NULL,
`gen-time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`catch` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=14 ;
--
-- Dumping data for table `datasource`
--
INSERT INTO `datasource` (`id`, `device_id`, `model_id`, `quality_id`, `currency_id`, `amount_id`, `gen-time`, `catch`) VALUES
(4, '1', '1', '1', '1', 1, '2013-12-31 06:01:47', 0),
(5, '1', '2', '1', '1', 2, '2013-12-31 06:03:16', 0),
(6, '1', '3', '1', '1', 3, '2013-12-31 06:03:16', 0),
(7, '1', '2', '2', '2', 1, '2013-12-31 06:04:14', 0),
(8, '1', '2', '3', '2', 2, '2013-12-31 06:04:14', 0),
(9, '2', '4', '1', '1', 1, '2013-12-31 06:07:01', 0),
(10, '2', '4', '1', '1', 2, '2013-12-31 06:07:01', 0),
(11, '2', '4', '1', '1', 3, '2013-12-31 06:07:01', 0),
(12, '2', '4', '1', '2', 1, '2013-12-31 06:07:01', 0),
(13, '2', '4', '1', '2', 2, '2013-12-31 06:07:01', 0);
Database- table2: reference:
Table2- reference:
CREATE TABLE IF NOT EXISTS `reference` (
`id` int(1) NOT NULL,
`device_name` varchar(10) NOT NULL,
`model_name` varchar(50) NOT NULL,
`quality_name` varchar(50) NOT NULL,
`currency_name` varchar(50) NOT NULL,
`amount_name` int(2) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
--
-- Dumping data for table `reference`
--
INSERT INTO `reference` (`id`, `device_name`, `model_name`, `quality_name`, `currency_name`, `amount_name`) VALUES
(1, 'mobile', 'iphone/ipad', 'Standard (good quality)', 'usd', 5),
(2, 'computer', 'Samsung & Android mobile and tablet', 'Premium (Excellent quality)', 'cad', 10),
(3, '', 'Nokia & Symbian mobile', 'Business grade', 'aud', 15),
(4, '', 'Computer & laptop (windows)', '', 'sgd', 20);
