Inserting data into two tables with one based on others id

[jack55]

Hi this is probably the easiest question you are ever going to answer but I have a registration form and currently all the data goes into one table (users) but on registration i want to soon stop having all the data in one table and have a dynamic table where you can add as many fields(columns) as you want to insert into the ‘users_profile’ table based on the id just generated for the other insert but to use it as user_id instead!

In simpler terms:
Register → Insert into users table then use the id for the insert as the user_id in the insert for the users_profile table → Then user password, emails etc all in main table and in user_profile its inserted a row with user_id as the id of the user in the user table!

Basically how do i go about starting it as I cannot find anything online for help? All help much appreciated!

This is my current insert code for my custom built MVC.

    public function registerUser($data)
    {
        $insert = [
            'pin' => $data['pin'],
            'username' => $data['username'],
            'email' => $data['email'],
            'name' => $data['name'],
            'password' => $data['password'],
			'role' => $data['role'],
            'profile' => $data['profile'],
            'country' => $data['country'],
            'reset_code' => $data['reset_code'],
            'verify_code' => $data['verify_code'],
            'last_login' => $data['last_login'],
            'last_ip' => $data['last_ip'],
			'status' => $data['status'],
			'activate_emails' => '1',
			'agree_terms' => $data['agree_terms'],
        ];
        $this->db->insert('chewi_users', $insert);
        return true;
    }

Thanks very much!

[chorn]

Whatever class you use to make the insert is not part of PHP itself, so you have to check out the code and look if the class can give you back the last_insert_id, that you can use.

[rpkamp]

Sounds like you are looking for entity-attribute-value. Basically you create a table with columns user_id, attribute and value. That could for example contain a record like user_id=1, attribute=hobbies,value=soccer,reading

Then when you want to fetch data for a user just fetch all the rows where the user_id is the one you’re looking for.

This works really well if you always read a single user at the time, but breaks down once you want to do aggregations over the data (for example lets say age is in attribute and you want to know the average age of all users. This gets worse the more attributes you use at once).

Another drawback is that you can’t have the database check for types because the value collumn must be a VARCHAR (or TEXT) as the are the most versatile. For example if one of the fields is a date then the database will just accept it as a string and not validate it. Which is no problem per se, but does put a stronger onus of validation on your application.

[jack55]

Thankyou very much for the reply! In that case would you suggest I just continue doing it inside the users table and not create another? Also the data I was going to store was going to be things like a bio, social media etc so nothing too fancy! All the important data would be inside the users table!

[m_hutley]

Why is user_profile not a part of a user? Sounds like you’ve invented a table for no discernable entity.

If a user leaves a column blank… then it’s blank.

If your user is DEFINING columns, thats a different matter, and you need the entity table as described above.

[rpkamp]

@jack55

In that case would you suggest I just continue doing it inside the users table and not create another?

No it must be another table with just three columns I described earlier.

[jack55]

It is part of it! But its another table so I can add rows easily and it just looks cleaner!

So you have user table with id then you have user profile table which has user_id which is how the two both talk to eachother.

[droopsnoot]

If this is Codeigniter query builder, you can use

$id = $this->db->insert_id();

to get the id of the last inserted row, just after you’ve inserted it.

https://www.codeigniter.com/userguide3/database/helpers.html