By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,501 Members | 2,790 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,501 IT Pros & Developers. It's quick & easy.

php how to treat mysql field like a date?

P: n/a
I have a query that looks like this: (I insert the date created with the php
date function)

$status = "Active";

//(I cannot use the mysql timestamp function for other reason).
$curdate = date("m/j/Y H:i");
$owner = "not assigned";

@ $db_connect = mysql_connect("localhost", "$db_username", "$db_password");
if (!$db_connect)
{
echo "Unable to connect to the database;
exit;
}
mysql_select_db("$db");
$query = "insert into tickets (user, status, loannum, problem, description,
multiples, ticketnumber, ip, office, submissiontime, ticketowner) values
('$user1', '$status', '$loannum', '$problem', '$description', '$multiples',
'$ticketnumber', '$ip', '$office', '$curdate', '$owner')";
$result = mysql_query($query);

I need to pull out the last fifty records from the database, so I use this
query:

$order = $order;
if ($order ==""){
$order = "user";
}

mysql_select_db("$db");
$query = "select * from tickets ORDER by $order";
$result = mysql_query($query);
$num = mysql_num_rows($result);
if I order the tickets by submission time, it orders the items, but not
correctly. It looks like php treating the submissiontime like integer:
example:

12/1/2003 16:51

12/1/2003 16:58

12/10/2003 12:29

12/10/2003 12:36

12/11/2003 07:10

12/2/2003 09:16

How can I tell php treat the submissiontime field in mysql as a date so it
can be ordered correctly? I need to do this so I can do some maths and some
sorting on the the date.

TIA

Yulia
Jul 17 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
In article <fP********************@giganews.com>,
"Yulia Yegenov" <yu****@nospam.noadd.ru> wrote:
I have a query that looks like this: (I insert the date created with the php
date function)

//(I cannot use the mysql timestamp function for other reason).
$curdate = date("m/j/Y H:i");

[major snip]

if I order the tickets by submission time, it orders the items, but not
correctly. It looks like php treating the submissiontime like integer:
Actually it's MySQL doing that, but you're right about the order.
12/1/2003 16:51

12/1/2003 16:58

12/10/2003 12:29

How can I tell php treat the submissiontime field in mysql as a date so it
can be ordered correctly? I need to do this so I can do some maths and some
sorting on the the date.


Use date('m/d/Y H:i') instead, so that the day gets zero-padded. This
should get MySQL to sort them the way you need, e.g. 12/01 will come
before 12/10.

Or, you could use a datetime column in the MySQL table (assuming you
aren't already).

hth

--
Bulworth : fu***@fung.arg | My email address is ROT13 encoded, decode to mail
--------------------------|--------------------------------------------------
<http://www.phplabs.com/> | PHP scripts and thousands of webmaster resources!
Jul 17 '05 #2

P: n/a
"Yulia Yegenov" <yu****@nospam.noadd.ru> wrote in
news:fP********************@giganews.com:
I have a query that looks like this: (I insert the date created with
the php date function)

$status = "Active";

//(I cannot use the mysql timestamp function for other reason).
$curdate = date("m/j/Y H:i");
$owner = "not assigned";

@ $db_connect = mysql_connect("localhost", "$db_username",
"$db_password"); if (!$db_connect)
{
echo "Unable to connect to the database;
exit;
}
mysql_select_db("$db");
$query = "insert into tickets (user, status, loannum, problem,
description, multiples, ticketnumber, ip, office, submissiontime,
ticketowner) values ('$user1', '$status', '$loannum', '$problem',
'$description', '$multiples', '$ticketnumber', '$ip', '$office',
'$curdate', '$owner')"; $result = mysql_query($query);

I need to pull out the last fifty records from the database, so I use
this query:

$order = $order;
if ($order ==""){
$order = "user";
}

mysql_select_db("$db");
$query = "select * from tickets ORDER by $order";
$result = mysql_query($query);
$num = mysql_num_rows($result);
if I order the tickets by submission time, it orders the items, but
not correctly. It looks like php treating the submissiontime like
integer: example:

12/1/2003 16:51

12/1/2003 16:58

12/10/2003 12:29

12/10/2003 12:36

12/11/2003 07:10

12/2/2003 09:16

How can I tell php treat the submissiontime field in mysql as a date
so it can be ordered correctly? I need to do this so I can do some
maths and some sorting on the the date.

TIA

Yulia


Or, you might do something like "select * from tickets order by
unix_timestamp($order) desc limit 50". This would get the newest 50
records.
Jul 17 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.