473,221 Members | 2,135 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,221 software developers and data experts.

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
Jul 17 '05 #1
2 3161
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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Albert Ahtenberg | last post by:
Hi, I have a database with a date field. And I would like to see the report grouped by a ceratin month and year, how can I do that without multiple queries? Albert Ahtenberg
3
by: Noyb | last post by:
How do I convert a string into a value I can upload to mysql date field. Specifically, I have a user select a month, day and year from drop-downs, then I want them submitted to one date field....
10
by: Newsscanner | last post by:
Hello, In my MySQL database, one of the fields eople have to fill in is "DOB" (Date of Birth). I have now managed to begin inserting data into my DB via a form, the data type for the DOB field...
4
by: Dan Lewis | last post by:
I've imported a ms access database into a table in a mysql database. The access database contains a field that holds date/time values in 'general date' format. These all show up at 01/01/1970 in...
2
by: hph | last post by:
Please be gentle; I am a relative newbie for php/MySQL, and most of what I am doing is based on being self-taught using someone else's well-written code, and my own googling the answers to my...
11
by: walterbyrd | last post by:
My MySQL table has a field that is set as type "date." I need to get today's date, and insert it into that field. The default for that MySQL field is 2006-00-00. I know about the date()...
8
by: libsfan01 | last post by:
how can i use regular expressions to ensure a mysql format date entry in a text field? thanks marc
6
Atli
by: Atli | last post by:
This is an easy to digest 12 step guide on basics of using MySQL. It's a great refresher for those who need it and it work's great for first time MySQL users. Anyone should be able to get...
6
by: Geoff Cox | last post by:
Hello, at the moment I can add the combined date and time into MySQL using php $dt1 = date("Y-m-d H:i:s"); is it possible to add the date and time separately? I thought it might be
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.