user named new DB table

rupertbj

I have had a good look here and at MySql.org for an answer:

I have the following sql statement to create a table:

$sql = "'CREATE TABLE fotoTable ('
        . ' `id` INT NOT NULL AUTO_INCREMENT, '
        . ' `userid` TEXT NOT NULL, '
        . ' `filename` TEXT NOT NULL, '
        . ' `title` TEXT NOT NULL, '
        . ' `comment` BLOB NOT NULL,'
        . ' PRIMARY KEY (`id`)'
        . ' )';";

This names the table fotoTable which is fine, but what I really want to do is for each new table that is created to be named as the username of the person logged into my site.

The question is how do I pass the userid variable to the name of the table?

Your help would be much appreciated - I'm sure it's simple, but I've tried so many things?!

Rupert

TheDefender

$sql = "'CREATE TABLE ".$variable_with_desired_name." (' 
        . ' `id` INT NOT NULL AUTO_INCREMENT, ' 
        . ' `userid` TEXT NOT NULL, ' 
        . ' `filename` TEXT NOT NULL, ' 
        . ' `title` TEXT NOT NULL, ' 
        . ' `comment` BLOB NOT NULL,' 
        . ' PRIMARY KEY (`id`)' 
        . ' )';";

It's up to you how to define that variable's name.

jdorsch

What is the purpose behind this? If you end up with a thousand users you are going to have a thousand tables, unless you are dropping these, but I am guessing there is probably a better way to accomplish whatever it is you are trying to do.

rupertbj

The purpose is to create a database for which each user has a table in which will be stored all their photos and comments that go with each photo. These can then be displayed for other users to view.

There will be no duplication of data within the table. If there is a better way to achieve what I'm trying to do then that would be great, because as you say 1,000 users = 1,000 tables.

Rupert

thorpe

Look at some tutorials on database normalization and design. Making a new table for each user is plain rediculous.

rupertbj

OK - so i read some stuff on normalization:

I can therefore drop the userid field in the table. I guess I can use the tableName to identify the table required.

However, all the other entries in the tables will be distinct from any other. All the filenames, titles, comments will be provided by user input and can be whatever they want to be shown on their page.

So for each user there will be a table with distinct information within it. Furthermore, this information will also be distinct from any other user table!

Am I missing something, or is there another way to achieve this more efficiently.

Rupert

TheDefender

LOL, I agree the idea was ridiculous, but that wasn't the question...
Question answered....

Now, onto the meat of what you're doing.

If you have a properly normalised and indexed database structure, then you'd have the following: (basically)

a table for the users (with a unique ID field for the key)
a table for the photos and the info submitted by the user with a foreign key that associates back to the user table.
a table that ties all of the keys together

This should get you going in the right direction. Designing a properly normalized database takes some planning and thinking, and some blood sweat and tears, but in the end you'll be better off, and be running more efficiently. It's definitely not efficient to have 1000 tables just for your users. Also, you don't want to put "database building" into the hand of your users like that.

rupertbj

Thanks for that.....i think that re-reading the stuff on normalization is going to make more sense now! That is a big help!
Rupert

TheDefender

No problem... Best thing to do is to build a table structure set as you think it'd need to be, and then post the SQL dump here in these forums, so others can look at it, and help you tune it... You'll get a lot of great help out here, as there are a ton of "normalisation gurus" around these boards. Good luck, and have fun learning!