1

How can I insert the value from an (in a form) into a MySQL date field. I'm trying this:

<form  action="enviar.php" autocomplete="off" method="post"> 
    <label for="nacimiento" class="youpasswd" data-icon="p">
        Fecha de Nacimiento
    </label>
    <br>
    <input id="nacimiento" name="nacimiento"
           required type="datetime" placeholder="DD/MM/YYYY" />
</form>

enviar.php

$nacimiento= $_POST['nacimiento'];
mysql_query("INSERT INTO voluntarios(nacimiento) VALUES ('$nacimiento')");`

This is working for all the other fields but not with the date field; it is empty. What am I doing wrong?

Thank you

Improve this question
5
  • 6
    Use parameterized SQL queries or you Will get hacked. Your site is currently wide open to SQL injection. See bobby-tables.com Commented Jul 23, 2012 at 22:36
  • 1
    The mysql_ functions are deprecated (ie, they will be removed in the future). Switch to mysqli_ or better still, to PDO. Rearding your problem, are you sure your database will accept a date string formatted as dd/mm/yyyy ? More than likely the correct format will have to be YYYY-MM-DD Commented Jul 23, 2012 at 22:39
  • As @Lusitanian stated, you should use parameterized queries (e.g. PDO) unless you have a very good reason not to. Otherwise, you can do like this: $nacimiento = mysql_real_escape_string($_POST['nacimiento']); Commented Jul 23, 2012 at 22:39
  • Can you please check the format of the string. Little know fact about datetime fields is that the browser transforms them before sending. Commented Jul 23, 2012 at 22:48
  • 1. You are not sanitizing your input. 2. You are using mysql_ instead of mysqli_. Use MySQLi and mysqli_query($conn, "INSERT INTO voluntarios(nacimiento) VALUES ('" . mysqli_real_escape_string($nacimiento) . "')"); Commented Jul 23, 2012 at 22:49

5 Answers 5

Reset to default
4

You seem to have problems even getting the value from your input field to the server. This needs to get fixed first. Debug (var_dump) your $_POST data and check what is going on. Provide more code if you need more help. From what you write, I don't see, why the value shouldn't get sent.

After you got your date string, convert the value to a string literal format MySQL recognizes:

$mysqldate = date( 'Y-m-d H:i:s', $phpdate );

Then insert it using mysqli_query:

mysqli_query(sprintf("INSERT INTO voluntarios(nacimiento) VALUES ('%s')"),
                      mysql_real_escape_string($mysqldate));

Using PDO do this:

$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
$q = $dbh->prepare("INSERT INTO voluntarios(nacimiento) VALUE (:nacimiento)");
$q->bindParam(':nacimiento', $mysqldate, PDO::PARAM_STR);
$success = $q->execute();
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

@Wolfram please check the entire code of the form in JSFIDDLE. Thank you
Doing a var_dump I saw that I had an error in the variable, this was fixed. Now ´$nacimiento´ is taking the date and I can store it in the database. At this time I am reading & learning about PDO to do secure codes.
2

Try and echo $_POST['nacimiento']; and see if it does anything? It also seems that you're not posting your whole SQL query, maybe there is an error in the query?

Edit: You could also try and see what the error is using mysql_error

The following code is vulnerable to SQL injection

$nacimiento= $_POST['nacimiento'];
mysql_query("INSERT INTO voluntarios(nacimiento) VALUES ('$nacimiento')");`

Stop using mysql_* functions as they're deprecated. Use PHP database objects (PDO) instead because PDO allows parameter binding which protects you from sql injection.

You can read up on using PDO here

Improve this answer

7 Comments

Or use mysqli, which is simpler than PDO. either way the raw mysql functions are deprecated.
doing echo $_POST['nacimiento']; the result is empty
@Aatch - simpler is just a matter of opinion, but I think PDO is more widely adopted which is why I recommended it :)
the first part of the query is mysql_connect("localhost", "user", "password"); mysql_select_db ("DB");
@kush PDO stands for PHP Data Objects not protected database objects.
|
1

MySQL expects date literals in YYYY-MM-DD format as explained in the manual. Although it's pretty lenient in what it accept, the format you offer won't be acceptable.

You could get away with some string juggling probably, but do yourself a favor and do it right - that is, use PDO prepared statements. Not only will you be able to easily defer date parsing & pre-checking to php, your code will be a lot safer and future proof.

The code will look like this, using elements from your example

$stmt = $dbh->prepare("INSERT INTO voluntarios(nacimiento) VALUES (:nacimiento)");
$stmt->bindParam(':nacimiento', $nacimiento);
// WARNING - strtotime strtotime expects '-' separators if you feed it dd-mm-yyyy
// you should modify that in your form.
$nacimiento = strtotime($_POST['nacimiento']);
$stmt->execute();
Improve this answer

Comments

1

Your issue is likely that the date needs to be formatted into the SQL standard (YYYY-MM-DD).

If you are fairly confident with what will be coming to you in the POST, then you can use this:

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

If you want to ensure the format then you could use something like this:

preg_match('#^(\d{1,2})/(\d{1,2})/(\d{4})$#', $_POST['nacimiento'], $matches);
if(count($matches))
{
    $formatted_date = $matches[3].'-'.str_pad($matches[1],2,0,STR_PAD_LEFT).'-'.str_pad($matches[2],2,0,STR_PAD_LEFT);
    // Use formatted date in query
}
else // handle error
Improve this answer

Comments

0

Use STR_TO_DATE to convert the text into a valid date format:

STR_TO_DATE('12/15/2008', '%m/%d/%Y');
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.