Concatenation of multiple user entries and submission into a single MySQL database entry

Question

I am referencing to a question here: Can I concatenate multiple MySQL rows into one field?

In this question multiple rows of a column are listed and separated by a "," using the GROUP_CONCAT function. I want to achieve something in reverse by concatenating multiple user inputs into a single database entry. Something like this:

<form action="server.php" method="POST">
    <div>
        <input type="text" name="value1">
        <input type="text" name="value2">
        <input type="text" name="value3">
        <button type="submit" name="submit">submit</button>
    </div>  
</form>

and php:

<?php
if (isset($_POST['submit'])) {  
    $value1 = mysqli_real_escape_string($conn, $_POST['value1']);
    $value2 = mysqli_real_escape_string($conn, $_POST['value2']);
    $value3 = mysqli_real_escape_string($conn, $_POST['value3']);

    $sql = "INSERT INTO database (col1)
    VALUES GROUP_CONCAT('$value1', '$value2', '$value3');";
    mysqli_query($conn, $sql);
    header("Location: ../web_page/client_database_page.php?add_client=success");
}
?>

I know some of you will say that it would not be good practice to do this and I should have an individual column for each user input, however there is a reason for not doing it this way. The user inputs are added based on the number of variables from another database. In this database a user can insert additional user inputs from the website, but it would not automatically add a column to the input database. So a single column row should be able to contain all the user inputs and than later be separated for interpretation when called from the database.

Anybody have any ideas?

_“however there is a reason for not doing it this way”_ - your reason is bogus; go read up on database _normalization_ – that is the answer, not what you are trying to do here. — CBroe, Jun 25 '18 at 10:14
I am familiar with database normalization, thank you for your insightful comment. — Mariska, Jun 25 '18 at 11:11
To provide you with the opportunity to make sub-textual condescending comments without providing any helpful information to the problem at hand. — Mariska, Jun 25 '18 at 11:21
That _is_ the most helpful advice you can get in this situation IMHO. (That you possibly lack the knowledge to see that yet, might be a different issue.) — CBroe, Jun 25 '18 at 11:24
Look man, this is part of database normalization. I have another database from which the id's will represent the user input so that they can select the id's they wish and submit it in another database representing a foreign key. It won't be helpful if I have 1000 id's for a 1000 user inputs, it is just easier to place them in one variable and call them as needed. By the way, users won't be able to access the database if new user input variables are added to that database. — Mariska, Jun 25 '18 at 11:54
_“it is just easier to place them in one variable and call them as needed”_ - that’s what many newbies think … and then they come running here later, when they have to deal with the problems arising from this wrong initial decision. — CBroe, Jun 25 '18 at 11:59

score 0 · Answer 1 · answered Jun 25 '18 at 08:52

0

You can simply concat values using PHP. No need to use GROUP_CONCAT for this. Try code like below:

$sql = "INSERT INTO database (col1) VALUES ('$value1 $value2 $value3');";

Note: Values can be separated by comma , or any other separator instead of space.

answered Jun 25 '18 at 08:52

Lovepreet Singh

4,792
1
18
36

How about if the multiple names are the same? So user input 1 the `name="value"`, and user input 2 `name="value"` and so on... The number of inputs depend on how many entries are in the reference database and are systematically added to the page as copies. All with thus have one variable e.g. `$value` for multiple user inputs. – Mariska Jun 25 '18 at 08:56
It's up to your code, how you coded things. Here solution provided to your current problem. – Lovepreet Singh Jun 25 '18 at 08:59

ubuntux · Accepted Answer · 2018-06-25T10:08:35.390

0

How about grouping your input values as an array then using implode() function in PHP before you insert into DB, like:

<form action="server.php" method="POST">
    <div>
        <input type="text" name="values[]">
        <input type="text" name="values[]">
        <input type="text" name="values[]">
        <button type="submit" name="submit">submit</button>
    </div>  
</form>

Then, in PHP:

if (isset($_POST['submit'])) {  
    $values = $_POST['values'];
    $escaped_values = array_map([$conn, 'mysqli_real_escape_string'], $values);
    $concat_values = implode(",", $escaped_values);

    $sql = "INSERT INTO database (col1) VALUES ('$concat_values');";
    mysqli_query($conn, $sql);
    header("Location: ../web_page/client_database_page.php?add_client=success");
}

Here, I used comma , as separator on each values. Just change it to your preference.

EDIT:
Another solution would be to use JSON for this so you can easily access the data when retrieved. Depending on your MySQL version, you can use the JSON data type for the col1 column/field.

ALTER TABLE `table_name` CHANGE COLUMN `col1` `col1` JSON;

Then modify the code to:

$json = json_encode($_POST['values']);
$sql = "INSERT INTO database (col1) VALUES ('$json');";

And then later when you retrieve the data you can do something like:

$values = json_decode($row['col1'], true);

Which you can then iterate to echo multiple <input> tags with values taken from the db.

edited Jun 25 '18 at 10:08

answered Jun 25 '18 at 08:59

ubuntux

394
3
12

I think this gets close to my solution, can be continue in discussion so I can elaborate on my script please? – Mariska Jun 25 '18 at 09:21
Do you know how to go to chat from here? – Mariska Jun 25 '18 at 09:29
I made some update on my answer... and no, sorry I don't know about chat here in SO. – ubuntux Jun 25 '18 at 09:42
I am getting a syntax error when trying to alter the table. Says the error is near JSON – Mariska Jun 25 '18 at 09:55
Could be your MySQL version... I think the support for JSON data type was included on MySQL 5.7.8 (and higher). If your MySQL doesn't support JSON, use the TEXT/LONGTEXT data type instead. – ubuntux Jun 25 '18 at 09:58
My version is 10.1.32-MariaDB... From the phpMyAdmin info. – Mariska Jun 25 '18 at 10:03
@Mariska, I believe JSON is not supported on your MariaDB version. And according to [MariaDB's KB](https://mariadb.com/kb/en/library/json-data-type/), it was only added on 10.2.7 as an alias to LONGTEXT. In your case, just use LONGTEXT data type. – ubuntux Jun 25 '18 at 10:07

Concatenation of multiple user entries and submission into a single MySQL database entry

2 Answers2