Before trying to figure this out on my own im curious if i can have a table inside a table, so e.g. users table, different rows containing different users details, then on each row there could i have a table containing things like log in dates? Or is that just not possible?

No. But you can have *RELATED* tables. which do exactly what you want. And they are the entire reason that RELATIONAL database systems are named...what else?..."relational".

Example:

Code:

CREATE TABLE users (
    userid INT AUTO_INCREMENT PRIMARY KEY,
    firstName VARCHAR(30),
    lastName VARCHAR(30),
    email VARCHAR(100),
    password VARCHAR(30)
    );

CREATE TABLE userDetails (
    userid INT REFERENCES users(userid),
    detailType ENUM( hobbies, interests, games ),
    detailDescription VARCHAR(500)
    );

The combination of a PRIMARY KEY in the users table with the foreign key created via REFERENCES causes these tables to be RELATED.

*IF* you use INNODB for your storage engine (most people do), then you will *NOT* be able to create a record in the userDetails table with an id that does not exist in the users table. And, on the flip side, you won't be able to DELETE a record in the users table if any record in userDetails has a reference to its id.

The latter restriction can be modified by use of CASCADE DELETE, but that's a topic for another day.

By the by, if you used an object-oriented database system (ODBMS, in contrast to RDBMS) then you could do what you asked...except that "tables" no longer exist in the RDBMS sense of the word and become, instead, simply arrays.

im gonna try the first method first see how that works for me, i tried your code but it spat me the error.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'hobbies, interests, games), detailDescription VARCHAR(500) )' at line 3
CREATE TABLE userDetails(
userid INT REFERENCES users( userid ) ,
detailType ENUM( hobbies, interests, games ) ,
detailDescription VARCHAR( 500 )
)

Im a novice at actually writing the create table coding, i use phpmyadmin to make tables etc (i really need to learn how to type the coding), can you see the error anywhere?

I dunno if this helps but "MySQL version 5.1.56-community-log". That's what my host is running

SO SORRY!

My error.

ENUM values are *strings* and need apostrophes around them:

Code:

CREATE TABLE userDetails (
    userid INT REFERENCES users(userid),
    detailType ENUM( 'hobbies', 'interests', 'games' ),
    detailDescription VARCHAR(500)
    );

Brain fart.

Thanks, that worked . If i detailed roughly what im wanting to do with this database would be be able to give me a little more indepth help with it?

Can try. And others here will be able to help, as well. What you are doing is a very very common scenario with relational databases. Honest.

Well i want a standard user table, username, password, email etc, then i want the other table, everytime they log in to store there IP address and the time of the log in. Im gonna have a go now at editing the code you gave me earlier try and get that correct.

Okay i have this which worked

Code:

CREATE TABLE users (
    userid INT AUTO_INCREMENT PRIMARY KEY,
    firstName VARCHAR(30),
    lastName VARCHAR(30),
    email VARCHAR(100),
    password VARCHAR(30)
    );

CREATE TABLE userDetails (
    userid INT REFERENCES users(userid),
    IP VARCHAR(100),
    Time VARCHAR(100)
    );

Now, how do i use this? Is it 2 querys to insert the relevant data or do i do it all in one?

Two queries.

But remember, you will only insert into USERS table the *FIRST TIME* you add a new user.

After that, you will only insert into userDetails table.

This is called a "many-to-one" table: Many records in userDetails that relate to one record in users.

To get back all the related records, you will use a JOIN query.

Example:

Code:

SELECT users.*, userDetails.IP, userDetails.Time
FROM users INNER JOIN userDetails
ON users.userid = userDetails.userid
WHERE userDetails.Time > DATE_SUB( NOW(), INTERVAL 1 DAY )

That would get you all users, *with* their details, who have a TIME value within the last 24 hours.

***********

NOTE: You are almost surely making a *HUGE* mistake having your TIME field be VARCHAR(100). If that field really is expected to hold a date and time value, then it *MUST* be declared as DATETIME, instead.

Also, TIME is a builtin function in MySQL. You *can* use it as a field name, but it's better to avoid it.

You might do something like:

Code:

CREATE TABLE userDetails (
    userid INT REFERENCES users(userid),
    IP VARCHAR(100),
    LoginTime DATETIME
    );

Or use a name that represents whatever the real purpose of the field is.

Yeah i will change it to a DATETIME, that was a brainfart on my part :P. Thanks for your help, will come in userful . Whats the benefit of doing it this way rather than two completely separate tables?

??? They *ARE* completely separate tables. They just happen to have a *RELATIONSHIP* defined between them.

If you omit the REFERENCES users(userid) then they *WILL* be completely separate.


Doesn't mean you can't still treat them as related (and, indeed, if you use MYISAM storage engine instead of INNODB, then the REFERENCES has no effect! it is treated as a comment and is not enforced).

But if you do use INNODB and do use REFERENCES, then you get the protection of not being able to add a record to the dependent table (userDetails in your case) when the corresponding record in the independent table (users) doesn't already exist. *AND* (perhaps more importantly) you can't delete a record from the independent table if there is some record in the dependent table that *needs* it to exist.

I phrased the question badly >.<. I meant rather what does the REFERENCES part do to how the table works, but i think i use MYISAM so i guess it does nothing but a comment :P.

Unless you are truly using MILLIONS or records in a high performance system, you probably should switch over to using INNODB. It's a much safer engine for most purposes.

Put it this way: You would *probably* need to be getting over 10,000 page hits per hour before it would be important to use MyISAM.

Just happened to stumble on this thread and got to wondering, why couldn't you have the 'userIP' and 'lastlogindate' (for example) in the user table?

A user has one ip, which can be updated on log in, and the 'lastlogindate' can be updated as well. I'm missing the point for an extra table here?
If you wanted to keep track of users' logins and the IPs they've logged in from then I get it, but to store one ip and one date?

I'm not sure, but I suspect this is just an example and he's not telling us what the actual tables will contain.

Yes, if you only cared about tracking the last login, no need for the other table. But for many people, that's a big "IF".

Yeah it was just an example, nd it would be not to store there last login, it would be to store each login.