I'm trying to check the input from the database before inserting. However, it inserted multiple id in the parent table. It suppose to insert multiple child parent with the same parent id. I have 3 child table, toto_number, damacai_number, and magnum_number. You can have a look on the image I have provided. I'm trying to check the input from the database before inserting. So means it checking the availability of the number before inserting into database
Interface view
insert.php file
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "2d_system";
$conn = new mysqli($servername, $username, $password, $dbname);
foreach ($_POST['gamecenter'] as $key => $value) { // all game centers will be looped here
$gamecenter = $_POST['gamecenter'][$key];
$number = $_POST['number'][$key];
$price = $_POST['price'][$key];
$result = mysqli_query($conn, "SELECT * FROM number_availability WHERE Number = '" . $number . "' AND GameCenter = '" . $gamecenter . "'");
$row = mysqli_fetch_assoc($result);
try {
if ($row['Availability'] > 0) {
if ($conn->query("INSERT INTO lottery_ticket(CreatedDateTime) VALUES (now())")) { // consider adding a default value of CURRENT_TIMESTAMP for CreatedDateTime
$lotteryTicketID = $conn->insert_id;
// foreach ($_POST['gamecenter'] as $k => $v) { // all game centers will be looped here
//$gamecenter = $_POST['gamecenter'][$k]; // make sure you need this, if the values are incorrect, then consider using
// $gamecenter = $v;
if ($stmt = $conn->prepare("INSERT INTO " . strtolower($gamecenter) . "_draw (LotteryId, " . $gamecenter . "_Number, Price) VALUES (?, ?, ?)")) { // This part is done to avoid creating so many duplicated queries and and shorten the code.
$number = $_POST['number'][$key];
$price = $_POST['price'][$key];
$stmt->bind_param('idd', $lotteryTicketID, $number, $price); // be careful with these values. If you change the name of your tables or columns, these might be affected.
$stmt->execute();
// }
if ($conn->errno) {
throw new Exception("Error: could not execute query/queries: " . $conn->error);
}
}
}
}
if ($conn->errno) {
throw new Exception("Error: could not execute query/queries: " . $conn->error);
}
echo "Records added successfully.";
}
catch (Exception $e) {
echo $e->getMessage();
}
}
$conn->close();
?>
//index.php
<?php
?>
<!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="test3.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" onclick="" 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="Check Number" />
</div>
</div>
</form>
</br>
</div>
</body>
</html>
<script>
$(document).ready(function(){
$(document).on('click', '.add', function(){
var html = '';
html += '<tr>';
html += '<td><input type="text" name="number[]" value="" 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><option value="Damacai">Damacai</option><option value="Magnum">Magnum</option><option value="Toto">Toto</option></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){
$(document.body).append(data);
}
});
}
else
{
$('#error').html('<div class="alert alert-danger">'+error+'</div>');
}
});
});
</script>
