Storing an array of unknown length

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;

Answer

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