I'm trying to add a sign up date into mysql based on the post timestamp

Question

This works for me but it adds the datetime in mysql as 1970 01 01 00:00 00

include("mysql_connect.php");
$phpdate = strtotime( $mysqldate );
$mysqldate = date( 'Y-m-d H:i:s', $phpdate );

//check connection
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$sql = "INSERT INTO people (username, email, sign_up_date) VALUES ('$_POST[username]','$_POST[usermail]', '" . $mysqldate . "'  )";

if (!mysqli_query($mysqli,$sql)) {
    die('Error: ' . mysqli_error($mysqli));
}
echo "1 record added";

mysqli_close($mysqli);

Here's my html form:

<section class="loginform tmr">
<form name="login" action="regi.php" method="post" accept-charset="utf-8">
    <label for="username">Username: </label><br />
        <input type="username" name="username" placeholder="Handle" required><br />
        <input type="hidden" name="sign_up_date" value="<?php echo $_POST['sign_up_date'] ?>">
    <label for="usermail">Email: </label><br />
        <input type="email" name="usermail" placeholder="yourname@email.com" required><br />
    <label for="password">Password: </label><br />
        <input type="password" name="password" placeholder="password" required><br />
        <input type="submit" value="Login">
</form>
</section>

I'm using a hidden input and requesting the post value.

I tried using timesteamp() and date()

sign_up_date type in mysql is datetime..

should i use something like this?

$insertdate = date('Y-m-d', strtotime($_POST['Date']));

Additionally, I have a field called account_permissions enum('a', 'b', 'c') and I'm wondering how I insert that into my form (should i use another hidden input)... I need it to default to 'a' all the time.

Any help would be greatly appreciated. Thank you.

This can help you: http://stackoverflow.com/questions/136782/format-mysql-datetime-with-php — Jérôme Teisseire, Dec 27 '13 at 17:55
Wait, you're generating the date in PHP, outputting that to the page, sending it back via a form to then populate a column in a new row instead of just using `NOW()` in SQL? — Popnoodles, Dec 27 '13 at 17:57

score 1 · Accepted Answer · answered Dec 27 '13 at 18:02

1

If your sign_up_date column data type is DATE, you can just use the MySQL CURDATE() function:

INSERT INTO people (username, email, sign_up_date)
  VALUES ('name', 'email', CURDATE())

Remember that a DATE column will store just the date, without the time of day. If you want to include the time of day accurate to seconds, make it a DATETIME column and populate it with NOW() instead of CURDATE(). If you want to include the time of day accurate to fractions of a second, make it a TIMESTAMP column and populate it with CURRENT_TIMESTAMP.

IMPORTANT: note that the point behind mysqli is to avoid using expressions like ... VALUES ('$_POST[username]', ..., which leave you open to SQL Injection attacks. See here for more information, and please follow their guidelines - SQL Injection is a very real threat.

answered Dec 27 '13 at 18:02

Ed Gibbs

25,924
4
46
69

NOW worked brilliantly. Thank you for the explanation and help. I'll mark this as the correct answer when the time allows me to. – cooking good Dec 27 '13 at 18:06
Can you help me with the second part of the question... for instance if i want the default account permissions to be a 'c' or 'b'. – cooking good Dec 27 '13 at 18:10
Sorry, I didn't completely understand that part of the question, but here's a general answer: If you define the column as "not null" - `account_permissions enum('a', 'b', 'c') NOT NULL` - and insert a row without specifying the `account_premissions` value, MySQL will automatically set it to the first `enum` value (`a`), so you don't really need to do anything from the PHP side. – Ed Gibbs Dec 27 '13 at 18:19
I'm not quite sure i understand the information on that link you provided. Those are prepared statements? – cooking good Dec 27 '13 at 18:19
It's defaulting to `a` correct. How to I make it default to a different permission like b or c? – cooking good Dec 27 '13 at 18:20
Regarding the link - yes, those are prepared statements. The examples are decent, but mysqli has a bit of a learning curve. Worth it, though. You can Google for other examples. As for defaulting to a different permission, do you mean all the time or do you mean under certain circumstances? – Ed Gibbs Dec 27 '13 at 18:22
defaulting all the time. – cooking good Dec 27 '13 at 18:25
1

To default all the time, one easy way would be to change the `enum` definition. For example, if you wanted to default to `b` all the time then you could define the column as `account_permissions enum('b', 'a', 'c') NOT NULL`. Or to make it more obvious (always a good thing), define the column as `account_permissions enum('a', 'b', 'c') NOT NULL DEFAULT 'b'`. – Ed Gibbs Dec 27 '13 at 18:32

score 1 · Answer 2 · answered Dec 27 '13 at 18:03

What you're doing doesn't make sense. You're generating the date in PHP, outputting that to the page, sending it back via a form to then reformat and use in SQL. What's wrong with NOW()?

$sql = "INSERT INTO people (username, email, sign_up_date) 
    VALUES ('$_POST[username]','$_POST[usermail]', NOW() )";

You could even just set the default value of that column to CURRENT_TIMESTAMP() and not have to send it

$sql = "INSERT INTO people (username, email) 
    VALUES ('$_POST[username]','$_POST[usermail]' )";

Please note your SQL is open to injection as you are not sanitizing the $_POSTs

Thanks for helping out too, i know you mentioned using NOW() in your original comment. I didn't know about that function. Should i still be using the hidden input to $_POST['sign_up_date'] — cooking good, Dec 27 '13 at 18:09

score 0 · Answer 3 · answered Dec 27 '13 at 17:56

0

Check here the datetime format and create it from the timestamp you get with time() http://dev.mysql.com/doc/refman/5.0/en/datetime.html

Alternatively you can store date in timestamp

About enum you can add a selectbox with these three values.

answered Dec 27 '13 at 17:56

makriria

383
1
9

score 0 · Answer 4 · answered Dec 27 '13 at 19:27

ALTER TABLE `people` MODIFY COLUMN `sign_up_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

Running this query once to alter your table and removing everything inside your php code related to sign_up_date field should solve all your problems. Basically this means when you insert a new record mysql will automatically assign current date value to sign_up_date field as default value.

I'm trying to add a sign up date into mysql based on the post timestamp

4 Answers4