MySQL database entries only inserting the last user input from multiple form input clones

Question

Example of the SQL/php code, the second query is applicable. I have created a user input that submits the id variables into a MySQL database. These user inputs are cloned multiple times within the form, thus resulting in multiple duplicate id's/names, however one column (lets call it column 1) is different for each clone.

Now, once the input has been added and the form is submitted it only appends the last form entry into the database table, which I expected.

Is there a way that all these copies can be added to the table, perhaps based on the variance of Column 1? I was thinking something like: for each Column 1 INSERT INTO database1 ...

EDIT

Some code:

HTML:

<style> 
.hidden {
    display: none;
}
</style>

<body>
    <div id="samplesinfo" class="samplesinfo hidden">
        <input type="text" id="column1" name="column1" value="1" readonly>
        <input type="text" name="column2">
    </div>
    <button id="btn">Paste</button>
    <div>
        <h3>Paste Below</h3>
    </div>
    <form>
        <div id="paste">    
        </div>
        <button type="submit" name="submit">Submit</button> 
    </form>

jQuery:

$(document).ready(function() {
        var sample = 1;
        $("#btn").click(function() {
            var element = $(".samplesinfo.hidden").clone(true);
            element.removeClass("hidden").appendTo("#paste:last");
            sample++;
            $("#column1").val(sample)
        });

    });

and php:

if (isset($_POST['submit'])) {  
    $column1 = mysqli_real_escape_string($conn, $_POST['column1']);
    $column2 = mysqli_real_escape_string($conn, $_POST['column2']);

    $query = "INSERT INTO db1 ('column1', 'column2')
    VALUES ('$column1', '$column2');";

    mysqli_query($conn, $query);
    header("Location: ../web_page/analysis_page.php?add_order=success");
}

How does your form look ? In general, you should give every input it's own name, so you can tell the difference when you get the posted information. If there's no sensible difference, just enumerate them like name1, name2, etc and then have your storage script enumerate them the same way. — Gloweye, Jun 21 '18 at 09:31
@JaccovanDorp the problem is that the clones are based on user preference. So the user would select the amount of clones needed. — Mariska, Jun 21 '18 at 09:33
@samezedi I would love to show you the code, but then I would have to attach the entire project which is quite extensive. — Mariska, Jun 21 '18 at 09:34
maybe you could just do a screen shot of the code which inserts into the db table — samezedi, Jun 21 '18 at 09:37
_“I would love to show you the code, but then I would have to attach the entire project”_ - no, you would not and you should not. _Reducing_ your code to a [mcve] that is able to reproduce the problem is your responsibility. You have to put in the work necessary to create such a minimal example, not just dump your entire code here for others to sift through. — CBroe, Jun 21 '18 at 09:39
@CBroe Which is why I am not dumping the code... The script is quite integrated with various languages, database tables and file links, a simplified example is just not viable. — Mariska, Jun 21 '18 at 09:41
@samezedi I have attached a picture. The "order_id" name is the one that changes along with each clone. — Mariska, Jun 21 '18 at 09:45
_“a simplified example is just not viable”_ - well, then asking here to get your problem solved maybe isn’t either. — CBroe, Jun 21 '18 at 09:46
But at least the few lines of the screenshot you have shown now, could easily be inserted into your question as a code sample directly. So please do at least that. — CBroe, Jun 21 '18 at 09:48
Form elements with the same name get _overwritten_ when PHP parses the data, unless you use the “special syntax” using `[]` in the field name. But _“thus resulting in multiple duplicate id's/names”_ is really rather the thing that you should fix. `$("#column1").val(sample)` is likely not even addressing the field you intended here, _because_ you messed up with the ids. I would suggest that you leave ids and names out of your “template” completely, and only set those dynamically when you clone the elements and insert them into the document. — CBroe, Jun 21 '18 at 11:03
And then of course you will also need some sort of loop in the place where you are inserting the data into the database, because right now you are inserting only one single record at that point. — CBroe, Jun 21 '18 at 11:04
How are those elements cloned ? If it's with Javascript, just have it remember how many there are, and let it assign new, unique names. Much like rkeet's form in his answer. — Gloweye, Jun 22 '18 at 06:57

rkeet · Accepted Answer · 2018-06-21T11:11:04.127

1

That only the new inputs are send to the back-end is because the original #column1 and #column2 are outside of the <form>. If you want to send everything, put it in the <form>.

Next, if you want to insert everything into the database and simultaneously update existing records if they're changed you might want to read this.

If you want to add multiple records of the same object, you must send them to the back-end as the same type of records and then treat them as an array.

<form>
    <input name="column1[0][name]" value="first one">
    <input name="column1[1][name]" value="second one">
    <input name="column1[2][name]" value="third one">
</form>

Submitting this example would get you 3 column1 array entries with a name and associated values.

// example value of received data
$_POST = [
    'column1' => [
        0 => [
            'name' => 'first one',
        ],
        1 => [
            'name' => 'second one',
        ],
        2 => [
            'name' => 'third one',
        ],
    ],
];

To dump all into database use your query in a foreach() loop. Such as:

foreach ($_POST['column1'] as $column) {
     $query = "INSERT INTO db1 ('column1') VALUES (" . $column['name'] . ");";
}

Obviously you'd have to update it a bit here 'n' there for your use-case and don't forget to sanitize this data.

edited Jun 21 '18 at 11:11

answered Jun 21 '18 at 10:38

rkeet

3,221
2
21
43

I am using your last solution just to dump everything but I am getting this error: syntax error, unexpected '' (T_ENCAPSED_AND_WHITESPACE), expecting '-' or identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING) – Mariska Jun 21 '18 at 10:54
Contained a small syntax error in the sql, fixed that for you. As an added challenge for you: when adding multiple records at once, do it in a single query. Added on top of that: if more than 100 records per query, split into multiple queries and insert via transactions. Just to keep it a challenge ;) – rkeet Jun 21 '18 at 11:13
I am guessing you use the mysqli_multi_query function for multiple queries :). Just a question regarding your sql, do I have to add column2 info as you would normally do after INSERT INTO and then would VALUES be like this: `(" . $column['column1'] . $column['column2']");` – Mariska Jun 21 '18 at 11:23
I think in your example you are only using one column, how about two or more? – Mariska Jun 21 '18 at 11:53
Yes I created a limited example. Found that understanding is aided more when people still have to figure something out :) On that note, yes, the example is just 1 column. If you want column 2 added, add it in the form to be send with it's own input (e.g.: ``). This would create an addition top-level array in your `$_POST` data (`$_POST['column1' => [... its data in answer], 'column2' => [ // column2 data]]`). – rkeet Jun 21 '18 at 12:14
For multiple inserts in single statement you _could do something like this_ (but not limited to): `$sql = "INSERT INTO db1 ('column1', 'column2') VALUES ";` (note the space). Then: `foreach ($_POST as $key => $input) { $sql .= "(" . $input['column1'][$key]['name'] . ", " . $input['column2'][$key]['name'] . ")"; if ($key !== end(array_keys($_POST))) { $sql .= ", "} }` – rkeet Jun 21 '18 at 12:18
Oh I see, that makes sense. Good I think I've got it from here! Thanks! – Mariska Jun 21 '18 at 12:18

Adder · Answer 2 · 2018-06-21T14:19:17.630

I replaced the ids with classes and added a [] to the input field names, so that $_POST['column1'] and $_POST['column2'] are arrays.

HTML:

<style> 
.hidden {
    display: none;
}
</style>

<body>
    <div id="samplesinfo" class="samplesinfo hidden">
        <input type="text" class="column1" name="column1[]" value="1" readonly>
        <input type="text" class="column2" name="column2[]">
    </div>
    <button class="btn">Paste</button>
    <div>
        <h3>Paste Below</h3>
    </div>
    <form>
        <div class="paste">    
        </div>
        <button type="submit" name="submit">Submit</button> 
    </form>

Javascript:

$(document).ready(function() {
        var sample = 1;
        $(".btn").click(function() {
            sample++;
            $(".samplesinfo.hidden").find(".column1").val(sample);
            var element = $(".samplesinfo.hidden").clone(true);
            element.removeClass("hidden").appendTo(".paste:last");
        });

    });

PHP:

if (isset($_POST['submit'])) {  
    $n = count($_POST['column1']);
    for($i = 0; $i<$n; ++$i) {
        $column1 = mysqli_real_escape_string($conn, $_POST['column1'][$i]);
        $column2 = mysqli_real_escape_string($conn, $_POST['column2'][$i]);

        $query = "INSERT INTO db1 (column1, column2)
        VALUES ('$column1', '$column2');";

        mysqli_query($conn, $query);
    }
    header("Location: ../web_page/analysis_page.php?add_order=success");
}

I changed the ids to classes because ids are supposed to be unique, while classes are not unique. Duplicate ids could cause problems. For example, your code `$("#column1").val(sample)` will change all ids to `sample`-value, not just the last one. — Adder, Jun 21 '18 at 11:31
I am not sure if I am doing something wrong, but it results in single entry and the entries are single letters. — Mariska, Jun 21 '18 at 11:59
Did you change the `name="column1[]"` and `name="column2[]"` to have brackets `[]`? — Adder, Jun 21 '18 at 12:48
I did but it now only results in the last input appended in the database. Please also note that the ' ' should be removed from the INSERT INTO variables, this did the trick, thank you! — Mariska, Jun 21 '18 at 13:23

MySQL database entries only inserting the last user input from multiple form input clones

2 Answers2