1How do I insert a query based on a dropdown list? My database consists of 4 tables, one parent table and 3 child tables. My dropdown list consists of Table1, Table2, and Table3. If Table3 is selected, data will be inserted into Table3.
It works fine when I try to insert a single input field but, when I try to insert more than one input, it will only store the last selected table.
Have a look at my form which I have stored here.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "2d_system";
$conn = new mysqli($servername, $username, $password, $dbname);
$sql = "INSERT INTO lottery_ticket(CreatedDateTime) VALUES (now())";
mysqli_query($conn, $sql);
foreach($_POST['gamecenter'] as $i => $gamecenter){
$gamecenter = $_POST['gamecenter'][$i];
$number = $_POST['number'][$i];
$price = $_POST['price'][$i];
//checks type of gamecenter if true, insert
if ($gamecenter == 'Damacai'){
try {
//to insert into parent table
//$sql = "INSERT INTO lottery_ticket(CreatedDateTime) VALUES (now())";
// mysqli_query($conn, $sql);
$queryDamacai = "INSERT INTO damacai_draw (LotteryId, Damacai_Number, Price) VALUES (last_insert_id(), '$number', '$price')";
if(!mysqli_query($conn, $queryDamacai)){
throw new Exception("error: could not able to execute $queryDamacai. " . mysqli_error($conn));
}
echo "Records added successfully.";
}
catch(Exception $e)
{
echo $e->getMessage();
}
}
//checks type of gamecenter if true, insert
if ($gamecenter == 'Magnum'){
try {
//to insert into parent table
//$sql = "INSERT INTO lottery_ticket(CreatedDateTime) VALUES
(now())";
//mysqli_query($conn, $sql);
$queryMagnum = "INSERT INTO magnum_draw (LotteryId, Magnum_Number, Price) VALUES (last_insert_id(), '$number', '$price')";
if(!mysqli_query($conn, $queryMagnum)){
throw new Exception("error: could not able to execute $queryMagnum. " . mysqli_error($conn));
}
echo "Records added successfully.";
}
catch(Exception $e)
{
echo $e->getMessage();
}
}
//checks type of gamecenter if true, insert
if ($gamecenter == 'Toto'){
try{
<?php
//index.php
$connect = new PDO("mysql:host=localhost;dbname=2d_system", "root", "");
function fill_unit_select_box($connect)
{
$output = '';
$query = "SELECT * FROM tbl_unit ORDER BY unit_name ASC";
$statement = $connect->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
foreach($result as $row)
{
$output .= '<option value="'.$row["unit_name"].'">'.$row["unit_name"].'</option>';
}
return $output;
}
?>
<!DOCTYPE html>
<html>
<head>
<title>2D</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>
</head>
<body>
<br />
<div class="container">
<br />
<h4 align="center">Enter Number Details</h4>
<br />
<form method="post" id="insert_form" action="insert.php">
<div class="table-repsonsive">
<span id="error"></span>
<table class="table table-bordered" id="item_table">
<tr>
<th>2D Number</th>
<th>Price (RM)</th>
<th>Game Center</th>
<th><button type="button" name="add" class="btn btn-success btn-sm add"><span class="glyphicon glyphicon-plus"></span></button></th>
</tr>
</table>
<div align="center">
<input type="submit" name="submit" class="btn btn-info" value="Print" />
</div>
</div>
</form>
<form method="post" action="collect_vals.php">
</div>
</body>
</html>
<script>
$(document).ready(function(){
$(document).on('click', '.add', function(){
var html = '';
html += '<tr>';
html += '<td><input type="text" name="number[]" class="form-control item_name" /></td>';
html += '<td><input type="text" name="price[]" class="form-control item_quantity" /></td>';
html += '<td><select name="gamecenter[]" class="form-control item_unit"><option value="">Select Unit</option><?php echo fill_unit_select_box($connect); ?></select></td>';
html += '<td><button type="button" name="remove" class="btn btn-danger btn-sm remove"><span class="glyphicon glyphicon-minus"></span></button></td></tr>';
$('#item_table').append(html);
});
$(document).on('click', '.remove', function(){
$(this).closest('tr').remove();
});
$('#insert_form').on('submit', function(event){
event.preventDefault();
var error = '';
$('.number').each(function(){
var count = 1;
if($(this).val() == '')
{
error += "<p>Enter Item Name at "+count+" Row</p>";
return false;
}
count = count + 1;
});
$('.price').each(function(){
var count = 1;
if($(this).val() == '')
{
error += "<p>Enter Item Quantity at "+count+" Row</p>";
return false;
}
count = count + 1;
});
$('.gamecenter').each(function(){
var count = 1;
if($(this).val() == '')
{
error += "<p>Select Unit at "+count+" Row</p>";
return false;
}
count = count + 1;
});
var form_data = $(this).serialize();
if(error == '')
{
$.ajax({
url:"insert.php",
method:"POST",
data:form_data,
success:function(data)
{
if(data == 'ok')
{
$('#item_table').find("tr:gt(0)").remove();
$('#error').html('<div class="alert alert-success">Item Details Saved</div>');
}
}
});
}
else
{
$('#error').html('<div class="alert alert-danger">'+error+'</div>');
}
});
});
</script>//to insert into parent table
$queryToto = "INSERT INTO toto_draw (LotteryId, Toto_Number, Price) VALUES (last_insert_id(), '$number', '$price')";
if(!mysqli_query($conn, $queryToto)){
throw new Exception("error: could not able to execute $queryToto. " . mysqli_error($conn));
}
echo "Records added successfully.";
}
catch(Exception $e)
{
echo $e->getMessage();
}
}
}
$conn->close();
?>
