table creating

[shark007]

The way I set up a system requires that every time I get a new user, I make a new table for that user. Well, the table is not being created.

My table is as such:

$create_name_table = "CREATE TABLE $name (

ID int NOT NULL AUTO_INCREMENT, //Session number

PRIMARY KEY(ID),

Date_Time datetime, //Date and Time taken

                .....

                )";

mysql_query($create_name_table, $login);

 

That all is in a function that I call when the user registers.

Am I allowed to have comments (e.g. //Session number) in this code?

Also, in the table creation, I delineate different fields by putting a line between them, for example:

...

//Boiler

Boiler-1 tinyint(1),

Boiler-2 tinyint(1),

Boiler-3 tinyint(1),

 

//General

Gen-1 tinyint(1)

...

 

Is this something that I can do?

 

I ask these questions because I'm trying to figure out why it wouldnt work.

 

Thanks for your time!       

[Yesideez]

Why create a table for each user? Why not have one table containing all the information about each user?

[shark007]

The application is something where everytime the user uses it, it is necessary to log all information involved. I have limited experience with mysql/php, so the route I've taken is to create a table for each user where all of the info (there will be something like 150 fields) will be stored for each session. This is distinct from the actual table of users.

[per1os]

I would highly suggest reading up on MySQL and even looking for a tutorial/book.

 

The way you are trying to use MySQL is not even plausible and very inefficient and you will be kicking yourself in the butt later for doing it that way.

 

Reading is often times very good in programming. Read up on Database Management, you can store multiple users in 1 table and link them to a "log" table that can log actions etc. if need be.

[shark007]

Thanks, I appreciate your help a lot. I'll look into that. I was told actually to worry about optimization later. What do you mean by it not being even plausible?

[per1os]

Read up on Database Management. You will understand soon enough. Databases are not meant to store 1 row per table. They are meant to store thousands, if not millions of rows per table. The way you are going about it you will have thousands if not millions of tables with 1 row a piece. Its just not even being realistic.

 

Imagine this, if you were laying a roof and had all the tiles scattered and only 1 nail per roof-tile it would not be very effective or efficient and chances are a good breeze would throw off the tiles.

 

You obviously have not even scratched the surface of database management, I wouldn't be surprised if you came back stating that you never even read anything about a database.

 

You will thank me for pushing you away from the current route you are going later on.

[shark007]

I appreciate your expertise. I have read quite a bit about databases, especially in setting up the one I'm using. It's clear though that I have some more to do, especially about logs.

On the topic of the method I am using though, it is the same way that I made the table for the login system. I dont see how it could generate tables of just 1 row. The login system is one table with 8 rows. Why wouldn't the code snippet I posted below generate a row for boiler-1, boiler-2, etc..?

 

Thanks again.

[per1os]

I do not really understand the question you are asking above. But here is a simple table design that may help you see why it would be better not to do it with tons of tables:

 

Create table users (
     userid int(11) NOT NULL auto_increment,
     username varchar(50) NOT NULL,
     password varchar(55) NOT NULL,
     email varchar(250) NOT NULL,
     date_created int(11) NOT NULL,
     primary key(userid),
     index(username,email)
);

create table logs (
     logid int(11) NOT NULL auto_increment,
     userid int(11) NOT NULL,
     log_timestamp int(11) NOT NULL,
     log_item int(5) NOT NULL,
     primary key(logid),
     index(userid)
);

create table log_items (
     logitemid int(5) NOT NULL auto_increment,
     logitemname varchar(50) NOT NULL,
     logitemdetail text NOT NULL,
     primary key(logitemid)
);

 

That is an example of a database in 3NF (3rd normal form)

 

Here is how a new user insert would look:

 

INSERT INTO users(`username`, `password`, `email`, `date_created`) VALUES ('$name', '$password', '$email', '" . time() . "');

 

Now let's say we want to insert a log for that user, something like this would work...

 

SELECT userid FROM users where username = '$name'

SELECT logitemid FROM log_items WHERE logitemname = 'Login';

INSERT INTO logs (`userid`, `log_timestamp`, `log_itemid`, `log_timestamp`) VALUES ('$userid', '$logitemid', " . time() . ");

 

That way everything is nice and neat, you can easily query this back and grab what log action the user did, what time they did it and it can have as many users/logs as needed and can be easily filtered by userid etc.

 

Anyhow hope that helps make it clearer on why you really do not want a ton of tables.