I am trying to create a search box where the option selected from 'box1' populates the options available for 'box2'. The options for both boxes are given from my MYSQL database. My problem is that I do not know how to perform a query based on the first query without refreshing the page which would be tedious and annoying.
HTML / PHP
<form role="form" action="search.php" method="GET">
<div class="col-md-3">
<select class="form-control">
<?php
$result = mysqli_query($con,"SELECT `name` FROM school");
while($row = mysqli_fetch_array($result)) {
echo '<option name="'.$row['name'].'">'.$row['name'].' School</option>';
}
?>
</select>
</div>
<div class="col-md-3">
<select class="form-control">
<?php
$result = mysqli_query($con,"SELECT * FROM products");
while($row = mysqli_fetch_array($result)) {
echo '<option name="'.$row['product'].'">'.$row['product'].'</option>';
}
mysqli_close($con);
?>
</select>
</div>
<button type="submit" class="btn btn-info">Search</button>
</form>
I think that the query would go something like this. AJAX may be the solution to this problem but I am unsure how to use AJAX to perform this query without refreshing.
SELECT `product` FROM products WHERE `school` = [SCHOOL NAME FROM BOX 1]
Thanks in advance!
First create the no1 select menu with php as you mentioned above. Then add a 'change' eventListener to it like:
$('#select1').change(createSelect2);
function createSelect2(){
var option = $(this).find(':selected').val(),
dataString = "option="+option;
if(option != '')
{
$.ajax({
type : 'GET',
url : 'http://www.mitilini-trans.gr/demo/test.php',
data : dataString,
dataType : 'JSON',
cache: false,
success : function(data) {
var output = '<option value="">Select Sth</option>';
$.each(data.data, function(i,s){
var newOption = s;
output += '<option value="' + newOption + '">' + newOption + '</option>';
});
$('#select2').empty().append(output);
},
error: function(){
console.log("Ajax failed");
}
});
}
else
{
console.log("You have to select at least sth");
}
}
Now the no2 select menu has new options according to the select 1 selected option.
And the php file:
<?php
header('Content-Type: application/json; charset=utf-8');
header('Access-Control-Allow-Origin: *');
if(isset($_GET['option']))
{
$option = $_GET['option'];
if($option == 1)
{
$data = array('Arsenal', 'Chelsea', 'Liverpool');
}
if($option == 2)
{
$data = array('Bayern', 'Dortmund', 'Gladbach');
}
if($option == 3)
{
$data = array('Aek', 'Panathinaikos', 'Olympiakos');
}
$reply = array('data' => $data, 'error' => false);
}
else
{
$reply = array('error' => true);
}
$json = json_encode($reply);
echo $json;
?>
Of course there i use some demo data but you can make an sql query populate the $data array there and send them as json with the right headers. Finally use some more js for the second select menu:
$('#select2').change(selectSelect2);
function selectSelect2(){
var option = $(this).find(':selected').val();
if(option != '')
{
alert("You selected: "+option);
}
else
{
alert("You have to select at least sth");
}
}
Check here http://jsfiddle.net/g3Yqq/2/ a working example
Try this:
You made one mistake here in select && option tag structure of HTML.
Just modify this and your code will work.
<select class="form-control" name="ddlist1">
&&
<?php
echo '<option value = "'.$row['name'].'">'.$row['name'].' School</option>';
?>
>> Add name property in select statement and value in place of name in option tag.
Thanks!
maybe you can create a javascript two-dimensional array that save the relations school to products. And when select a shool name, you can get the products list by the school name as the key in the array, and update the box2's options list.
Maybe you can echo a js string like this:
<script language="javascript">
var array = { "school_name1" : { "product1", "poduct2" }, "school_name2", { "product3", "product4" } };
//you can get the product list by array['school_name1'], and you use js to update the product list
</script>
thanks
You should try this;
search.php
<?php
// Check if the user wants the school or the school products (based on what the $.getJSON function sends us
if ( ! isset( $_GET['products'] ) && ! empty( $_GET['school'] ) ) {
$sql_schools = mysqli_query( $con, "SELECT `name` FROM school" );
$schools = '';
while ( $school = mysqli_fetch_array( $sql_schools ) ) {
$schools .= '<option value="' . $school['name'] . '">' . $school['name'] . '</option>';
}
}
// The user selected a school and now we will send back a JSON array with products, belonging to the specified school
else {
$school = mysqli_real_escape_string( $_GET['school'] );
$sql_products = mysqli_query( $con, "SELECT `product` FROM products WHERE school = '{$school}'" );
$products = array();
while ( $product = mysqli_fetch_array( $sql_products ) ) {
// Note: If you use array_push() to add one element to the array it's better to use $array[] =
// because in that way there is no overhead of calling a function.
// http://php.net/manual/en/function.array-push.php
$products[] = $product['product'];
}
header( "Content-Type: application/json" );
echo json_encode( $products );
die;
}
?>
<form role="form" action="search.php" method="GET">
<div class="col-md-3">
<select class="form-control" id="schools" name="school">
<?= $schools; ?>
</select>
</div>
<div class="col-md-3">
<select class="form-control">
</select>
</div>
<button type="submit" class="btn btn-info">Search</button>
</form>
<script src="http://code.jquery.com/jquery-1.10.2.min.js"></script>
<script>
// When #schools gets changed to a new value, load the products belonging to that school
$('#schools').on('change', function() {
$.getJSON('search.php?products&school=' + this.value, function(data) {
var items = [];
$.each(data, function(key, val) {
items.push('<option value="' + val + '">' + val + '</option>');
});
$('#schools').empty().append(items);
});
});
</script>