php how to treat mysql field like a date?

[Yulia Yegenov]

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

[Senator Jay Billington Bulworth]

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!

[Don Crossman]

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