Storing an array of unknown length

Question

I'm letting my users add number inputs in a HTML form and filling them with values. The form is serialized on submit and sent to a PHP file that inserts the data into a MySQL database with PDO. The database table has a bunch of columns for storing other values from the form, but the columns for storing the user added inputs are set up like this:

input_value_1
input_value_2
input_value_3
input_value_4
...
...
input_value_10

These columns allows NULL since I don't know if the user will add any number inputs at all.

Is there a better way of storing these numbers?

Below is the part of my JS for getting form data and sending to LandOwners.php which inserts the values to my database. Code is without the number inputs (I haven't added them yet since I'm not sure how I should store their data).

$("#createLandOwnerForm").on( "submit", function( event ) {
    event.preventDefault();

    createLandOwner($(this).serialize(), appendCreatedLandOwnerToSelect, appendCreatedLandOwnerToSelect_Error);

    $('#createLandOwnerForm')[0].reset();
});

function createLandOwner(landOwner, onSuccess, onError) {    
     var data = landOwner + "&action=create";

     $.ajax({
        type: "post",
        url: host + 'LandOwners.php',
        data: data,
        success: onSuccess,
        error: onError
     });
}

Below is the part of LandOwners.php which is inserting to mydatabase without the number inputs (I haven't added them yet since I'm not sure I how/if should).

$stmt = $pdo->prepare("INSERT INTO land_owner (land_owner_name, land_owner_identification_number, land_owner_contact, land_owner_phone, land_owner_email, land_contracts) VALUES (?, ?, ?, ?, ?, ?)");
$stmt->execute([$land_owner_name, $land_owner_identification_number, $land_owner_contact, $land_owner_phone, $land_owner_email, $land_contracts]);

$last_inserted_land_owner_id = $pdo->lastInsertId();

$stmt = $pdo->prepare("SELECT * FROM land_owner WHERE land_owner_id = ?");
$stmt->execute([$last_inserted_land_owner_id]);

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  $arr[] = $row;
}

if(!$arr) exit('No rows');

echo json_encode($arr);

$stmt = null;

Below is the part of LandOwners.php that selects data from my database. I want to (still be able to) get the result as JSON.

$arr = [];

if (isset($_POST["land_owner_id"])){
  $stmt = $pdo->prepare("SELECT * FROM land_owner WHERE land_owner_id = ?");
  $stmt->execute([$land_owner_id]);
} else {
    $stmt = $pdo->prepare("SELECT * FROM land_owner");
    $stmt->execute();
}

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  $arr[] = $row;
}

if(!$arr) exit('No rows');

echo json_encode($arr);

$stmt = null;
break;

Yes, you should instead have a table with the values and a relation to whatever they're related to - more simple rows instead of many repeating columns is better. — Qirel, Jul 23 '19 at 10:16
@Qirel So I should replace the ten columns with a new column called `input_values` and relate that to a new table with a foreign key? If that is correct, what should `input_values` look like if someone added 15 inputs and how should the new table be structured? Thanks! — Rawland Hustle, Jul 23 '19 at 11:00
@Qirel I have updated my question with my currect code. How would it have to be changed? — Rawland Hustle, Jul 23 '19 at 11:22
Create a `land_owner_input` and put all the inputs there on separate rows, with a reference to the unique identifier of `land_owner` (the ID, probably). — Qirel, Jul 23 '19 at 11:33
Is this the way you mean the setup of the tables should look like? https://i.imgur.com/CESnTyF.png — Rawland Hustle, Jul 23 '19 at 11:48
Looks great! That way you can have an endless, unspecified amount of inputs for each `land_owner`. — Qirel, Jul 23 '19 at 12:01
Perfect! If you add this as answer instead of a comment I'll accept it but that's up to you of course. Thanks! — Rawland Hustle, Jul 23 '19 at 13:04

score 2 · Accepted Answer · answered Jul 23 '19 at 17:54

If you find yourself in a situation where you start having column1, column2 on the table - chances are that your design is getting flawed, and you should instead create a separate table - where each columnX gets a row of its own. It's (nearly) always better to have a separate table if you find yourself having multiple repeating columns on the same table.

That way, you avoid storing comma-separated values in columns, you avoid breaking your code/queries if you suddenly have to introduce another value column{X+1} - and instead can have as many or as few input-values as you need to.

For you, that would be something like a new table called land_owner_input, where you have the value (that you would put in columnX), and a reference to the row in land_owner that the value belongs to.

Typical design pattern would be something like this.

CREATE TABLE land_owner_input (
    land_owner_input_id INT(11) AUTO_INCREMENT
    land_owner_id INT(11), 
    land_owner_input_value VARCHAR(MAX)
);

Keep in mind that your land_owner_id in the new table should be of the exact same type and size as the ID its referencing.

You can also create a foreign key constraint between the land_owner_id and the ID of the land_owner table to ensure data integrity.

Once you have your new table, you can query them together by using a LEFT JOIN (or a normal JOIN if you only want to return rows if it has input-values).

SELECT *
FROM land_owner AS lo
LEFT JOIN land_owner_input AS loi
    ON loi.land_owner_id = lo.land_owner_id

Thanks for your answer, I really appreciate your time and help! — Rawland Hustle, Jul 23 '19 at 18:22
I'm using your solution to store my data but I've been struggling for many hours with a problem related to it. Can you please have a look? https://stackoverflow.com/q/57209240/1711950 — Rawland Hustle, Jul 26 '19 at 10:59

score 0 · Answer 2 · answered Jul 23 '19 at 10:20

Maybe you cold have one column, json type, and store json in it?

Something like:

CREATE TABLE IF NOT EXISTS some_table
(
  `json` JSON NOT NULL
  ) COLLATE='utf8_general_ci' ENGINE=InnoDB;

And than you can store JSON to this column, like:

{"input_value_1":1, "input_value_2",...."input_value_10":10}

You culd than easily get single value (assuming they will always have same keys) with JSON_EXTRACT:

SELECT JSON_EXTRACT(json,'$.input_value_'.$x) FROM some_table WHERE some_condition

I have updated my question with my currect code. How would it have to be changed? — Rawland Hustle, Jul 23 '19 at 11:25

Storing an array of unknown length

2 Answers2

Linked