How to insert more than 2 queries in MySQL using arrays

Question

I have a problem in inserting query in MySQL. Whenever I try to submit more than 2 queries in MySQL. Like this:

The first 2 queries(2011 and 2012) will only be inserted to MySQL. While the 2013 was not inserted. I'm sorry but I'm just new here in PHP. I want the forms to be submitted and inserted all the queries to MySQL. I want that whenever I try to input more than two forms that it will be all submitted and inserted to MySQL and not just 2 queries only.

<?php
$mysqli = new mysqli('localhost','root','','sample');

//Output any connection error
if ($mysqli->connect_error) {
die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
}
$i = 0;

foreach ($_REQUEST as $val) {
if (isset($_REQUEST['submit'])) {

$year = $_REQUEST['field_name'][$i];

$mysqli->query("INSERT INTO firearms_id_secs (year) VALUES ('$year')");
$i++;
} 
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Add more fields using jQuery</title>
<script src="jquery.js"></script>
<script type="text/javascript">
$(document).ready(function(){
var maxField = 10; //Input fields increment limitation
var addButton = $('.add_button'); //Add button selector
var wrapper = $('.field_wrapper'); //Input field wrapper
var fieldHTML = '<div><input type="text" name="field_name[]" value=""/><a     href="javascript:void(0);" class="remove_button" title="Remove field"><img     src="remove-icon.png"/></a></div>'; //New input field html 
var x = 1; //Initial field counter is 1
$(addButton).click(function(){ //Once add button is clicked
    if(x < maxField){ //Check maximum number of input fields
        x++; //Increment field counter
        $(wrapper).append(fieldHTML); // Add field html
    }
});
$(wrapper).on('click', '.remove_button', function(e){ //Once remove button     is clicked
    e.preventDefault();
    $(this).parent('div').remove(); //Remove field html
    x--; //Decrement field counter
});
});
</script>

<style type="text/css">
input[type="text"]{height:20px; vertical-align:top;}
.field_wrapper div{ margin-bottom:10px;}
.add_button{ margin-top:10px; margin-left:10px;vertical-align: text-bottom;}
.remove_button{ margin-top:10px; margin-left:10px;vertical-align: text-    bottom;}
</style>
</head>

<body>
<form name="codexworld_frm" action="" method="post">
<div class="field_wrapper">
<div>

    <a href="javascript:void(0);" class="add_button" title="Add field"><img src="add-icon.png"/></a>
</div>
</div>
<input type="submit" name="submit" value="SUBMIT"/>
</form>

</body>
</html>

It is better to do one insert query that hitting the database several time. Now you only have a couple of entries, but think about a case where you have millions. See https://stackoverflow.com/questions/6889065/inserting-multiple-rows-in-mysql — Elzo Valugi, Mar 22 '16 at 09:27

score 1 · Accepted Answer · answered Mar 22 '16 at 07:19

1

Instead of this code,

foreach ($_REQUEST as $val) {
if (isset($_REQUEST['submit'])) {

$year = $_REQUEST['field_name'][$i];

$mysqli->query("INSERT INTO firearms_id_secs (year) VALUES ('$year')");
$i++;
}

Use this code:

if (isset($_REQUEST['submit']) && isset($_REQUEST['field_name'])) {
  foreach ($_REQUEST['field_name'] as $val) {
    $mysqli->query("INSERT INTO firearms_id_secs (year) VALUES ('$val')");
  }
}

Explanation:

You are only ing the value of field_name, so loop over it.

No need for increment counter.

answered Mar 22 '16 at 07:19

Pupil

23,834
6
44
66

for first loop you need use `for` not `foreach`, because you need increment `$i` and you `foreach $_REQUEST` but get `REQUEST['field_name']` don using `$val` this error! – Naumov Mar 22 '16 at 07:23
@Naumov, please read the program once again. And if possible, try to run it on your machine. Its not mandatory to use OP's logic for answering. The answer should work, that is the criteria. – Pupil Mar 22 '16 at 07:26
There is no need of `for`. The program will become complex unnecessarily. – Pupil Mar 22 '16 at 07:27
you can simple bind parameters as array to query for multiinsert https://dev.mysql.com/doc/refman/5.5/en/insert.html. sorry I don't understand finaly your answer I can up vote – Naumov Mar 22 '16 at 07:32
Yes, we do it. Its a different approach. I gave the answer with my little knowledge. – Pupil Mar 22 '16 at 08:38

score 1 · Answer 2 · answered Mar 22 '16 at 07:47

1

try this

    for($i=0;$i<=count($_REQUEST);$i++){
    $year = $_REQUEST['field_name'][$i];
    $mysqli->query("INSERT INTO firearms_id_secs (year) VALUES ('$year')");
}

answered Mar 22 '16 at 07:47

kaushik

903
7
16

1

have you tries this? – kaushik Mar 28 '16 at 04:55
1

i tried your code and than after i put solution here. – kaushik Mar 28 '16 at 04:55

score 1 · Answer 3 · answered Mar 22 '16 at 07:57

1

No need to use one query, Take two query's and you're fine.

answered Mar 22 '16 at 07:57

Chris Meller

179
1
1
8

score 1 · Answer 4 · answered Mar 22 '16 at 08:00

1

You can do like this:

$newdata = "('" . implode("'), ('", $_REQUEST['field_name']) . "')";

$mysqli->query("INSERT INTO firearms_id_secs (year) VALUES $newdata");

answered Mar 22 '16 at 08:00

Md Mahfuzur Rahman

2,319
2
18
28

How to insert more than 2 queries in MySQL using arrays

4 Answers4