Advice needed re database changes

qme1ster

Hi there,

I wonder if someone could advise me - I have a database which is currently being populated by data collected in a webform. There are currently 3 columns in the db, one which is a customerID (autoincrementing), and the other two being a name and an email address. I now need to expand this database to include the date and also a telephone number.

How do I go about adding the 2 columns which I need, and will it effect the data (already collected) in any way ?
The tel number is already being collected in the webform but currently only emailed on - so easy enough to pass this telephone data over to the database. In terms of the DATE however I would like to use the server datestamp. How would I go about setting this up ?

Grateful for any help.

halojoy

As I know
this is when we use ALTER TABLE statement
http://www.w3schools.com/SQl/sql_alter.asp

Looks like it is simple:
"ALTER TABLE table_name
ADD column_name datatype"

And this will not effect the data you already have.

qme1ster

Thanks halojoy - much appreciated. I did not know about that statement. Seems very logical !

So to add the column to capture date, and to have it automatically recorded whenever a new record is added, do I create the column in this way:

ALTER TABLE table_name
ADD dateandtime NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

Many thanks in advance

halojoy

I think so.
You add the column just like when CREATE TABLE.
Only difference is that you add 1 column only.
I am not so advanced with SQL.
So, lets wait to see what the other say about this 🙂

Bjom

almost there, you forgot the datatype. In this case the timestamp type would be appropriate:

ALTER TABLE myTable
ADD dateandtime TIMESTAMP NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

qme1ster

Thank you very very much - that works !