使用具有父ID的数组方法插入表 [英] Insert into table using array method with parent ID
本文介绍了使用具有父ID的数组方法插入表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我试图在插入之前检查数据库的输入.但是，它在父表中插入了多个ID.它假设要插入多个具有相同父ID的子父.我有3个子表， toto_number，damacai_number和magnum_number .您可以看一下我提供的图像.我试图在插入之前检查数据库的输入.因此，意味着它会在插入数据库之前检查号码的可用性
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
界面视图
insert.php文件
<?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>
推荐答案
这应该很好.让我知道是否还有其他问题.
This should do just fine. Let me know if there's any other issue further on.
<?php
try {
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "2d_system";
$conn = new mysqli($servername, $username, $password, $dbname);
if($stmt = $conn->prepare($conn, "SELECT `Availability` FROM `number_availability` WHERE `Number`=? AND `GameCenter`=?")){
foreach($_POST['gamecenter'] as $key => $value){
$gamecenter = $_POST['gamecenter'][$key];
$number = $_POST['number'][$key];
$stmt->bind_param('ii', $number, $gamecenter); // if any of these values is a String, use 's' for that value instead (ii means integer-integer)
$stmt->execute();
if($conn->errno){
throw new Exception("Error: could not check for availability: " . $conn->error);
}
$result = $stmt->get_result();
$data = $result->fetch_array();
if($data['Availability'] <= 0){
unset($_POST['gamecenter'][$key]);
unset($_POST['number'][$key]);
unset($_POST['price'][$key]);
}
}
}
if($conn->errno){
throw new Exception("Error: could not check for availability: " . $conn->error);
}
if(count($_POST['gamecenter']) > 0){
if($conn->query("INSERT INTO `lottery_ticket` (`CreatedDateTime`) VALUES (now())")){
$lotteryTicketID = $conn->insert_id;
foreach($_POST['gamecenter'] as $key => $value){
$gamecenter = $_POST['gamecenter'][$key];
$number = $_POST['number'][$key];
$price = $_POST['price'][$key];
if($stmt = $conn->prepare("INSERT INTO `" . strtolower($gamecenter) . "_draw` (`LotteryId`, `" . $gamecenter . "_Number`, `Price`) VALUES (?, ?, ?)")){
$stmt->bind_param('idd', $lotteryTicketID, $number, $price);
$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.";
} else {
throw new Exception("Error: no available numbers.");
}
} catch(Exception $e){
echo $e->getMessage();
}
$conn->close();
?>
顺便说一句，在继续开发之前，请阅读有关参数化语句的更多信息.另外，请尝试理解我给您的代码并阅读评论.上次我几乎更改了所有内容，在这个问题中我可以看到您忽略了所有这些内容.此外，您似乎不了解代码的逻辑，因此请考虑一下.尝试在纸上写下每种算法，然后测试算法，然后基于该算法构建应用程序.
By the way, before you continue developing, read more about parameterized statements. Also, try to understand the code I'm giving you and read the comments. Last time I changed pretty much everything and I can see in this question that you ignored all that. Furthermore, you don't seem to understand the logic of your code, so give it some thought. Try writing every algorithm down in paper, then test the algorithm, and then build your applications based on that algorithm.
这篇关于使用具有父ID的数组方法插入表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
