Jump to content


Photo

Date Format Help


  • Please log in to reply
21 replies to this topic

#1 rscott7706

rscott7706
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts
  • LocationLakeside CA

Posted 09 June 2006 - 03:50 AM

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";
}


#2 poirot

poirot
  • Members
  • PipPipPip
  • Advanced Member
  • 646 posts
  • LocationAustin, TX

Posted 09 June 2006 - 04:44 AM

Change the following line:

$ts=$myrow[12];
to

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

Change to the proper format.

<?php

$ts = time();

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

?>

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

#3 rscott7706

rscott7706
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts
  • LocationLakeside CA

Posted 09 June 2006 - 05:01 AM

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?

#4 poirot

poirot
  • Members
  • PipPipPip
  • Advanced Member
  • 646 posts
  • LocationAustin, TX

Posted 09 June 2006 - 05:09 AM

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.
~ D Kuang

#5 rscott7706

rscott7706
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts
  • LocationLakeside CA

Posted 09 June 2006 - 02:17 PM

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



#6 joquius

joquius
  • Members
  • PipPipPip
  • Advanced Member
  • 319 posts

Posted 09 June 2006 - 02:33 PM

[!--quoteo(post=381844:date=Jun 9 2006, 03:17 PM:name=RonScott)--][div class=\'quotetop\']QUOTE(RonScott @ Jun 9 2006, 03:17 PM) View Post[/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)
z..z..z..z..z..z..z..z..

#7 rscott7706

rscott7706
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts
  • LocationLakeside CA

Posted 09 June 2006 - 02:44 PM

No, it is timestamp, BASICALLY 20060607

June 7, 2006

#8 joquius

joquius
  • Members
  • PipPipPip
  • Advanced Member
  • 319 posts

Posted 09 June 2006 - 02:48 PM

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

#9 rscott7706

rscott7706
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts
  • LocationLakeside CA

Posted 09 June 2006 - 02:57 PM

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

#10 joquius

joquius
  • Members
  • PipPipPip
  • Advanced Member
  • 319 posts

Posted 09 June 2006 - 03:02 PM

oh ok. but this is not advised:

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

$new_date = $month."/".$day."/".$year;

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

#11 kenrbnsn

kenrbnsn
  • Staff Alumni
  • Advanced Member
  • 8,235 posts
  • LocationHillsborough, NJ, USA

Posted 09 June 2006 - 03:07 PM

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:
<?php
$ds = "20060607";
echo date('m/d/Y',strtotime($ds));
?>

Ken

#12 rscott7706

rscott7706
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts
  • LocationLakeside CA

Posted 09 June 2006 - 03:13 PM

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.

#13 kenrbnsn

kenrbnsn
  • Staff Alumni
  • Advanced Member
  • 8,235 posts
  • LocationHillsborough, NJ, USA

Posted 09 June 2006 - 03:20 PM

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

Ken


#14 rscott7706

rscott7706
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts
  • LocationLakeside CA

Posted 09 June 2006 - 03:32 PM

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



#15 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 09 June 2006 - 03:42 PM

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

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

Should fix your problem

Ray

#16 rscott7706

rscott7706
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts
  • LocationLakeside CA

Posted 09 June 2006 - 03:50 PM

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

#17 kenrbnsn

kenrbnsn
  • Staff Alumni
  • Advanced Member
  • 8,235 posts
  • LocationHillsborough, NJ, USA

Posted 09 June 2006 - 04:14 PM

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

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

Ken

#18 poirot

poirot
  • Members
  • PipPipPip
  • Advanced Member
  • 646 posts
  • LocationAustin, TX

Posted 09 June 2006 - 04:18 PM

Mes ami, 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, '%M %e, %Y') AS time FROM `table` [!--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, '%m/%d/%Y') AS time FROM `table` [!--sql2--][/div][!--sql3--]
~ D Kuang

#19 rscott7706

rscott7706
  • Members
  • PipPipPip
  • Advanced Member
  • 88 posts
  • LocationLakeside CA

Posted 09 June 2006 - 04:53 PM

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.

#20 poirot

poirot
  • Members
  • PipPipPip
  • Advanced Member
  • 646 posts
  • LocationAustin, TX

Posted 09 June 2006 - 04:56 PM

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, '%M %e, %Y') AS time FROM `table` [!--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.
~ D Kuang




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users