Page 1 of 2

Date conversion

Posted: Tue Oct 10, 2006 7:48 pm
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);

?>

Posted: Tue Oct 10, 2006 7:56 pm
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

Posted: Tue Oct 10, 2006 8:22 pm
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

Posted: Wed Oct 11, 2006 3:56 am
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.

Posted: Wed Oct 11, 2006 10:18 am
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?

Posted: Wed Oct 11, 2006 10:42 am
by RobertGonzalez

Code: Select all

<?php
echo '<td align="left">' . date('M d, Y', $row['date']) . '</td>';
?>

Posted: Wed Oct 11, 2006 10:43 am
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";
}

Posted: Wed Oct 11, 2006 10:48 am
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.

Posted: Wed Oct 11, 2006 10:49 am
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]

Posted: Wed Oct 11, 2006 10:53 am
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();

Posted: Wed Oct 11, 2006 10:57 am
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";
		}
	}

Posted: Wed Oct 11, 2006 11:02 am
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. :(

Posted: Wed Oct 11, 2006 11:04 am
by RobertGonzalez
Ok, how are your dates stored in your database?

Posted: Wed Oct 11, 2006 11:06 am
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>';

Posted: Wed Oct 11, 2006 11:09 am
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";
		}
	}