RE: [PHP-DB] displaying a MySQL date in a different format
[snip] > 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; As an alternative, you can use a query such as this: $query = "SELECT DATE_FORMAT(o.date, '%m/%d/%Y') as alias, ... and then print the value with $date = $row['alias']; echo $date; instead of using row["DATE_FORMAT(o.date, '%m/%d/%Y')"]; It makes things easier to read in your code, too... ---John W. Holmes... PHP Architect - A monthly magazine for PHP Professionals. Get your copy today. http://www.phparch.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] displaying a MySQL date in a different format
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 Add to Processed"; 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 > -mm-dd > but I want to display it as dd-mm-, or dd- > > 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 intend
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 > -mm-dd > but I want to display it as dd-mm-, or dd- > > 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
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 -mm-dd but I want to display it as dd-mm-, or dd- 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
Re: [PHP-DB] displaying a MySQL date in a different format
SELECT DATE_FORMAT(NOW(), '%m/%d/%Y'); will out put 01/04/2003. replace NOW() with you're column name. check out hte mysql manual for all of the date variations. hth jeff "Ali McLeod" cc: Subject: [PHP-DB] displaying a MySQL date in a different format 01/04/2003 06:27 PM 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 -mm-dd but I want to display it as dd-mm-, or dd- 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php