Help understanding date format

[matius]

I have the following abbreviated code processes a form on an HTML page (the birth date section specifically).

 

if(true === isset($data['dob'])){
    if(1 !== preg_match('~(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)[0-9]{2}~', $data['dob'])){
      $errors[] = 'Date Of Birth field has an invalid format';
    }
  }else{
    $errors[] = 'Date Of Birth field empty';
  }

 

mysql_real_escape_string(date('Y-m-d', strtotime($user['dob'])), $con),

 

Obviously in the database it shows something like 1980-12-25.  I want the format to be 12-25-1980... but when I flip the PHP Date variables around the entries appear as 0000-00-00.  So not only does it not change the order, it prints as zeros.

 

What am I missing? 

 

This data is being exported to CSV.  Is it easier to change this format in the export process or should I change it where the form is being processed (and how)?

 

Thanks for any help.

[PFMaBiSmAd]

A DATE data type in your table is formated yyyy-dd-mm (for several reasons.) The data values you put into the table must be formatted that way (for several reasons.) If you want to retrieve the data with a different format, you use the msyql DATE_FORMAT() function in your query or you write some slow php code to do the same after you retrieve the data.

[matius]

you use the msyql DATE_FORMAT() function in your query or you write some slow php code to do the same after you retrieve the data.

Thanks for your reply.  The code I'm using is below...  not looking for a hand out at all I just want to see if I got the spot to modify right (ok maybe looking for a hint).  See asterisks.

 

<?php
$host = 'localhost';
$user = 'mysqlUser';
$pass = 'myUserPass';
$db = 'myDatabase';
$table = 'products_info';
$file = 'export';

$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");

$result = mysql_query("SHOW COLUMNS FROM ".$table."");
$i = 0;
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$csv_output .= $row['Field']."; ";
$i++;
}
}
$csv_output .= "\n";

***** DATE FORMAT HERE? ****************
$values = mysql_query("SELECT * FROM ".$table."");
while ($rowr = mysql_fetch_row($values)) {
for ($j=0;$j<$i;$j++) {
$csv_output .= $rowr[$j]."; ";
}
$csv_output .= "\n";
}
***************************************

$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");
print $csv_output;
exit;
?>

---

[fenway]

You'll need to inspect the type of each column in the schema.

[matius]

Ah... ok thanks for that !

[Mchl]

Also, there's no point in escaping output of date() (as long as format string is hardcoded).