php how to treat mysql field like a date?

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

re: php how to treat mysql field like a date?

In article <fPadnQQ0M5JfNkWiU-KYjA@giganews.com>,
"Yulia Yegenov" <yuliay@nospam.noadd.ru> wrote:
[color=blue]
> 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:[/color]

Actually it's MySQL doing that, but you're right about the order.
[color=blue]
> 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.[/color]

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 : funha@fung.arg | My email address is ROT13 encoded, decode to mail
--------------------------|--------------------------------------------------
<http://www.phplabs.com/> | PHP scripts and thousands of webmaster resources!

re: php how to treat mysql field like a date?

"Yulia Yegenov" <yuliay@nospam.noadd.ru> wrote in
news:fPadnQQ0M5JfNkWiU-KYjA@giganews.com:
[color=blue]
> 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
>
>[/color]

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