Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > PHP Databases
|
PHP Databases Using PHP in conjunction with databases. PHP questions not specific to databases should be directed to one of the other PHP forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the PHP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 25th, 2006, 03:18 AM
Friend of Wrox
 
Join Date: Mar 2005
Posts: 264
Thanks: 0
Thanked 0 Times in 0 Posts
Default problem displaying records in order of time ,date

Hi all.I use $Timestamp=date("g:i A l, F j Y."); to collect time and date the visitors entered my site . It inserts the data in to mysql database in this format :



3:02 AM Monday, July 24 2006





Code:
mysql_query("INSERT INTO logdisplay VALUES('$ID','$column1','$Timestamp','$column2','$columns3')");

 

 

$affected_rows = $sql->a_rows;




But when i query the database and use order by ,the data does not get displaied in order of newest on the top!!



Code:
select * from logdisplay order by Timestamp




could any one help me so that i get newst log records on the top .In another how to query my table baced on time an data.Thanks
 
Old July 25th, 2006, 07:19 PM
Friend of Wrox
 
Join Date: Jan 2006
Posts: 198
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The "desc" SQL keyword causes the query results to be sorted in descending order:

select * from logdisplay order by Timestamp desc

Jon Emerson
http://www.jonemerson.net/
 
Old July 25th, 2006, 07:28 PM
Friend of Wrox
 
Join Date: Mar 2005
Posts: 264
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by panacea
 The "desc" SQL keyword causes the query results to be sorted in descending order:

select * from logdisplay order by Timestamp desc

Jon Emerson
http://www.jonemerson.net/
i do not think that gona works since my the column that has both
date and time is stored as char like this :3:02 AM Monday, July 24 2006
 
Old July 25th, 2006, 08:16 PM
Friend of Wrox
 
Join Date: Jan 2006
Posts: 198
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Haha that's not exactly a great database design, is it? I'd strongly encourage you to improve it by using a timestamp or a date column instead. In fact, you can add one very simply by simply adding a new timestamp column at the end of your table. MySQL will automatically set this timestamp value to the current time every time the row is updated. Since this appears to be a log table, each row will only be written to once, and therefore the new column will be the insertion date.

If you're serious about keeping your current design, there's still hope for you :). You can use MySQL's STR_TO_DATE function to convert the date string to a proper timestamp inside your query. A rough undebugged example would be:

select * from logdisplay order by STR_TO_DATE(Timestamp, "%l:%i %p %W, %M %e %Y")

For more information on date format conversion in MySQL, go here: http://dev.mysql.com/doc/refman/5.0/...functions.html

If you're not using MySQL, other database servers should provide equivalent functions.


Jon Emerson
http://www.jonemerson.net/





Similar Threads
Thread Thread Starter Forum Replies Last Post
date-time problem naveed77 Beginning VB 6 0 February 1st, 2007 07:13 AM
Displaying time in chronological order mg1966 Classic ASP Databases 3 January 24th, 2005 02:11 PM
displaying date and time harpua Classic ASP Databases 2 October 29th, 2004 09:16 PM
Date And Time Problem tsukey PHP Databases 0 July 28th, 2004 10:44 PM
Problem displaying one of the records. mAdg3rr Classic ASP Databases 5 June 24th, 2003 08:56 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.