DATE FORMAT

[johnseito]

Hello everyone,

 

I created a timestamp in mysql with this code:

entrydate TIMESTAMP PRIMARY KEY,

and this comes out as 2008-01-26 19:00:41

but I want the date to come in this format:

 

January 12th, 2008 at 2:51 am

 

could someone tell how I can do this?

 

thanks

 

 

[AndyB]

Good start. Put the date in the database using the ISO standard as you have.

 

You can display the date in whatever format you want. Check the nice little table in the manual to see how.

 

http://ca.php.net/manual/en/function.date.php

[johnseito]

ok thanks. so How can I get the date out and manipulate it? 

I didn't really set a variable for it in PHP, and I use this code and I can export everything out.

 

$sql = "SELECT * FROM tlb";
$result = mysql_query($sql, $conn);
   
      while ($row = mysql_fetch_array($result)) {
    
      }
    

 

This code even pulls the dates out, because it's pulling everything from the tlb array.

 

so how do I pull the date out and manipulate it, and if I didn't want to pull it out how do I set PHP to do that?

 

 

[Barand]

You did did create a php variable containing the date. It's $row['entrydate']

 

Don't use "SELECT * FROM ... ", specify the columns you need (unless you really do need all of them). e.g.

 

SELECT id, name, entrydate FROM ....

[laffin]

function dateformat($from="",$format="M d, Y")
{
   if(empty($from))
          $ft=time();
   else
          $ft=strtotime($from);
  return date($format,$ft);
}

$sql = "SELECT * FROM tlb";
$result = mysql_query($sql, $conn);
   
      while ($row = mysql_fetch_array($result)) {
         $row['dt']=dateformat($row['dt']);
      }
[code]

[/code]

[johnseito]

Don't use "SELECT * FROM ... ", specify the columns you need (unless you really do need all of them). e.g.

 

SELECT id, name, entrydate FROM ....

 

I changed the code to this:

 

$conn  = mysql_connect("localhost", "username", "psswrd");
mysql_select_db("db", $conn);
$sql = "SELECT name FROM info";
$result = mysql_query($sql, $conn);
   
      while ($row = mysql_fetch_array($result)) {

      }

 

This code gave me everything, not just the name as I wanted.

why is that? you know what I can do to change it?

 

[Barand]

http://www.w3schools.com/sql/sql_intro.asp

[johnseito]

Thanks for the website, supposedly that is how it should be but when I do it in my code, everything comes out. Weird

[johnseito]

Ok I got it, thanks it works.

 

now I'll see how I can manipulate the entrydate field to be like this

 

January 12th, 2008 at 2:51 am

[PFMaBiSmAd]

Using any computer based language, like the SQL query language, requires that you know the basics to be effective at using it.

 

On the third page at the link that Barand posted is how you select specific information from a database. If you did not get that far or did not understand what was on that page, then go back and reread all the pages that are part of that tutorial.

 

What you are asking, to only return the name you wanted, is a fundamental concept of using a database. If you are asking how to do that, then you need to spend more time getting up to speed on the overall concept of using databases before you attempt to actually write or use code. This is a little like getting into a car to drive it and knowing that there are gas and brake pedals and what each of them does before you start the engine.

[johnseito]

PFMaBiSmAd -

you're right, thanks.  I do have sql experience I work with it, I export datas out of it from database.  just a bit new to the intergation of PHP and Mysql

 

 

function dateformat($from="",$format="M d, Y")
{
   if(empty($from))
          $ft=time();
   else
          $ft=strtotime($from);
  return date($format,$ft);
}

 

what is the $from"",  I put $from="entrydate"

 

         $row['dt']=dateformat($row['dt']);

 

what is 'dt"?  I put dt as entrydate not sure if that would be correct

   

[fenway]

Why not use DATE_FORMAT( entry_date, <your desired format> )?

[johnseito]

 

DATE_FORMAT( entry_date, <your desired format> )?

 

I tried this but it gaves me an error,

 

then I tried this and it gave me a date but not the date that was in the database, seems to be some date that was already set.

 

$datetime = strtotime('$entrydate');

echo date("l, jS F, Y h:i:s a", $datetime);

[laffin]

this is why i gave u a function to use

function dateformat($from="",$format="M d, Y")
{
   if(empty($from))
          $ft=time();
   else
          $ft=strtotime($from);
  return date($format,$ft);
}

 

it's a function, u call it from yer code, not change it (unless u know what u are doing).

in my original post i gave an example of how to use it

$sql = "SELECT * FROM tlb";
$result = mysql_query($sql, $conn);
   
      while ($row = mysql_fetch_array($result)) {
         $row['dt']=dateformat($row['dt']);
      }

 

for a more precise description of what it does: dateformat converts a date string to another date format.

the calling is simple

$var=dateformat($olddate,$newformat)

$var is the variable to hold the new date

$olddate, is the variable holding the old date

$newformat is how u want the new date to look like, use date function to help u try different formats. this parameter is optional as a default format is set in the function.

[fenway]

 

DATE_FORMAT( entry_date, <your desired format> )?

 

I tried this but it gaves me an error,

 

then I tried this and it gave me a date but not the date that was in the database, seems to be some date that was already set.

 

$datetime = strtotime('$entrydate');

echo date("l, jS F, Y h:i:s a", $datetime);

This is a MYSQL function, not a php function.

[johnseito]

Look I got a Date_format code from the website:

 

   $query ="SELECT entrytitle, entrytext,";
   $query.=" DATE_FORMAT(entrydate, '%M %d, %Y') AS date";
   $query.=" FROM tbl ORDER BY entrydate DESC LIMIT 10";
   $result=mysql_query($query);
   while (list($entrytitle,$entrytext,$entrydate) = 
    mysql_fetch_row($result)) {
       echo "<dt><b>$entrytitle ($entrydate)</b></dt>";
       echo "<dd>$entrytext</dd>";

 

and it looks like it is a php function, i mimic this and it didn't work.

[fenway]

That doesn' tlook like a php function at all.  And it doesn't "work" because now you've changed the name of the field.

[johnseito]

And it doesn't "work" because now you've changed the name of the field.

 

where did I changed the name of the field?

[fenway]

Sorry, my bad... didn't see the list() there, thought you were referring to the column name, which has now been aliases to date.