setting date in php before puting in a database

Question

I want to put the uses date of birth into the database..so I wrote this function:

      function setAge($day, $month, $year)
  {
      $date= strtotime("$day $month $year");

       mysql("UPDATE controlpanel
             SET date_of_birth=$date
             WHERE=".this->$userID) or die(mysql_error());
  }

I am not sure if this is correct, or the best way to update the date. The field variable is DATE, .. I dont insert dates, I just update it..will it cause an error? is this the best way to put a date from php to mysql?

CM Kanode · Accepted Answer · 2011-11-08 11:01:12Z

1

You should format the date to 'yyyy-mm-dd' for your SQL statement to insert it into MySQL.

answered Nov 8, 2011 at 11:01

CM Kanode

1,4261 gold badge11 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Polynomial · Accepted Answer · 2011-11-08 11:02:54Z

1

Quite a lot of stuff wrong here.

Your query is malformed. You're not specifying which field you're comparing in the WHERE clause. You need to do something like WHERE userID = 123.
You're not sanitising your values. ALWAYS ALWAYS ALWAYS use the appropriate filtering function on query data. If you don't, you're open to SQL injection. In this case, you probably want to use mysql_real_escape_string().
Your use of strtotime() is wrong. You can easily manually format it using "{$year}-{$month}-{$day}".

Just for emphasis:

SERIOUSLY, SANITISE YOUR DATA PROPERLY. YOU WILL GET HACKED.

answered Nov 8, 2011 at 11:02

Polynomial

28.4k13 gold badges86 silver badges110 bronze badges

2 Comments

WithFlyingColors Over a year ago

the variables will be safe before they go into the function dont worry..nicely noticed..it was a mistake to put year, month, date

Polynomial Over a year ago

It is ALWAYS better to sanitise at the site of the query, rather than rely on other parts of the code doing the sanitising for you. It means that you only have to remember to sanitise once, rather than every time you want to use that function. It also makes your code easier to read, since it's free of annoying temp variables that contain sanitised versions of your data.

Niet the Dark Absol · Accepted Answer · 2011-11-08 11:00:55Z

0

mysql_query( "UPDATE `controlpanel`
                 SET `date_of_birth`='".$year."-".$month."-".$day."'
                 WHERE `userid`=".this->$userID);

There are quite a few code errors in your code. Did you even TRY running it?
Anyway, this code removes the need for the strtotime call.

answered Nov 8, 2011 at 11:00

Niet the Dark Absol

326k86 gold badges480 silver badges604 bronze badges

Comments

pinaldesai · Accepted Answer · 2011-11-08 11:10:39Z

0

You don't need custom function has PHP has predefine date functions.

$date= date("Y-m-d", strtotime(mktime(0, 0, 0, $month, $day, $year)));

You can use strtotime() if you want.

refer documentation for more details http://php.net/manual/en/function.mktime.php

answered Nov 8, 2011 at 11:10

pinaldesai

1,8352 gold badges13 silver badges22 bronze badges

1 Comment

Álvaro González Over a year ago

You already have day, month and year from the beginning. You don't need to convert to Unix timestamp. It isn't wrong bug it's kind of redundant.

Naseer Panhwer · Accepted Answer · 2011-11-08 11:12:41Z

0

kolink is correct..in database date datatype format is year-month-day, you can also store date and time in timestamp, it will be more easy to handle.. through time stamp you can handle date and time more flexibly..

answered Nov 8, 2011 at 11:12

Naseer Panhwer

1691 gold badge2 silver badges10 bronze badges

1 Comment

Álvaro González Over a year ago

Bad advice. Data types exist for a reason. If you handle dates as integers you are just making this harder for no good reason.

Álvaro González · Accepted Answer · 2011-11-08 12:00:48Z

0

If you look at the PHP manual, you'll see that strtotime() returns an integer, more specifically a Unix timestamp. If you look at the MySQL manual, you'll see that DATE columns are handled as strings.

Apart from reading the documentation, you can see that something's wrong with some simple debugging. If you print your query with var_dump() and run it in your favourite MySQL client, you'll see that your row gets updated with an invalid value (or you get an error message, depending on the server settings).

Now, there are many ways to create a properly formatted string. You appear to have the individual components saved into PHP variables, so you can use sprintf():

sprintf('%04d-%02d-%02d', $year, $month, $day)

Update: I'm adding some other versions to please @Col. Shrapnel:

sprintf('%s-%s-%s', $year, $month, $day)
"$year-$month-$day"
$year . '-' . $month . '-' . $day

edited Nov 8, 2011 at 12:00

answered Nov 8, 2011 at 11:12

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

4 Comments

Álvaro González Over a year ago

@Col.Shrapnel - sprintf() is a handy way to generate a string with a fixed format. It takes care of casting to integer and add leading zeroes as required and it's sometimes cleaner than raw string concatenation. I thought you would know that with 27K rep ;-P

Your Common Sense Over a year ago

what's the point in adding zeros to the year? and in casting too? if you want to validate your date, use checkdate() first - it will do the validation part explicitly, not silently with possible erroneous result as sprintf().

Álvaro González Over a year ago

@Col.Shrapnel - I've updated my answer. I know I shouldn't care but it's just that I don't want to provide the worse answer in six when I'm the only one who cared to explain why the OP's code was wrong and link to the appropriate manual chapters.

Your Common Sense Over a year ago

thanks. the "$year-$month-$day" variant is indeed most natural of them all.

Collectives™ on Stack Overflow

setting date in php before puting in a database

6 Answers 6

Comments

SERIOUSLY, SANITISE YOUR DATA PROPERLY. YOU WILL GET HACKED.

2 Comments

Comments

1 Comment

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

SERIOUSLY, SANITISE YOUR DATA PROPERLY. YOU WILL GET HACKED.

2 Comments

Comments

1 Comment

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related