Ali,
Both examples work well. For purposes of maintaining the MySQL date
format in the table so that you can benefit from the comparison and
computational aspects of MySQL, I suggest using the SELECT DATE_FORMAT
for displaying the information to the user and the function described by
Fernando for converting input to the MySQL date format for (re)insertion
into a(nother) table.
For example:
Let's say you have an e-business site. Using a shopping cart
application you would want orders to be added to an order table
possessing a date column. The user makes selections which are added to
the cart and today's date is registered by default when they process the
order and INSERT it into the table.
In a receipt to the customer you might want to show them their date of
order and other information using:
$query="SELECT DATE_FORMAT(o.date, '%m/%d/%Y'),
it.cost
FROM order o, item it
WHERE cust_id = $custId";
To display this:
$date=$row["DATE_FORMAT(o.date, '%m/%d/%Y')"];
echo $date;
If you are an administrator of this site requiring multiple orders to be
displayed in multiple rows (using a row counter function), you use the
same approach. If you also use an <a href to pass the order on to
another script which enters the date into an "orders processed" table,
the other approach is useful since you are passing the date in the
XX/XX/XXXX format and you need to input it as XXXX-XX-XX
Example:
echo "<a href=\"enter.into.processed.php?unformatted_date="
.$row["DATE_FORMAT(o.date, '%m/%d/%Y')"] ."&custId="
.$row["cust_id"] ."\">Add to Processed</a>";
Then for enter.into.processed.php script, call to a function that cleans
the $unformatted_date of any extraneous input that may include a shell
script (for security - won't go into that). Before your INSERT query,
use the second function offered (only, I haven't found it useful to
describe it as a function in my scripts). Incidentally, I think
Fernando (and George) would use the sequence $month,$day,$year (as
below) for us "estadounideses" when referencing 12/01/2002, which us
non-conformists should read as January 12, 2002 according to their code
following European-style date rendering.
Example:
$month=substr($unformatted_date,0,2);
$day= substr($unformatted_date,3,2);
$year=substr($unformatted_date,6,4);
$date="\"$year-$month-$day\"";
$query="INSERT INTO processed
SET cust_id=$custId,
date = $date";
I don't know if this is the best approach for this type of operation,
but it works. Let me know if you require more detail if this type of
operation is what you require.
For a form entry where the user is inputting the date, you would lead
off with something like Fernando's code and use George's approach to
display it to the user in a receipt. But if you want to validate the
date (such as a date of birth), you would need to get a little more
complicated and use sessions.
Michael Conway
[EMAIL PROTECTED]
(703) 968-8875
-----Original Message-----
From: George Pitcher [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 06, 2003 9:42 AM
To: 'Ali McLeod'; [EMAIL PROTECTED]
Subject: RE: [PHP-DB] displaying a MySQL date in a different format
Ali,
SELECT DATE_FORMAT('1997-10-04','%d/%m/%b/%Y');
gives you '04/10/1997'.
replace the date with the column name and I would suggest giving it an
alias
such as eurodate or whatever fits your application.
HTH
George in Oxford
> -----Original Message-----
> From: Bernain, Fernando G. [mailto:[EMAIL PROTECTED]]
> Sent: 06 January 2003 1:35 pm
> To: 'Ali McLeod'; [EMAIL PROTECTED]
> Subject: RE: [PHP-DB] displaying a MySQL date in a different format
>
>
> I dont know if its the "best" solution, but I made a function
> that "cut" the
> date:
>
> function sqldate($fecha) {
>
> $dia=substr($fecha,0,2);
> $mes=substr($fecha,3,2);
> $anio=substr($fecha,6,4);
>
> $fechasql=$anio."/".$mes."/".$dia;
>
> return $fechasql;
>
> }
>
> You input 12/01/2002 and output 2002/01/12!!!!
>
>
> Fernando Bernain
> Senior A
> Business Process Outsourcing
>
> KPMG Argentina
> Tel: 54 11 4316 5754
> Fax: 54 11 4316 5734
> [EMAIL PROTECTED]
>
>
>
>
> -----Original Message-----
> From: Ali McLeod [mailto:[EMAIL PROTECTED]]
> Sent: Saturday, January 04, 2003 8:27 PM
> To: [EMAIL PROTECTED]
> Subject: [PHP-DB] displaying a MySQL date in a different format
>
>
> Can anyone help me. I am trying to display a date in a different
> format from
> that stored in a MySQL database. MySQL forces you to store it as
> yyyy-mm-dd
> but I want to display it as dd-mm-yyyy, or dd-mmmm
>
> Any ideas?
>
> Many thanks in advance
>
> Ali McLeod
> [EMAIL PROTECTED]
>
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
> Email Disclaimer
>
> The information in this email is confidential and may be
> legally privileged.
> It is intended solely for the addressee.
> Access to this email by anyone else is unauthorised.
> If you are not the intended recipient, any disclosure,
> copying, distribution
> or any action taken or omitted to be taken in reliance
> on it, is prohibited and may be unlawful.
> When addressed to our clients any opinions or advice
> contained in this email are subject to the terms and
> conditions expressed in the governing KPMG client engagement
> letter.
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
