Quick Question, I know PHP can insert information into tables in MySQL, but can it create them? My project involves when a user signs up for an account PHP will create two (maybe three) personal tables for the user. Is this possible with PHP or do I need another language? For example I know this inserts data into the table (I know the password isn't encrypted) $q = "INSERT INTO test (username, email, pass, entry_date) VALUES ('$u', '$e', '$p', NOW() )"; PHP: Will something like this work? $x = "CREATE TABLE test (username VARCHAR(20) NOT NULL, email VARCHAR(40) NOT NULL, etc..); PHP:
I was just wondering, would creating personal tables for every user be a good idea? Or will it just stress out the server to much?
Really bad idea, can I know why do you want to create table for each user? I think I can suggest you better way
Well, the user registers on the site. PHP then creates 2 tables in a database for him. The user can then enter in information so it is stored in the database. He'll then be able to access that information at a later date. He'll also be able to update that information at any time. For example. A pet shop. They put in information on; Name of Pet; Age; Weight; DOB; etc... What I mean by being able to update the information is, later on the pet shop can come back and change the 'Age' column. That's just an example.
I would recommend that you use one table only and associate the rows with that specific user id: User Id Name of Pet Weight DOB etc.. If the user would have multiple pet's, then I would add a unique id (pet id): Pet Id User Id Name of Pet Weight Dob etc.. I wouldn't normalize this into its own table unless the Name/Weight/Dob/etc.. fields will be shared with multiple pets. In the future, if this table becomes big there are many things you can do to fix that issue including partitioning, sharding or chuncking, I wouldn't worry myself with that at this point of time.
As a rule of thumb, it's NEVER a good idea to create specific tables for individual users. Thinking like that is what creates DB-nightmares like hundreds or thousands of tables with no real correlation, and a spaghetti-code nightmare to actually make sure users access the right table, not to mention the hassle which occur when a user changes his/her login-information, name, email, etc.
Put will this work if the user is entering hundreds of Pets? Won't the table soon become really large?
I wouldn't worry about this now. I have worked with tables that have 100m rows (60 gb of data) that work fine with correctly structured queries. MySQL was built to handle large datasets, if by any chance you get to the point of this is too much for my queries, you can move on to sharding, partitioning, chunking or even use technologies like clusters or caching.
So how would I create the So how would I create the table? Would it be something like this? CREATE TABLE users ( pet_id MEDIUMINT NOT NULL, user_id MEDIUMINT NOT NULL, pet_name VARCHAR(40) NOT NULL, weight MEDIUMINT NOT NULL, etc.. ); Code (markup):
So how would I create the So how would I create the table? Would it be something like this? CREATE TABLE users ( pet_id MEDIUMINT NOT NULL, user_id MEDIUMINT NOT NULL, pet_name VARCHAR(40) NOT NULL, weight MEDIUMINT NOT NULL, etc.. ); Code (markup):
A table becoming large really isn't an issue if it's properly INDEXED and has KEY's. A great example is a forum software where at MOST you usually have the following tables: users -- contains ID (key), name, other details about the user category -- contains ID (key), name and description topics -- contains ID (key), categoryID (indexed), userID (indexed), time and text replies -- contains ID (key), topicID (indexed), userID (indexed), time and text Assuming indexes are maintained properly (which modern SQL engines handle rather nicely) most of them don't take more than 16 'seeks' to go through 65K records (assuming a shifting binary tree), while 32 'seeks' can search through as many as 4 billion records -- so the size of the table does NOT have all that massive an impact unless you're searching non-indexed fields. Numerical indexes also work FAR faster than text ones. So long as your queries are looking for indexes or keys, the overhead isn't that massive. It's why we have indexes and databases in the first place -- and keys+indexes are what puts the 'relational' in 'relational databases'. In your case, if you had owners and pets: owners -- ID (key), data specific to the owner pets -- ID (key), ownerID (indexed), data specific to the pet A query for "SELECT * FROM pets WHERE owner_id = ?" shouldn't take much time at all, so long as you set ownerID to indexed when you make the table. CREATE TABLE pets ( id INT NOT NULL AUTO_INCREMENT, owner_id INT NOT NULL, name VARCHAR(63), dob DATETIME PRIMARY KEY (id), INDEX (owner_id), ) Code (markup): Some people prefer to set the key and index alongside the fields, I prefer putting them after (especially since INDEX can accept more than one parameter) -- either way works. Hope that clears it up a bit.
But how will that work for all the users that sign up? For example user1 could enter 20 pets one day and then user2 could enter 15 pets the day after. This would mean that user1 owns pets 1 - 20 and user2 owns pets 20 - 35. So how would this work?
Because you have two tables. One for users, one for pets. The users table has all the information on the user - email, password, username, userid. Userid should be unique and auto increment, The pets table has all the information on the pets - pet name, pet type, petid and the userid of the owner. The pet id must be unique and autoincrement. The user id can be used as many times as you like, giving one owner many pets. Users can input pets in whatever order they'd like. Then all you need to do is select pets belonging to a certain user, using a query deathshadow showed you above. SELECT * FROM pets WHERE userid=2. Will return all pets belonging to that user, and leave everything else out.
This is a common newbie mistake. You need to read up on Relational Databases. Instead of having a Table named "Sam" holding sams account info, profile info, and messages you should have Tables for AccountInfo, ProfileInfo, and Messages then use a id (username perhaps) linking the information in the tables to the user Sam.