MySQL table with date(now()) field??

Spudrifle

Hi, I'll cut to the chase, I'm trying to make a poor-mans version of a blog for a profile based website. I'm wondering if you can use php to create a column(s) in a MySQL table with the title being the current date/time.

Something like -

$query = "ALTER TABLE db_name.tbl_name ADD (now()) TEXT NOT NULL";

Basically then I'll be using a simple post form to add messages to the now() columns, (that will always be unique) where username=$_POST[blah]

Thanks in advance, and sorry if this question has been asked before, but I've been searching for a few days now and haven't come to any conclusions...

🙂 Spud

Pikachu2000

You probably can't name it 'NOW()' as that is a function name. You can name it 'time_stamp', give it the datetime type, and use NOW() to insert the current date and time, though.

Spudrifle

Just in case I wasn't clear, and I don't think I have been :S
I need a query that will create a new field/column in the MySQL table everytime a message is submitted from a form, and that will store said message.

Spudrifle

Sorry, I posted that last message as you posted yours, unaware of a reply..

Thanks for your response,
does that mean I'd have a column called time_stamp and the data in the field would be NOW()?

Could I get a POST to submit NOW() along with text??

Spud,

Pikachu2000

Spudrifle;10947294 wrote:
Sorry, I posted that last message as you posted yours, unaware of a reply..

Thanks for your response,
does that mean I'd have a column called time_stamp and the data in the field would be NOW()?

Could I get a POST to submit NOW() along with text??

Spud,

The column would have whatever name you want it to have, with a data type of datetime, then the form handler and insert query would look something like this. The NOW() in the insert query takes care of inserting the current time and date into the DB.

if( isset($_POST['submit']) ) {
$email = mysqli_real_escape_string($dbc, $_POST['email']);
$name = mysqli_real_escape_string($dbc, $_POST['name']);

$query = "INSERT INTO table (name, email, timestamp) VALUES ( '" . $name . "', '" . $email . "', $NOW() )";
$mysqli_query($dbc, $query) or die("Query failed");
}

bradgrafelman

If you use the TIMESTAMP column type, you could even use the 'DEFAULT CURRENT_TIMESTAMP' option so that you wouldn't even need to include the column in your INSERT queries at all.

Spudrifle

I will use the TIMESTAMP column to coincide with the messages being posted, but at the moment, my system only allows one message to be posted at a time..
After thinking about it, what I really need is to have my message field created, with a message number, say message1, message2, that auto-increments. and the date can be posted back to the profile next to the message. However, I am not sure how to have these messages stored, whether I need to create a new column each time, and how I'd post message1, message2 and so on, without knowing how many there are..
nor am I sure how I could make this work for each usernames profile. Its a bit intricate for a newbie.

I think I'm taking the completely wrong approach to this amateur blog thing. And/or have dove straight into the deep end!!

Any suggestions? Criticisms? Sorry to be such a n00b.

Spud