sql — добавить несколько строк в базе данных php?
Я хочу вставить много строк в базу данных через циклы. ($ Product, $ size, $ amount, $ uom, $ target_price) столбцы одной строки. Как вставить эти типы строк многопользовательского режима в database.and data.how, чтобы использовать для цикл или другой цикл для ввода серийной записи agasint № 1.
if(isset($_POST['Submit_form']))
{
/*For Master Detail*/
$serial_no=$_POST['serial_no'];
$dated=$_POST['dated'];
$ship_schedule=$_POST['ship_schedule'];
$commission=$_POST['commission'];
$customer_name=$_POST['customer_name'];
$agnet_name=$_POST['agnet_name'];
$remark=$_POST['remark'];
/*For Detail*/
$product=$_POST['product'];
$size=$_POST['size'];
$quantity=$_POST['quantity'];
$uom=$_POST['uom'];
$target_price=$_POST['target_price'];
$sqli = "INSERT INTO inquiry_mst (dated,ship_schedule,commision,customer_id,agent_id,remarks)
VALUES ('$serial_no','$dated','$ship_schedule','$commission','$customer_name','$agnet_name','$remark')";
$sqli=$mysqli->query($sqli) or die('Failed to connect'.$mysqli->error._LINE_);
if($sqli)
{
foreach($choices as $choice => $values)
{
$sqli="INSERT INTO inquiry_dtl (serial_no, product_name,size,quantity,uom,target_price)
VALUES ($product,$size,$quantity,$uom,$target_price)";
}
}
}
Решение
Вы можете использовать mysqli_multi_query для вашего запроса.
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', '[email protected]');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Mary', 'Moe', '[email protected]');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Julie', 'Dooley', '[email protected]')";
if ($conn->multi_query($sql) === TRUE) {
echo "New records created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
Источник: http://www.w3schools.com/php/php_mysql_insert_multiple.asp
или используя синтаксис SQL (если вы хотите один запрос):
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
Другие решения
I want this type of concept.that choices are save in database against the question number.in this code row consist of one column.but i need to insert multiple rows that are consist of multiple columns.
if(isset($_POST['submit']))
{
//for question
$question_number=$_POST['question_number'];
$question_text=$_POST['question_text'];
$correct_number=$_POST['correct_number'];
//for choices
$choices=array();
$choices[1]=$_POST['choice1'];
$choices[2]=$_POST['choice2'];
$choices[3]=$_POST['choice3'];
$choices[4]=$_POST['choice4'];
//Insert query for question
$sqli="INSERT INTO questions (question_number,question_text) VALUES ('$question_number','$question_text')";
$query=$mysqli->query($sqli) or die("falied to Query".$mysqli->error._LINE_);
if($query)
{
foreach($choices as $choice => $values)
{
if($values !== "")
{
if($correct_number == $choice )
{
$is_correct =1;
}
else
{
$is_correct=0;
}
$sqli= "INSERT INTO choices (question_number,is_correct,text) VALUES ('$question_number','$is_correct','$values')";
$query=$mysqli->query($sqli) or die('Failed to inserted'.$mysqli->error._LINE_);
if($query)
{
continue;
}
else
{
die('Failed insertes in choices table');
}
}
<?php
include_once 'connection.php';
session_start();$mysql="SELECT max(inquiry_dtl_id) FROM inquiry_dtl";
$query=$mysqli->query($mysql) or die('Failed to connect'.$mysqli->error._LINE_);
if($query)
{
while($max=$query->fetch_array())
{
$increment=$max[0]+1;
}
}
if(isset($_POST['add']) == "ADD")
{
if (isset($_POST['count'])) {$count = $_POST['count'] + 1;}
// set value for first load
else {$count = 1;}
}
if(isset($_POST['btnremove']) == "REMOVE") {
// decrement the row counter
$count = $_POST['count'] - 1;
// set minimum row number
if ($count < 0) $count = 0;
}
if(isset($_POST['Submit_form']))
{
/*For Master Detail*/
$serial_no=$_POST['serial_no'];
$dated=$_POST['dated'];
$ship_schedule=$_POST['ship_schedule'];
$commission=$_POST['commission'];
$customer_name=$_POST['customer_name'];
$agnet_name=$_POST['agnet_name'];
$remark=$_POST['remark'];/*for product table*/
$product=$_POST['product'];
$size=$_POST['size'];
$quantity=$_POST['quantity'];
$uom=$_POST['uom'];
$target_price=$_POST['target_price'];$sqli = "INSERT INTO inquiry_mst (dated,ship_schedule,commision,customer_id,agent_id,remarks)
VALUES ('$serial_no','$dated','$ship_schedule','$commission','$customer_name','$agnet_name','$remark')";
$sqli=$mysqli->query($sqli) or die('Failed to connect'.$mysqli->error._LINE_);
if($sqli)
{
foreach($choices as $choice => $values)
{
$sqli="INSERT INTO inquiry_dtl (serial_no, product_name,size,quantity,uom,target_price)
VALUES ($product,$size,$quantity,$uom,$target_price)";
}
}
}?>
<html>
<head>
<meta charset="utf-8">
<title>Customer Inquiry</title>
<link rel="stylesheet" type="text/css" href="css/bootstrap.min.css" />
<link rel="stylesheet" href="css/font-awesome.min.css"/>
<script type="text/javascript">
$("#add_row").click(function () {
var form = jQuery('form');
var row = jQuery('input',form).length;
var row_id = 'addr'+row;
var tr=$("#increment,#product,#Product_size,#quantity
,#uom,#target_price,#mymodel").
clone().attr('id',row_id);
tr.find('input').attr('id','parent'+row);
jQuery('form').append(tr);
});
</script>
</head>
<body>
<div style="background-image:url(images/bg.png);">
<form class="form-horizontal" method="post">
<fieldset>
<!-- Form Name -->
<legend style="text-align:center;">Customer Inquiry</legend>
<!-- Text input-->
<div class="form-group">
<label class="col-md-1 control-label" for="">Serial #</label>
<div class="col-md-1">
<?php
$mysql="SELECT max(serial_id) FROM inquiry_mst";
$query=$mysqli->query($mysql) or die('Failed to connect'.$mysqli->error._LINE_);
if($query)
{
while($max_id=$query->fetch_array())
{
?>
<input id="" name="serial_no" id="serial_no"type="text" placeholder=""class="form-control input-md" value="<?php
echo $max_id[0]+1; ?>"readonly>
<?php
}
}
?>
</div>
<label class="col-md-1 control-label" for="dated">Dated</label>
<div class="col-md-2">
<input id="dated" name="dated"type="text" placeholder="" class="form-control input-md">
</div>
<label class="col-md-2 control-label"for="ship_schedule">Ship Schedule</label>
<div class="col-md-2">
<input id="ship_schedule"name="ship_schedule" type="text"placeholder="" class="form-control input- md">
</div>
<label class="col-md-1 control-label" for="commission">Commission</label>
<div class="col-md-2">
<input id="commission"name="commission" type="text" placeholder=""class="form-control input-md">
</div>
</div>
<!-- Text input-->
<div class="form-group">
<label class="col-md-1 control-label" for="customer">Customer :</label>
<div class="col-md-2">
<select class="form-control input-md" name="customer_name">
<option>Customer Name</option>
<?php
$query="SELECT * FROM company_info";
$query=$mysqli->query($query) or
die('Failed to connect'.$mysqli- >error._LINE_);
if($query)
{
while ($row=$query->fetch_array())
{
?>
<option value="<?php
echo $row['company_id']; ?>">
<?php echo $row['contact_person']; ?></option>
<?php
}
}
?>
</select>
</div>
<label class="col-md-4 control-label" for="agent">Agent</label>
<div class="col-md-2">
<select class="form-control input-md" name="agnet_name">
<option>Agent Name</option>
<?php
$query="SELECT * FROM agent";
$query=$mysqli->query($query) or
die('Failed to connect'.$mysqli- >error._LINE_);
if($query)
{
while ($row=$query->fetch_array())
{
?>
<option value="<?php echo $row['agent_id']; ?>">
<?php echo $row['agent_name']; ?></option>
<?php
}
}
?>
</select>
</div>
</div>
<script src="js/jquery-2.1.4.min.js"></script>
<script src="js/bootstrap.min.js"></script>
</body>
</html>