[octalgenesis]

I tried to read as much as i can on the matter to solve why this is not working but i can't find anything on it, would apprichaite if someone could explain to me why it gives me a error that the Date and Time lines are not correct.

CREATE TABLE users
(
UserId int NOT NULL,
Password varchar(255) NOT NULL,
Username varchar(255) NOT NULL,
Date datetime NOT NULL DEFAULT CURRENT_DATE,
Time datetime NOT NULL DEFAULT CURRENT_TIME,
PRIMARY KEY (userid)
)

i also tried

CREATE TABLE users
(
UserId int NOT NULL,
Password varchar(255) NOT NULL,
Username varchar(255) NOT NULL,
Date datetime NOT NULL DEFAULT CURDATE(),
Time datetime NOT NULL DEFAULT CURTIME(),
PRIMARY KEY (userid)
)

Still didn't work.

The error was

Quote

#1064 - 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 'CURRENT_DATE, Time datetime NOT NULL DEFAULT CURRENT_TIME, PRIMARY KEY (userid' at line 6

[Atli]

The problem there is that the DATETIME type can not be initialized to the current time by default. Only the TIMESTAMP type can do that, and only one TIMESTAMP field per table. - It's odd, I know, but that's just how it's always been in MySQL.

One workaround is to use a trigger to set the date when the field is inserted, like I show here, but that is a rather dirty trick. It's best to avoid using something like that, unless you absolutely can not stand using the NOW() function in your insert queries.

[octalgenesis]

Atli, on 20 July 2012 - 10:00 PM, said:

The problem there is that the DATETIME type can not be initialized to the current time by default. Only the TIMESTAMP type can do that, and only one TIMESTAMP field per table. - It's odd, I know, but that's just how it's always been in MySQL.

One workaround is to use a trigger to set the date when the field is inserted, like I show here, but that is a rather dirty trick. It's best to avoid using something like that, unless you absolutely can not stand using the NOW() function in your insert queries.

Thank you for the reply, i'll see what i can do

[octalgenesis]

This is really hard to understand, i trid so many things for it to add the date to the database but i have no luck doing it, can someone please tell me what the line look like to add date and time into a column. I need a exaple to work with

[Atli]

Like I say: the DATETIME type can not be set to the current time by default. Not without resorting to trickery, like I do in that trigger snippet.

If you only have one DATETIME column to work with, you can replace it with a TIMESTAMP and set the default value to the current timestamp:

when_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

But this will only work once per table!


I should also point out that, in your original statement, creating two DATETIME columns, one for the "Date" and one for the "Time", makes no sense. Like the name suggests (or rather: screams rather obviously), the DATETIME type stores both the date and the time. You don't have to store them separately; you can do it in one field.

date_and_time DATETIME NOT NULL

And, not being able to default to the current time, you'll have to set it's value manually in each INSERT statement.

INSERT INTO whatever
    (something, date_and_time)
VALUES 
    ('some value', NOW()),
    ('some other value', NOW());

[octalgenesis]

Atli, on 21 July 2012 - 05:10 PM, said:

Like I say: the DATETIME type can not be set to the current time by default. Not without resorting to trickery, like I do in that trigger snippet.

If you only have one DATETIME column to work with, you can replace it with a TIMESTAMP and set the default value to the current timestamp:

when_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

But this will only work once per table!


I should also point out that, in your original statement, creating two DATETIME columns, one for the "Date" and one for the "Time", makes no sense. Like the name suggests (or rather: screams rather obviously), the DATETIME type stores both the date and the time. You don't have to store them separately; you can do it in one field.

date_and_time DATETIME NOT NULL

And, not being able to default to the current time, you'll have to set it's value manually in each INSERT statement.

INSERT INTO whatever
    (something, date_and_time)
VALUES 
    ('some value', NOW()),
    ('some other value', NOW());

Thank you, that made more sense.

CREATE TABLE users
(
UserId int NOT NULL AUTO_INCREMENT,
Password varchar(255) NOT NULL,
Username varchar(255) NOT NULL,
Created TIMESTAMP NOT NULL,
UNIQUE key (userid, username),
PRIMARY KEY (userid)
)

but now i would like to make both userid unique and also username, but maybe i should make username not being able to duplicates in the php code with error checking?