Date Format Help

[rscott7706]

Hi guys, I hate to ask for help, but honest I have been reading every post on date
formatting and trying the solutions, but I have hit a wall.

I will enter some code below. I hope someone will assist me in formatting the variable
$ts (timestamp in my MYSQL database) to month, day, year (in whatever form -
dosen't matter - 06/07/2006 or June 7, 2006 or any other variation - I don't care).

Please, please, put the exact code in, no "select from blah, etc.

I have used every coding sample and looked at every example, and the best I come up with
coverts the timestamp number to like the 1970's or something like that.

Code:
===================================================

$query = "SELECT * FROM openings ORDER BY id desc";
$mysql_result = mysql_query($query, $db);

// get each row
while($myrow = mysql_fetch_row($mysql_result))
{

//get data - eg, reading fields 0 and 1

$id=$myrow[0];
$cpny=$myrow[3];
$cont=$myrow[1];
$ti=$myrow[2];
$ph=$myrow[4];
$em=$myrow[5];
$opn=$myrow[6];
$desc=$myrow[7];
$req=$myrow[8];
$mn=$myrow[9];
$mx=$myrow[10];
$nd=$myrow[11];
$ts=$myrow[12];
$dent=$myrow[13];
print "</tr>\n<tr>\n";

// output row from database
echo "<td>
<h3>Opening ID#: $id</h3>
[!--coloro:#FF0000--][span style=\"color:#FF0000\"][!--/coloro--]Date Record Entered: $ts<br><br>[!--colorc--][/span][!--/colorc--]
Company: $cpny <br>
Contact: $cont <br>
Title: $ti <br>
Phone: $ph <br>
E-mail: <a href=mailto:$em>$em </a> <br>
Opening: $opn <br>
Description: $desc <br>
Requirements: $req <br>
Min $: $mn <br>
Max $: $mx <br>
Need Date: $nd <br>
===================================================<br><br></td>\n";
print "<TD></TD>\n";
}

[poirot]

Change the following line:

[code]$ts=$myrow[12];[/code]
to

[code]$ts = date("FORMAT_HERE", $myrow[12]);[/code]

Change to the proper format.

[code]<?php

$ts = time();

date("m/d/Y", $ts) // This is 06/08/2006
date("F j, Y", $ts); // This is June 8, 2006

?>[/code]

For more information on the format:
[a href=\"http://www.php.net\" target=\"_blank\"]http://www.php.net[/a]

[rscott7706]

poirot, thanks for your quick response, but i am starting to feel like a dunce.

By reading your note, I think it says to me I have to make two changes;

One at $ts = date("FORMAT_HERE", $myrow[12]);

Which I think means put the proper date format I want here, for example:

$ts = date("m/d/Y", $myrow[12]); (leaving out the second $ts - because if I keep it I get an
error.

Then in addition I have to format it somewhere else, but the place I think I need it does
not match your second example:

Date Record Entered: $ts

Am I just not understanding your examples?

[poirot]

RonScott, you just need to change that line and use the format you want.

Note that my second code block was just to show you different ways to format the timestamp, you must choose either of them.

[rscott7706]

It just is not working, here is my formating:

$ts=date('m/d/Y', $myrow[12]);


Here is the result: [a href=\"http://www.egca.org/new_site/members-only/jm_postings.php\" target=\"_blank\"]jm_postings[/a]


Check out how it takes any date (in the month of June, 2006) and makes it
08/20/1970.

I have tried every variation, such as $ts=date('r', $myrow[12]);

No luck....

I know there is some small thing I am missing, but can't figure it out.

[img src=\"style_emoticons/[#EMO_DIR#]/huh.gif\" style=\"vertical-align:middle\" emoid=\":huh:\" border=\"0\" alt=\"huh.gif\" /]

[joquius]

[!--quoteo(post=381844:date=Jun 9 2006, 03:17 PM:name=RonScott)--][div class=\'quotetop\']QUOTE(RonScott @ Jun 9 2006, 03:17 PM) [snapback]381844[/snapback][/div][div class=\'quotemain\'][!--quotec--]
It just is not working, here is my formating:

$ts=date('m/d/Y', $myrow[12]);
Here is the result: [a href=\"http://www.egca.org/new_site/members-only/jm_postings.php\" target=\"_blank\"]jm_postings[/a]
Check out how it takes any date (in the month of June, 2006) and makes it
08/20/1970.

I have tried every variation, such as $ts=date('r', $myrow[12]);

No luck....

I know there is some small thing I am missing, but can't figure it out.

[img src=\"style_emoticons/[#EMO_DIR#]/huh.gif\" style=\"vertical-align:middle\" emoid=\":huh:\" border=\"0\" alt=\"huh.gif\" /]
[/quote]
Tell me.........was this "date" the 20th of june 2006?
Are you inputting 20062006 (which comes out 08/20/1970) or the actually timestamp which should be about 1150000000?

remember date (FORMAT, TIME_SECONDS)

[rscott7706]

No, it is timestamp, BASICALLY 20060607

June 7, 2006

[joquius]

ok well as I said this is wrong. The time stamp has to be taken from a time(); function, which gives you the number of seconds which have passed from 0:00 1/1/1970. date() works with these time stamps. it calculates the date based on the number of seconds you input, and adds those seconds to the date of 1/1/1970. you are giving date() 20060607, which adds that many seconds to the above date. That's why you are getting 08/20/1970. when you write the database insert the date from a time() output.

[rscott7706]

Wait a minute... This is a whole new dynamic.

I have an MYSQL database that has an automatic time stamp field.

When I say time stamp, it stamps the date and the time.

If I leave the variable $ts alone (don't try to format it), it comes out fine.

But in a format that makes it difficult for the uninformed to read.

It comes out 20060607

2006 the year, 06 the month, and 07 the day.

I am trying to format it to show any variation of:

06/07/2006

By the way, I tried:

$ts=time('m/d/Y', $myrow[12]);

It results in:

Date Record Entered: 1149864994

[joquius]

oh ok. but this is not advised:

[code]
$year = substr ($date, 0, 4);
$month = substr ($date, 4, 2);
$day = substr ($date, 6, 2);

$new_date = $month."/".$day."/".$year;
[/code]

can't you just dump the timestamp and go for putting time(); into the db? date() is much better for formatting.

[kenrbnsn]

Just use the function [a href=\"http://www.php.net/strtotime\" target=\"_blank\"]strtotime()[/a] to get the string into a usable format for the [a href=\"http://www.php.net/date\" target=\"_blank\"]date()[/a] function:
[code]<?php
$ds = "20060607";
echo date('m/d/Y',strtotime($ds));
?>[/code]

Ken

[rscott7706]

Ok, sorry to be such a bother...

I went to my, MYSQL database and changed the field to timestamp(8)

If I don't format the variable $ts, it shows 20060607.

If I format $ts=date('m/d/Y', $myrow[12]);

It shows Date Record Entered: 08/20/1970

I am in an endless loop and fell like I am draining your time
(I did send a donation to try to offset this).

Should I look for asnother form of displying the data, such as in a table or something?

If you look at the original note and the coding, maybe it is how the results are called that is
gumming this up.

[kenrbnsn]

Change [code]<?php $ts=date('m/d/Y', $myrow[12]); ?>[/code] to [code]<?php $ts=date('m/d/Y', strtotime($myrow[12])); ?>[/code]

Ken

[rscott7706]

kenrbnsn,

Thanks for your input, here is what I did:

I put the coding (changing your example variable from $ds to $ts since I don't have a $ds in my coding)
above the query select as follows:


$ts = "20060607";
echo date('m/d/Y',strtotime($ts));

It makes no difference. Man I hate being such a lunk, but it does seem like there is something
messing this up, that is stumping us all.

Ken on your note of 8:20 am,

Change
CODE
<?php $ts=date('m/d/Y', $myrow[12]); ?>
to
CODE
<?php $ts=date('m/d/Y', strtotime($myrow[12])); ?>

Gives me an error:

Parse error: parse error, unexpected '<' in /home/egcaorga/public_html/new_site/members-only/jm_postings.php on line 192

Whisch is that code line...

[craygo]

In my experience, timestamp is useless when coding with php since it is just as easy to insert a date in the proper format using php.

I would change the field to just a date field which would output 2006-06-06, then using ken's code of

[code]$ts = date("m/d/Y", strtotime($myrow[12]));[/code]

Should fix your problem

Ray

[rscott7706]

OK, I think this is a great option.

Only one shortcoming here - so let's switch gears and see if we can attack from
a differennt angle.

I can leave the timestamp as a field - I won't even show on the web page -
just to sort and delete records in the future.

Now, here is the next trick - the input is for a date filed istaken from a web
form the users submit.

Now, there is no way in html to properly input the date. (I don't think)

For instance, if I put a form field that says "Date" - the user is going to enter
06/07/2006 (for instance).

I need to translate that to 20060607 prior to proccess.php submitting it to the
database.

Can we use PHP to do this?

[img src=\"style_emoticons/[#EMO_DIR#]/smile.gif\" style=\"vertical-align:middle\" emoid=\":smile:\" border=\"0\" alt=\"smile.gif\" /]

[kenrbnsn]

Use a combination of strtotime() and date(). These two functions when used together are very powerful.

[code]<?php
$input = "6/7/2006"
$output = date('Ymd',strtotime($input));
echo $output;
?>[/code]

Ken

[poirot]

[i]Mes ami[/i], that is a MySQL timestamp. In this case, you don't need to convert it to UNIX and then use date().

Actually, if you do this, it may screw up some dates, since UNIX timestamps don't work prior to January 1 1970. You can and should use MySQL directly to format the dates.

For June 9, 2006 format:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] DATE_FORMAT(row_name, [color=red]'%M %e, %Y'[/color]) [color=green]AS[/color] time [color=green]FROM[/color] [color=orange]`table`[/color] [!--sql2--][/div][!--sql3--]

For 06/09/2006 format:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] DATE_FORMAT(row_name, [color=red]'%m/%d/%Y'[/color]) [color=green]AS[/color] time [color=green]FROM[/color] [color=orange]`table`[/color] [!--sql2--][/div][!--sql3--]

[rscott7706]

Guys, I do appreciate your input, and I try like crazy to make it work in my particular
coding situation, but it seems like we are drifting a bit to an area that is helpful in
many situations, but not in this particular one.

Ken and poirot, the examples you give are great, but suited for a new query. I have an
existing query, with results output.

I am trying to get a variable ($ts) to format correctly, but it seems as if there is something
impeding it working properly.

Here is my code again, if you want I can set up a test database and give you the logon
info so you can test your results. Everything we have tried so far does not work with
my coding.

I have red lined the areas I see as needing attention:

$query = "SELECT * FROM openings ORDER BY id desc";
$mysql_result = mysql_query($query, $db);

// get each row
while($myrow = mysql_fetch_row($mysql_result))
{

//get data - eg, reading fields 0 and 1

$id=$myrow[0];
$cpny=$myrow[3];
$cont=$myrow[1];
$ti=$myrow[2];
$ph=$myrow[4];
$em=$myrow[5];
$opn=$myrow[6];
$desc=$myrow[7];
$req=$myrow[8];
$mn=$myrow[9];
$mx=$myrow[10];
$nd=$myrow[11];
[!--coloro:#FF0000--][span style=\"color:#FF0000\"][!--/coloro--]$ts=$myrow[12];[!--colorc--][/span][!--/colorc--]
$dent=$myrow[13];
print "</tr>\n<tr>\n";

// output row from database
echo "<td>
<h3>Opening ID#: $id</h3> [!--coloro:#FF0000--][span style=\"color:#FF0000\"][!--/coloro--]Date Record Entered: $ts[!--colorc--][/span][!--/colorc--]<br><br>
Company: $cpny <br>
Contact: $cont <br>
Title: $ti <br>
Phone: $ph <br>
E-mail: <a href=mailto:$em>$em </a> <br>
Opening: $opn <br>
Description: $desc <br>
Requirements: $req <br>
Min $: $mn <br>
Max $: $mx <br>
Need Date: $nd <br>

Maybe I should try to output the results differently, maybe in a table or something
like that.

I would just be concerned that I would construct a whole new php coding
and get the same results




By the way poirot, if I use your example alone,

SELECT DATE_FORMAT(row_name, '%M %e, %Y') AS time FROM `table`

it does work great!

Obviously though then I don't get he other needed fields.

[poirot]

If you want to format the date using PHP, you can follow ken's suggestion and change the line that says $ts = ...

If you want to format the date using MySQL, change your query to something like:

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] *, DATE_FORMAT(date_row_here, [color=red]'%M %e, %Y'[/color]) [color=green]AS[/color] time [color=green]FROM[/color] [color=orange]`table`[/color] [!--sql2--][/div][!--sql3--]

MySQL will return you an extra field, which contains an already formated date. Then change $ts = myrow[12] to $ts = myrow[14] (or whatever is the greatest number) and you're set.

[rscott7706]

[b]That did it!!!![/b]

I think - I just want to qualify this so I can take some time to add and delete
entries and test it thoroughly.

T H A N K Y O U ! !

poirot, joquius, kenrbnsn, craygo

- and anyone else I may have forgotten.

I appreciate you folks sticking with me through this thing.

An additional donation to PHP Freaks will be coming next payday.

And I will faithfully click on your sponsors...

This site rocks..

[img src=\"style_emoticons/[#EMO_DIR#]/laugh.gif\" style=\"vertical-align:middle\" emoid=\":laugh:\" border=\"0\" alt=\"laugh.gif\" /] [img src=\"style_emoticons/[#EMO_DIR#]/wink.gif\" style=\"vertical-align:middle\" emoid=\":wink:\" border=\"0\" alt=\"wink.gif\" /]

[WendyLady]

[code]$ts = date("m/d/Y", strtotime($myrow[12]));[/code]


I have been searching everywhere for the past 2 weeks to simply reformat a MySQL date for the project I'm working on -- I just want it to look recognizable! -- and finally this!

Thanks!

Wendy