Jump to content

Date Format Help


rscott7706

Recommended Posts

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";
}
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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\" /]

Link to comment
Share on other sites

[!--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)
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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...

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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\" /]
Link to comment
Share on other sites

[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--]
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

[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\" /]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.