CODEWITHSUNDEEP
phpmysqldatabasedatabase-design
MikeyC343
MikeyC343

Reputation: 139

Website with a list local to the user only to store data, simple table

Here's what I've done. Its a website with a login page. Once logged in, you have access to a table in which you can modify to hold information on comic books (eg. which ones you own, issue, volume, etc.). I have a MySql database with two tables, the user info and the comic info. I have it set up that if you are in the User table (e.g. registered) then you can access the database but the database is global. Everyone included in users will see the same list of comics.

(Sorry for the long explanation just want to be as clear as possible)

I'm not worried about security because it's only for about 10 people (friends) who will use it and I'm also relatively new to PHP and mysql. The above work is a result of many hours of research and tutorials.

My question is, how would I go about making each user have they're own personal list in which they can modify to add they're own comic info and only they can access once logged in?

Upvotes: 0

Views: 106

Answers (2)

spencer7593
spencer7593

Reputation: 108410

One approach is to add a 'user_id' column on your comicbook table.

Your SELECT queries could then be modified to add a predicate in the WHERE clause

SELECT c.id
 FROM comicbook c
 WHERE c.user_id = ?

You supply the user_id value for the user that is "logged in" in the query, in place of that question mark.

For DML operations INSERT statements, you insert the value of the "logged in" user to the user_id column.

For UPDATE and DELETE operations, you add a predicate to the WHERE clause like you did for the SELECT.

With this approach, you are effectively making the single comicbook table look like a separate table for each logged in user, where each user can view and update ONLY their "section" of the table, and cannot view or modify rows that are not "marked" with their user_id.

This is probably the simplest approach, which achieves your stated goal.

There are some requirements that are not met with this approach, like when you want to start giving "privileges" to some users to view other user's comics, but not all. But when you get to implementing that, you will still have the idea that a row in the comicbook table "belongs" to a user.

Upvotes: 1

Sebastian Paaske Tørholm
Sebastian Paaske Tørholm

Reputation: 50868

Create a table with two fields, user_id and comic_id.

Each entry in that table corresponds to the user with id user_id owning the comic with id comic_id.

This is what is commonly referred to as a many-to-many relation between users and comics.

Further material:

Upvotes: 3

Related Questions