存储未知长度的数组 [英] Storing an array of unknown length

问题描述

我让我的用户以HTML格式添加数字输入，并用值填充它们.该表单在提交时被序列化，并发送到一个PHP文件，该文件使用PDO将数据插入到MySQL数据库中.数据库表中有一堆列，用于存储表单中的其他值，但是用于存储用户添加的输入的列的设置如下:

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

这些列允许NULL，因为我不知道用户是否会添加任何数字输入.

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?

下面是我的JS的一部分，用于获取表单数据并将其发送到LandOwners.php，后者将值插入到我的数据库中.代码是没有的数字输入(因为我不确定应该如何存储它们的数据，所以我还没有添加它们).

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
     });
}

以下是LandOwners.php的一部分，该部分将无插入到我的数据库中(我尚未添加它们，因为我不确定我应该/应该如何).

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;

下面是LandOwners.php的一部分，它从我的数据库中选择数据.我想要(仍然能够)将结果作为JSON.

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;

推荐答案

如果您发现自己桌上开始出现column1和column2的情况，则可能是您的设计有缺陷，并且您相反，应该创建一个单独的表-每个columnX都获得一个自己的行.如果发现自己在同一张表上有多个重复的列，最好有一个单独的表.

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.

这样，您避免在列中存储用逗号分隔的值，避免在突然需要引入另一个值的情况下破坏代码/查询column{X+1}-而是可以根据需要具有任意数量的输入值到.

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.

对您来说，这就像是一个名为land_owner_input的新表，其中具有值(您将在columnX中放置)，并引用该值所属的land_owner中的行

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)
);

请记住，新表中的land_owner_id的类型和大小应与其引用的ID完全相同.

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.

还可以在land_owner_id和land_owner表的ID之间创建外键约束，以确保数据完整性.

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.

一旦有了新表，就可以使用LEFT JOIN(如果只想返回具有输入值的行，则可以使用普通的JOIN)一起查询它们.

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 

这篇关于存储未知长度的数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！