convert MySQL date to dd mm yyyy format

[emonsta]

Hi Nick, me again.

I've included a date field in the Profile screen for the administrator to record the date a user takes out a membership. i've set the database field to date format, and i've got the fielf functioning correctl except that it displays the date as yyyy-mm-dd. Am wanting to convert that to dd-mm-yyyy for display and input. found the following code and wondered whether i can incorporate this somehow. have no idea where to begin, so i wondering if you would mind giving me a pointer? sorry to badger you again...

thanks

/**
* function dateconvert
*
* dateconvert is a handy function to take the aches and pains out of mysqls stupidity
* by converting data from a variable (posted from a form or just stored)
* into a format mysql will be able to store and converting the
* database date back into the british standard of date month year.
* The Script accepts day.month.year or day/month/year or day-month-year.
* example:
*
* <code>
* <?php // using type 1
* $date = "19.12.2005";
* $date = dateconvert($date, 1);
* echo $date; // Would echo 2005-12-19 which is the format stored by mysql
* ?>
* </code>
* <code>
* <?php // using type 2
* $date = $row['date']; //your mysql date
* $realdate = dateconvert($date,2);
* echo $realdate; // would display 19/12/2005
* ?>
* </code>
*
* @author Chris McKee <pcdevils@gmail.com>
*
* @param string $date - Date to be converted
* @param string $func - which function is to be used (1 for input to mysql, 2 for output from mysql)
*/
function dateconvert($date,$func) {
if ($func == 1){ //insert conversion
list($day, $month, $year) = split('[/.-]', $date);
$date = "$year-$month-$day";
return $date;
}
if ($func == 2){ //output conversion
list($year, $month, $day) = split('[-.]', $date);
$date = "$day/$month/$year";
return $date;
}

}

(from: http://www.phpbuilder.com/annotate/message.php3?id=1031006)

[Nick]

2 ways to do this:

You can store the data in the database as the unix timestamp using php's mktime() and use phpScheduleIt's built in date formatting functions

You will need to create a unix datestamp from your data using php's mktime() then use phpScheduleIt's built in date formatting functions

So like:

Code:

list($day, $month, $year) = split('[/.-]', $date);

CmnFns::formatDate( mktime(0,0,0,$day,$month,$year) );

[emonsta]

thanks Nick, so i should put that code where? in the auth.template.php file?

[Nick]

Yeah, wherever you had it before or wherever you need to display it in that format.

[emonsta]

Hiya  I'm just returning to this after putting it in the too hard basket for a while.  i have to confess i have no idea where to put that code.
this is my current code which correctly enters and returns dates from the database.
   
    echo translate('Membership start date').'</p></td><td>';
    echo '<input type="text" name="member_date" class="textbox" value="';
       echo isset($data['member_date']) ? $data['member_date'] : '';
            echo '" maxlength="10" /></td></tr>';
     }
     ?>

i need the date to display in dd--mm--yyyy format and to handle the same as input....

thanks in advance

[Nick]

To display it, just use php's built in stuff:

date('m-d-Y', $data['member_date']);

Putting it in the database, do this:

$d = explode('-', $_POST['member_date']);
$date = mktime(0,0,0,$d[0],$d[1],$d[2]);

[emonsta]

ok, so i put the first line in the auth.template.php file in the following place

 echo translate('Membership start date').'</p></td><td>';
    echo '<input type="text" name="member_date" class="textbox" value="';
       echo isset($data['member_date']) ? date('m-d-Y', $data['member_date']) : '';

and i put the other two lines in the auth.class.php

here

// Register the new member
      $id = $this->db->insertMember($data);
    $d = explode('-', $_POST['member_date']);
   $date = mktime(0,0,0,$d[0],$d[1],$d[2]);


      $this->db->auto_assign($id);      // Give permission on auto-assigned resources


and here

   function do_edit_user($data, $adminUpdate) {
      global $conf;
$d = explode('-', $_POST['member_date']);
   $date = mktime(0,0,0,$d[0],$d[1],$d[2]);


 it displayed the date in the mm--dd--yyyy format, but it defaulted them all to 1969, and it didn't pass a new date the database or retrieve it again.
I really just want to know where i have to put this code. does it go in a function, does it go at the top of the page, i really don't know, i've being trying to figure this problem out for weeks and i really can't get my head around the date thing.

thanks

e

[Nick]

You haven't written the code to put the data in the DB?

You'll need to modify lib/db/AuthDB.class.php functions insertMember() and update_user().  Just put your $data['member_date'] below array_push($to_insert, $data['timezone']); in both functions and add a question mark to line 93 and change line 125 to . ' timezone=?,member_date=?';

[Edward Collin]

I strongly think REDSA has a point there. Though, i use JDBC ,servlet to be precise, and i notice that one can only retrieve date in the format DD:MM:YYYY with the appropriate JDBC code without the ability to insert it in exactly that format (DD:MM:YYYY) because MySql does not provide special codes for doing that.