Insert one or more sets of fields into database using dynamic form

Question

This is my second post on the same issue, in my first post I was using mysql instead of mysqli so I have made some updates according to recommendations. I have a form with variable sets of fields (1 to 20 sets) controlled by the user via javascript.

Without the dynamic content all works well. After I changed my html form from city to city[] it obviously stopped passing the data to my database.

I have tried all sorts of ways to make this work with no success. Any ideas?

Form manipulation:

var i=0; //Global Variable i
//function for increment
function increment(){
    i +=1;
}

$(document).ready(function(e){
    /// Variables
    var html ='<p /><div>';
        html+='<label for="city">City:</label>';
        html+=' <input type="text" name="childcity[]" id="city">';
        html+=' <label for="date">Date:</label>';
        html+=' <input type="text" name="childdate[]" id="date">';
        html+='<a href="#" id="removecity">Remove City</a>';
        html+='</div>';

    var maxRows = 20;
    // var x = 1;

    // Rows
    $("#addcity").click(function(e){
        if(i <= maxRows){
            $("#container").append(html);
            i++;
        }
    });

    // Remove Rows
    $("#container").on('click','#removecity', function(e){
        $(this).parent('div').remove();
        i--;
    });
});

Form data retrieval and querying:

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */

$mysqli = new mysqli("localhost", "root", "", "pb1646a_jos1");

// Check connection

// Escape user inputs for security

$city = $_POST['city'];
$date = $_POST['date'];

foreach($city AS $key => $value){
    // attempt insert query execution
    $sql = "INSERT INTO employe_table(name,age)
    VALUES (
        '".$mysqli->real_escape_string($value)."',
        '".$mysqli->real_escape_string($date['$key'])."'
    )";
    $insert = $mysqli->query($query);
}
$mysqli->close();    // Close connection
header("location: http://www.orastravelagency.com/modules/mod_pari/test/index.html")
?>

Form:

<!DOCTYPE html>
<html lang="en">
<head>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script> 
<script src="java.js" type="text/javascript"></script>
<meta charset="UTF-8">
<title>Add Record Form</title>
</head>
<body>
<form action="insert2.php" method="post">
    <div>
    <p>
        <label for="name">Trip Name:</label>
        <input type="text" name="name" id="name">
    </p>
    </div>
    <div id="container">
    <p>
        <label for="">City:</label>
        <input type="text" name="city" id="city[]">
        <label for="date">Date:</label>
        <input type="text" name="date" id="date[]">
        <a href="#" id="addcity">Add City</a>
    </p>
    </div>
    <p />
    <input type="submit" value="submit">
</form>
</body>
</html>

Barmar · Answer 1 · 2017-05-03T21:58:44.330

1

$date['$key'] should be $date[$key]. The quotes are preventing the $key variable from being evaluated, it's using $key as the literal index.

But you should use a prepared statement rather than concatenating variables.

$city = $_POST['city'];
$date = $_POST['date'];

$stmt = $mysqli->prepare("INSERT INTO employe_table(name, age) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $age);
foreach ($city as $key => $name) {
    $age = $date[$key];
    $stmt->execute();
}

edited May 03 '17 at 21:58

answered May 03 '17 at 21:36

Barmar

741,623
53
500
612

when I try it like that it gives me a server error (also what does the "si" stand for?) – Pari Baker May 03 '17 at 21:47
`si` stands for the first column being a string and the second column being an integer. – Barmar May 03 '17 at 21:56
This page isn’t working www.orastravelagency.com is currently unable to handle this request. HTTP ERROR 500 – Pari Baker May 03 '17 at 21:57
for my test I left the age column as a text field does that matter? – Pari Baker May 03 '17 at 21:58
I changed it to `ss` to match that, although it probably doesn't matter if the values are numeric. MySQL automatically converts. – Barmar May 03 '17 at 21:59
I don't actually know how to check that past the HTTP ERROR 500 – Pari Baker May 03 '17 at 21:59

score 0 · Answer 2 · edited May 23 '17 at 12:17

There are a few things wrong in your process.

You want to generate multiple input fields, and store them as an array in $_POST.

Change this:

However, you are writing the attributes incorrectly, $_POST doesn't read id's:

<input type="text" name="city" id="city[]">
<input type="text" name="date" id="date[]">

To this:

<input type="text" name="city[]">
<input type="text" name="date[]">

I have removed your id attributes because you shouldn't/can't have duplicate id's in your page. If you need to identify these fields for css, then use their name attribute or add a class attribute to identify them.

When you dynamically add your subsequent in javascript, you should be using the name=city[] and name=date[] again; and remove the id's.

This will lead to your $_POST array being a properly filled multi-dimensional array as intended.

$name=$_POST['name'];  // this is a string
$cities=$_POST['city'];  // this is an array
$dates=$_POST['date'];  // this is an array

I don't really understand why city is in your form if you aren't going to include it in your query -- so I included it in my solution. And your table columns are not intuitively named, so consider changing them to something that better represents the values to be stored.

$stmt=$mysqli->prepare("INSERT INTO employe_table (`name`,`city`,`date`) VALUES (?,?,?)");
$stmt->bind_param("sss",$name,$city,$date);
foreach($cities as $index=>$city){
    $date=$dates[$index];
    $stmt->execute();
}

When programming, always endeavor to produce DAMP and DRY work -- it will exponentially help you and anyone else who sees it.

Insert one or more sets of fields into database using dynamic form

2 Answers2

Linked