PHP user dasboard with user set options

Question

Im trying to start building a dashboard filled with user set options such is the user profile private/public and adding "Subjects" with their own variables that stay on database.

I already have table for users. All works fine.

With these properties:

 - user_id
 - user_name
 - user_email
 - user_pass

How do I proceed if I want to system that user can add text such as their favourite color into the database without creating new table for every new user that register into the system.

You should probably start with just about any PHP/MySQL tutorial. Almost all of them would cover creating and using database tables. And no, you don't create a table for *each user*. You create a table of users, and *each row* in that table represents a user. — David, Jul 13 '15 at 12:07
Yes that is exactly what Ive done. But where do I store the info that user adds? — Skege, Jul 13 '15 at 12:11
You store information in the database. Again, a tutorial will cover that. "How do I store data in a database" is a bit too broad for a Stack Overflow question. If you've made some attempt and have encountered some issue, we can help with that. But Stack Overflow isn't a replacement for an introductory tutorial. — David, Jul 13 '15 at 12:13
Im well aware of that David. Im not asking tutorial or any examples whatsoever. I just want the "how" should I proceed creating table for specific user. — Skege, Jul 13 '15 at 12:16
You *don't* create a table for each user. The tables define the *structure* of the data, the rows in those tables define the *values* of the data. If the table you have doesn't provide the structure you need, then you need to either add columns to that table or add related tables with foreign keys to that table. — David, Jul 13 '15 at 12:18

HenryTK · Answer 1 · 2015-07-13T12:17:13.893

0

An example would be:

CREATE TABLE IF NOT EXISTS user (
user_id SERIAL, 
user_name NOT NULL VARCHAR(32), 
user_email VARCHAR(254), 
user_pass VARCHAR(128) NOT NULL
);

SERIAL is short for BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, you do not need to insert values in here, MySQL will handle that automatically.

Official documentation for MySQL 5.5

Once this table is set up you can insert rows:

INSERT INTO user (user_name, user_email, user_pass) 
VALUES ('some name', 'email@domain.com', 'pass');

edited Jul 13 '15 at 12:17

answered Jul 13 '15 at 12:13

HenryTK

1,287
8
11

I already have this for User. Im missing the userdata table that stores all the data that user puts in. – Skege Jul 13 '15 at 12:16
So you want to know how to insert a row into the table? – HenryTK Jul 13 '15 at 12:18
When User X fills the form and submits it. Data goes into Database under his own user. User X can only see his data. – Skege Jul 13 '15 at 12:22
I find it hard to understand that you put all users data under one table. Is it that way? – Skege Jul 13 '15 at 12:23
Yes. All users in one table. It is the user_id that needs to be unique to ensure you access the right data, and allow you to create related tables. That's why the id is usually a number that automatically increments when you add a new row, to ensure uniqueness. – HenryTK Jul 13 '15 at 12:25
Yes of course. But I was talking about the user set data. Such as favourite color. – Skege Jul 13 '15 at 12:29

score 0 · Accepted Answer · answered Jul 13 '15 at 12:52

It sounds like you're asking how to create a table structure in which the user can define their own schema. For example, if users are defining their own key/value pairs of information and each user would be different.

There are a couple of approaches to take...

First, give considerable thought to what you're doing. Does the user experience really call for this? Or would some free-form text fields suffice? What else in the system needs these to be individually tracked key/value pairs?
You could use a schema-less database, such as a document store in which any given record can have any serializable structure.
In a relational database, this generally boils down to simply storing the key/value pairs. Something like this:

Users
----------
id (PK)
username
password
email

UserValues
----------
id (PK)
userid (FK to Users)
key
value

With this, any given user can store as many key/value pairs as they like. And you can continue to expand on this structure to add more user data structures if necessary. One significant drawback to this is that everything becomes "stringly typed" which means that if a value should be numeric then your code needs to be robust enough to validate that anywhere the value is used, because in the backing data everything here is just going to be strings.

This is basically what I was asking for. Im really sorry that I couldnt be any clearer. By-the-way what does the FK stand for? — Skege, Jul 13 '15 at 13:05
@JaakkoUusitalo: "Foreign Key". It's basically a connection between the two tables to enforce referential integrity of the data. And no worries about not being clear, the internet is a famously difficult place for people to communicate effectively. At least we weren't arguing politics on Facebook :) — David, Jul 13 '15 at 13:08

PHP user dasboard with user set options

2 Answers2