In this tutorial we are going to learn how to insert multiple select box data into mysql table by using Ajax jquery with php. In this post we will make stylish multi select box with easy to use user interface, so for this we have use Jquery plugin light weight multi select, by using this light weight jquery plugin we can converts multi select box into two different selection box, when we have click on particular option of left select box then that value will be append into right select box and suppose we want to remove value from right select box then we have simply click on particular option that value will be removed from right box to left. This type of user interface we should use if we have multiple option select and from this interface we can easily understand which option we have select and which are left for selecting.
We will make simple dynamic dropdown list box for insert country state and multiple city data into table, from country and state drop down box we can select single data but from city multi select we can select multiple city at the same time. And lastly all select box data has been dependent on it's parent select box. That means city data depend on state selection and state data depend on country selection. In short all dropdown list are dynamic dependent. If we want to select with multiple item from single select box and this plugin is light weight and it is easy to use. If we have go to user interface wise it is easy to understand to user which option he has select from select box, but this plugin has one draw back is that it is not responsive. So here we have discuss how to insert multi select box data by using light weight multi select jquery plugin with Ajax and PHP.
Source Code
database_connection.php
<?php
//database_connection.php
$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");
?>
index.php
<?php
//index.php
include('database_connection.php');
$country = '';
$query = "
SELECT country FROM country_state_city GROUP BY country ORDER BY country ASC
";
$statement = $connect->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
foreach($result as $row)
{
$country .= '<option value="'.$row["country"].'">'.$row["country"].'</option>';
}
?>
<!DOCTYPE html>
<html>
<head>
<title>Insert Dynamic Multi Select Box Data using Jquery Ajax PHP</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<script src="jquery.lwMultiSelect.js"></script>
<link rel="stylesheet" href="jquery.lwMultiSelect.css" />
</head>
<body>
<br /><br />
<div class="container" style="width:600px;">
<h2 align="center">Insert Dynamic Multi Select Box Data using Jquery Ajax PHP</h2><br /><br />
<form method="post" id="insert_data">
<select name="country" id="country" class="form-control action">
<option value="">Select Country</option>
<?php echo $country; ?>
</select>
<br />
<select name="state" id="state" class="form-control action">
<option value="">Select State</option>
</select>
<br />
<select name="city" id="city" multiple class="form-control">
</select>
<br />
<input type="hidden" name="hidden_city" id="hidden_city" />
<input type="submit" name="insert" id="action" class="btn btn-info" value="Insert" />
</form>
</div>
</body>
</html>
<script>
$(document).ready(function(){
$('#city').lwMultiSelect();
$('.action').change(function(){
if($(this).val() != '')
{
var action = $(this).attr("id");
var query = $(this).val();
var result = '';
if(action == 'country')
{
result = 'state';
}
else
{
result = 'city';
}
$.ajax({
url:'fetch.php',
method:"POST",
data:{action:action, query:query},
success:function(data)
{
$('#'+result).html(data);
if(result == 'city')
{
$('#city').data('plugin_lwMultiSelect').updateList();
}
}
})
}
});
$('#insert_data').on('submit', function(event){
event.preventDefault();
if($('#country').val() == '')
{
alert("Please Select Country");
return false;
}
else if($('#state').val() == '')
{
alert("Please Select State");
return false;
}
else if($('#city').val() == '')
{
alert("Please Select City");
return false;
}
else
{
$('#hidden_city').val($('#city').val());
$('#action').attr('disabled', 'disabled');
var form_data = $(this).serialize();
$.ajax({
url:"insert.php",
method:"POST",
data:form_data,
success:function(data)
{
$('#action').attr("disabled", "disabled");
if(data == 'done')
{
$('#city').html('');
$('#city').data('plugin_lwMultiSelect').updateList();
$('#city').data('plugin_lwMultiSelect').removeAll();
$('#insert_data')[0].reset();
alert('Data Inserted');
}
}
});
}
});
});
</script>
fetch.php
<?php
//fetch.php
if(isset($_POST['action']))
{
include('database_connection.php');
$output = '';
if($_POST["action"] == 'country')
{
$query = "
SELECT state FROM country_state_city
WHERE country = :country
GROUP BY state
";
$statement = $connect->prepare($query);
$statement->execute(
array(
':country' => $_POST["query"]
)
);
$result = $statement->fetchAll();
$output .= '<option value="">Select State</option>';
foreach($result as $row)
{
$output .= '<option value="'.$row["state"].'">'.$row["state"].'</option>';
}
}
if($_POST["action"] == 'state')
{
$query = "
SELECT city FROM country_state_city
WHERE state = :state
";
$statement = $connect->prepare($query);
$statement->execute(
array(
':state' => $_POST["query"]
)
);
$result = $statement->fetchAll();
foreach($result as $row)
{
$output .= '<option value="'.$row["city"].'">'.$row["city"].'</option>';
}
}
echo $output;
}
?>
insert.php
<?php
//insert.php
if(isset($_POST['country']))
{
include('database_connection.php');
$query = "
INSERT INTO country_state_city_form_data (country, state, city)
VALUES(:country, :state, :city)
";
$statement = $connect->prepare($query);
$statement->execute(
array(
':country' => $_POST['country'],
':state' => $_POST['state'],
':city' => $_POST['hidden_city']
)
);
$result = $statement->fetchAll();
if(isset($result))
{
echo 'done';
}
}
?>
Your all work is excellent I learn many thing from your weblesson upload tutorial for new items add and remove in existing invoice
ReplyDeleteWhat do i need to remove from the code if i from the beginning want to select state, and then the list will update?
ReplyDeleteSo, only one drop down list is necessary.
What do i need to remove from the code if i want to chose cities from the country i set in the drop down list?
ReplyDeleteSo drop the state-option
hi, every nice night, very nice this system. i have a question. how can i add the accept values in 2 0r 3 state. İf i add to the 1 state is ok forexample how i add to Alabama and Newyork. thank you for this project and help. sorry for my English.
ReplyDeletethis project is vary helpful.
ReplyDeletehow to insert to multiple rows in another table
ReplyDeleteThanks for your help but i want to know how to create dynamic field/column+value to a database using php
ReplyDeletehi i'm happy with your cod you have developed i appriciat for that thus i have a question for you when i try to upload a file by combining your source code it is not work so sir i need your help how can upload a file using your source code that you have used
ReplyDeleteprepare($query);
ReplyDelete$statement->execute(
array(
':country' => $country,
':state' => $state,
':city' => $city,
':file' => $name
)
);
$result = $statement->fetchAll();
if(isset($result))
{
move_uploaded_file($temp,"files/".$name);
echo 'done';
}
}
?>
this is insert code that try to upload a file but it is not working how can i solve it
the values are getting sort not inserted to database as selected
ReplyDeletehow to show already selected option on update page
ReplyDeletecan do it laravel ?
ReplyDelete