Date conversion

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Javrixx
Forum Commoner
Posts: 32
Joined: Thu Aug 24, 2006 2:05 pm

Date conversion

Post by Javrixx »

Yeah, I've searched everywhere and tried everything, but can't get this figured out. This is almost the last part of my project. Below is my code. All I need is to convert the mySQL date timestamp into other date formats. Right now it will display something like 2006-10-25. I need it to show up either as 10-25-2006 or October 25, 2006. Either way it doesn't matter but I'm pulling my hair out getting this part done. Below is my code. I'm only concerned about the date part, everything else works fine.

Code: Select all

<?php
// include needed files
require('../../db_config.php');
require('../../global.php');
require("../../lib/Pager.class.php");


// connect to the database
db_connect($mysql['username'],$mysql['password'],$mysql['database'],$mysql['host']);

// assign config options from database to an array
$config = get_config($mysql['prefix']);

debug_mode($config['debug_mode']);

// remove users that have not verified their email after 72 hours if email verification is enabled
if($config['verify_email']=='true' && $config['prune_inactive_users']=='true'){
	PruneInactiveUsers($mysql['prefix']);
}

// make sure user is logged in
require('../auth.inc.php');

////////////////

// start class
$p = new Pager;

// results per page
$limit = 10;

// Find the start depending on $_GET['page'] (declared if it's null)
$start = $p->findStart($limit);

//////////////////////
$sql= 'SELECT * FROM '.$mysql['prefix'].'users WHERE username="'.$_SESSION['username'].'"';

if(!$result = mysql_query($sql))
{
	die('The following MySQL query failed. User data could not be retrieved. '.$sql);
}


// assign the user info to variables
while (($row = mysql_fetch_array($result)) != false)
{
	$userid = $row['id'];
}


//////enter the user's ID
if($userid != 1)
{
    header('Location: ../../support/');
    exit;
}

/////////////////

// date time code goes here?

///////////////






{
	// list all users
	$sql = 'SELECT * FROM `'.$mysql['prefix'].'results` WHERE `user_id`='.$userid.' ORDER BY date';
	$sql2 = $sql.' LIMIT '.$start.', '.$limit;
}

if ($result = mysql_query($sql2))
{
	if (@mysql_num_rows($result) > 0)
	{
		$filelist = '';
		while (($row = mysql_fetch_array($result)) != false)
		{
		
			$filelist .= '<tr class="style1"><td align="left"><b>'.str_chop($row['filename'],25).'</b></td><td align="left">'.$row['date'].'</td><td align="left"><a href="'.$row['folder'].'/'.$row['file'].'.html">View Results</a></td><td align="left"><a href="'.$row['folder'].'/'.$row['file'].'.xls">Download Excel</a></td><td align="left"><a href="'.$row['folder'].'/'.$row['file'].'.zip">Download Zip</a></td></tr>'."\n";
		}
	}
	else
	{
		if (empty($_GET['search']))
		{
			$filelist = '<tr><td colspan="4"><span class="style11">There are currently no results.</span></td></tr>';
		}
		else
		{
			$filelist = '<tr><td colspan="4"><span class="style11">Your search returned 0 results.</span></td></tr>';
		}
	}
}

else
{
	die('The MySQL query failed. MySQL said: '.mysql_error());
}

$count = mysql_num_rows(mysql_query($sql));
$pages = $p->findPages($count, $limit);
// get the page list
$pagelist = $p->pageList($_GET['page'], $pages);

?>
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

You can let mysql format the date to your liking via date_format, see http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html
chakhar86
Forum Commoner
Posts: 45
Joined: Mon Jun 05, 2006 1:36 am
Contact:

Post by chakhar86 »

Easy ways to convert your date is use the date() function.

Code: Select all

$date = "2006-10-11";

echo date("M, d Y", $date);   //it will echoed as "Oct, 11 2006";
To list complete format for date() function, see php manual.
You can get it fre from:
http://www.php.net
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

no, it will print
Notice: A non well formed numeric value encountered in filenname on line x
Jan, 01 1970
php's date() does not take a string as second parameter but a unix timestamp.
Javrixx
Forum Commoner
Posts: 32
Joined: Thu Aug 24, 2006 2:05 pm

Post by Javrixx »

chakhar86 wrote:Easy ways to convert your date is use the date() function.

Code: Select all

$date = "2006-10-11";

echo date("M, d Y", $date);   //it will echoed as "Oct, 11 2006";
To list complete format for date() function, see php manual.
You can get it fre from:
http://www.php.net

Thanks for the respones guys. So I have a stuipd question. I define the variable date like you say above, but how do I plug that into another variable? My code is using a variable which will populate a table with different rows, the cell in the table that will print the date looks like:

Code: Select all

<td align="left">'.$row['date'].'</td>
So how do I convert it when it's like that?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Code: Select all

<?php
echo '<td align="left">' . date('M d, Y', $row['date']) . '</td>';
?>
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Code: Select all

{
	// list all users
	$sql = "SELECT
			Date_format(`date`, '%W %M %Y') as datef,filename,folder,file
		FROM
			`".$mysql['prefix'].'results`
		WHERE
			`user_id`='.$userid.'
		ORDER BY
			date';
	$sql2 = $sql.' LIMIT '.$start.', '.$limit;
}

if ($result = mysql_query($sql2)) {
	echo $row['datef'], "<br />\n";
}
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

On a side note, volka's way of doing it will be fast to the application overall. If you are going to be handling date conversions that do not need dynamic conversion, I would suggest you use volka's method.
Javrixx
Forum Commoner
Posts: 32
Joined: Thu Aug 24, 2006 2:05 pm

Post by Javrixx »

Also, it is a timestamp when the register in seconds. I don't know if that makes a difference or not or how this works. :(


[edit]
Sorry, it's NOT a timestamp. I'm entering the date manually for these, I got mixed up with another table for a moment, sorry!
[/edit]
Last edited by Javrixx on Wed Oct 11, 2006 10:54 am, edited 1 time in total.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

The unix time stamp is measured as the number of seconds from Jan 1, 1970 00:00:00. The PHP function time() will return the current unixtimestamp, as will the SQL query:

Code: Select all

SELECT UNIX_TIMESTAMP();
Javrixx
Forum Commoner
Posts: 32
Joined: Thu Aug 24, 2006 2:05 pm

Post by Javrixx »

Everah wrote:The unix time stamp is measured as the number of seconds from Jan 1, 1970 00:00:00. The PHP function time() will return the current unixtimestamp, as will the SQL query:

Code: Select all

SELECT UNIX_TIMESTAMP();
Sorry, I just edited my post. It's not a timestamp, I'm entering the date in manually.


So I did your suggestion first, I'll try volka's in a moment. Yours seems to work, except now all the dates are displaying "Dec 31, 1969" on the page. But they're in the correct format I need. Here's the exact code I'm using:

Code: Select all

// list all users
	$sql = 'SELECT * FROM `'.$mysql['prefix'].'results` WHERE `user_id`='.$userid.' ORDER BY date';
	$sql2 = $sql.' LIMIT '.$start.', '.$limit;
}

if ($result = mysql_query($sql2))
{
	if (@mysql_num_rows($result) > 0)
	{
		$filelist = '';
		while (($row = mysql_fetch_array($result)) != false)
		{
		
			$filelist .= '<tr class="style1"><td align="left"><b>'.str_chop($row['filename'],25).'</b></td><td align="left">'.date('M d, Y', $row['date']).'</td><td align="left"><a href="'.$row['folder'].'/'.$row['file'].'.html">View Results</a></td><td align="left"><a href="'.$row['folder'].'/'.$row['file'].'.xls">Download Excel</a></td><td align="left"><a href="'.$row['folder'].'/'.$row['file'].'.zip">Download Zip</a></td></tr>'."\n";
		}
	}
Javrixx
Forum Commoner
Posts: 32
Joined: Thu Aug 24, 2006 2:05 pm

Post by Javrixx »

Sorry volka, I'm confused on your code and where to place things. I tried just replacing my code with yours, but half of the other stuff didn't work. So I tried taking bits and pieces and putting it into my code but I can't seem to get it to work, the page just goes blank when I go to it. I'll keep trying, but man this is a headache, I thought it would be really simple. :(
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Ok, how are your dates stored in your database?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

in case of doubt run a SHOW CREATE TABLE tableName query.
e.g. via phpMyAdmin or (if you must) as

Code: Select all

$sql = 'SHOW CREATE TABLE `'.$mysql['prefix'].'results`';
$r = mysql_query($sql) or die(mysql_error());
echo '<pre>';
while($row=mysql_fetch_array($r, MYSQL_ASSOC)) {
	print_r($row);
}
echo '</pre>';
Javrixx
Forum Commoner
Posts: 32
Joined: Thu Aug 24, 2006 2:05 pm

Post by Javrixx »

Oh snap I got it working! I used volka's code, changed some of it using Everah's code, and nudged it into my own so it would work. Just took some obvious thinking on my part. :D

Works flawlessly, the only last request/advice I am asking for... It is sorting it by date, but how do I reverse this? The top of the list is the oldest date, I want the top to be the newest. Thanks so much guys, you've helped me sooooo much. Below is the code:

Code: Select all

// list all users
        $sql = "SELECT
                        Date_format(`date`, '%M %D, %Y') as datef,filename,folder,file
                FROM
                        `".$mysql['prefix'].'results`
                WHERE
                        `user_id`='.$userid.'
                ORDER BY
                        date';
        $sql2 = $sql.' LIMIT '.$start.', '.$limit;
}

if ($result = mysql_query($sql2))
{
	if (@mysql_num_rows($result) > 0)
	{
		$filelist = '';
		while (($row = mysql_fetch_array($result)) != false)
		{
		
			$filelist .= '<tr class="style1"><td align="left"><b>'.str_chop($row['filename'],20).'</b></td><td align="left">'.$row['datef'].'</td><td align="left"><a href="'.$row['folder'].'/'.$row['file'].'.html">View Results</a></td><td align="left"><a href="'.$row['folder'].'/'.$row['file'].'.xls">Download Excel</a></td><td align="left"><a href="'.$row['folder'].'/'.$row['file'].'.zip">Download Zip</a></td></tr>'."\n";
		}
	}
Post Reply