PHP查询—当用户id存储在另一个表中时，如何添加新列并在表中插入用户信息?

so I'm new to PHP, currently I'm making an PHP OOP authentification system. The login and registration works fine, but now I need to have an option for users to add unlimited attributes(input column name and value) to his profile, but the trick is that I need to store the attributes in another database table. So this is my users table.

我是PHP新手，我现在正在做一个PHP OOP认证系统。登录和注册工作正常，但是现在我需要为用户添加无限制的属性(输入列名和值)到他的配置文件中，但是关键是我需要将属性存储在另一个数据库表中。这是我的用户表。

uid upass      fullname      uemail
1   md5(pass)  John Snow     [e-mail]
2   md5(pass)  John Doe      [e-mail]

And i think my new table should look something like this.

我想我的新桌子应该是这样的。

id  uid  age  professsion   gender
1   1    17   haha          male
2   2    62   eee           male

and so on. This system is just for educational purposes, it probably doesn't make any sense, that user can add unlimited attributes, but well it is what needs to be done. I'm hoping someone can help. Thanks in advance.

等等。这个系统只是为了教育目的，它可能没有任何意义，用户可以添加无限的属性，但这是需要做的。我希望有人能帮忙。提前谢谢。

EDIT

编辑

Ok i have made this function, but what do i have to echo out?

好的，我已经做了这个函数，但是我需要做什么回波呢?

        public function get_property($uid)
{
        //$query = "SELECT property,value FROM info WHERE uid='$uid' AND property='Gender'";

        $query = "SELECT uid, CONCAT('{', GROUP_CONCAT(property, ': ', value), '}') AS json FROM info GROUP BY uid";

        $result = $this->db->query($query) or die($this->db->error);

        $user_data = $result->fetch_array(MYSQLI_ASSOC);
            echo $user_data['property'];
}

OH i guess it's json

我猜是json

5 个解决方案

#1

I don't think that your proposed new table is a good choice, in particular because you mentioned that the user needs to be able to store unlimited attributes. Your suggested design places each attribute into a separate column, but this could become unwieldy as the number of attributes really starts to get large. Instead, I propose something like this:

我认为您建议的新表不是一个好的选择，特别是因为您提到用户需要能够存储无限的属性。您建议的设计将每个属性放置到一个单独的列中，但是这可能会变得非常笨拙，因为属性的数量开始变得很大。相反，我提议如下:

uid | property   | value
1   | age        | 17
1   | profession | haha
1   | gender     | male
2   | age        | 62
2   | profession | eee
2   | gender     | male

Now adding a new property just means inserting a record. Note that this table can easily be queried. If you wanted to find all male users you could do:

现在添加一个新属性仅仅意味着插入一个记录。注意，这个表很容易被查询。如果你想找到所有男性用户，你可以:

SELECT uid
FROM yourTable
WHERE
    property = 'gender' AND value = 'male'

This design is flexible and we could index the property and value columns to improve performance.

这种设计是灵活的，我们可以索引属性和值列以提高性能。

Edit:

编辑:

If you wanted to output each user's properties in a sort of JSONeqsue format, you could try using GROUP_CONCAT e.g.

如果您想以JSONeqsue格式输出每个用户的属性，可以尝试使用GROUP_CONCAT。

SELECT
    uid,
    CONCAT('{', GROUP_CONCAT(property, ': ', value), '}') AS json
FROM yourTable
GROUP BY uid;

Output:

输出:

    uid json
1   1   {age: 17,profession: haha,gender: male}
2   2   {age: 62,profession: eee,gender: male}

Demo

#2

I would create another table, named "users_data", with two fields. One beeing the user id and the second beeing a BLOB, containing the fields of the user which he created. BLOBs are stored in serialized form, which you then can unserialize in PHP.

我将创建另一个表，名为“users_data”，包含两个字段。一个是用户id，另一个是BLOB，包含用户创建的字段。blob以序列化形式存储，然后您可以在PHP中对其进行序列化。

Doing it this way, you dont have to edit the database, rather then just adding data to the users data array or removing it.

这样做，您不必编辑数据库，而只需将数据添加到用户数据数组或删除它。

---

You can select it by using something like this:

您可以使用以下方法来选择:

SELECT users.*, users_data.data FROM users JOIN users_data ON users_data.user_id = users.id

---

IMO this approach is even simpler to accomplish then doing it the way you described.

在我看来，这种方法比你描述的更简单。

#3

I think you can create a json object of user and map with id in other table and save json in one column against id. You can extract json easily and decode it properly.

我认为您可以创建一个用户的json对象，并在其他表中使用id映射，并将json保存在一个列中的id中。

#4

If the User can input anynumber of dynamic attributes then you need to alter your table structure as below

如果用户可以输入任意数量的动态属性，则需要更改表结构，如下所示

user_tbl

user_tbl

uid upass      fullname      uemail
1   md5(pass)  John Snow     [e-mail]
2   md5(pass)  Adolf Hitler  [e-mail]

user_attr_tbl [If any user inputs a new attr_name that's not existing in the table then you need to insert it to here.]

user_attr_tbl[如果有用户输入表中不存在的新attr_name，则需要将其插入到这里]。

id       attr_name   
1        age              
2        professsion       
3        gender  

user_attr_values

user_attr_values

id uid  user_attr_id    value
1   1       1            17
2   1       2            haha
3   1       3            male
4   2       1            62
5   2       2            eee
6   2       3            male

#5

You can create other table for storing additional user details with a table_name like 'user_attributes' or 'user_details' (or) your wish. Then You have to store 'user_id' while storing the additional data for user So that you can fetch all the additional user information by using Mysql JOIN's.

您可以创建其他表来存储附加的用户详细信息，比如“user_attributes”或“user_details”(或)您的愿望。然后，您必须存储“user_id”，同时为用户存储额外的数据，这样您就可以通过使用Mysql连接来获取所有附加的用户信息。

users

用户

uid          upass              full_name        uemail 
 1         md5('something')    James Anderson   [e-mail]
 2         md5('123456')        Brett Lee       [e-mail]

Your user_details would be like below:

您的user_details如下所示:

id   uid      age      professsion       gender
 1    1       25        Faculty           Male
 2    2       26        Entreprenuer      Male

You can fetch the data by using any one of Mysql JOIN's like below:

您可以通过以下任何一种Mysql连接来获取数据:

SELECT users.*,user_details.age,user_details.profession FROM users INNER JOIN user_details ON users.uid=user_details.uid