Trying to order by date

Nik has asked for the wisdom of the Perl Monks concerning the following question:

Hello i have this code to select my visitor records from the most current one to the first one and it works cause iam ordering by id(as ti happens) desc.

Problem is that some records dont appear in the correct places because those users have visited my webpage more than once and their record has just been updated in the position that there were. How can i correct this? I hope i gave you to understand what i am trying to do. Actually then i tried to cahnge the msyql column of date fro m text to datetime but what i get is 00-00-00 00:00:00. What i want to display is:

if( $name eq 'showlog' )
{
   $select = $dbh->prepare( "SELECT * FROM guestlog ORDER BY date DESC
+" );
   $select->execute;

   print br;

   print table( {class=>'info'} );
       while( $row = $select->fetchrow_hashref )
       {
           print Tr(
                     td( {width=>'35%',     class=>'aquamarine'},     
+$row->{host}    ),
                     td( {width=>'15%',     class=>'white'},          
+$row->{date}    ),
                     td( {width=>'45%',     class=>'cyan'},           
+$row->{passage} ),
                     td( {width=>'5%',      class=>'lime'},           
+$row->{counter} )
                 );
       }
   print '</table>';
[download]

but i cant get it working. its just dont ordering by right! iam gettign date as my $date = strftime( "%d %b, %H:%M", localtime );

Comment on Trying to order by date Download Code

Replies are listed 'Best First'.
Re: Trying to order by date by Joost (Canon) on Aug 04, 2005 at 10:37 UTC
the code looks good to me, but I guess you can't convert a text column to "datetime" automatically; i.e. you don't have valid dates in the date column. Also note that your $date format: a) won't sort right, b) has no year, so it will mess up after a year. It's much easier to just insert NOW() instead of creating your own representation of "right now". "What should it profit a man, if he should win a flame war, yet lose his cool?"	[reply]
Re: Trying to order by date by polettix (Vicar) on Aug 04, 2005 at 10:44 UTC
Of course, you understand that this has nothing to do with Perl :) i tried to cahnge the msyql column of date fro m text to datetime but what i get is 00-00-00 00:00:00 If you have to handle a datetime, use a datetime and not a text. There are two considerations about this: first of all, you don't need to get your time from Perl here. You can use the `NOW()` function in MySQL to get the current time; if the database insists on telling you "00-00-00 00:00:00", why don't you stick to the same format? It seems that it's expecting something like "YY-MM-DD hh:mm:ss", so your date from Perl should be something like `my $date = strftime("%y-%m-%d %H:%M:%S", localtime);`. You'll surely want your script to work in 2105 too, so you'd better use four-digit years via `%Y` instead of `%y` :) If you fix the dates in your guestlog table you'll obtain what you're looking for, the query is right. Flavio perl -ple'$_=reverse' <<<ti.xittelop@oivalf Don't fool yourself.	[reply] [d/l] [select]
Re^2: Trying to order by date by Nik (Initiate) on Aug 04, 2005 at 11:17 UTC
Year is not a problem and actually i dotn wanty it: So the solutuon is to change this my $date = strftime( '%F %T', localtime ); to this? my $date = now(); and then insert it to the mysql database? But then agian when i get th date column form the mysq table i want to print it in this format my $date = strftime( "%d %b, %H:%M", localtime );	[reply]
Re^3: Trying to order by date by Joost (Canon) on Aug 04, 2005 at 11:42 UTC
No, you just want something like the following SQL: "UPDATE sometable SET datecolumn = NOW() where ID=..." There is no need to generate the date in perl. Update: by the way, YOU might not be interested in the year (as in, you might not want to show it) but the database can't sort the dates correctly if you don't supply a year: jan 2004 IS later than dec 2003, you see. Update2: if you have a datetime column, mysql can take care of the output formatting too. You desperately need to take a look at the mysql date and time functions instead of guessing. "What should it profit a man, if he should win a flame war, yet lose his cool?"	[reply]
A reply falls below the community's threshold of quality. You may see it by logging in.
Re: Trying to order by date by nedals (Deacon) on Aug 04, 2005 at 15:39 UTC
.... but what i get is 00-00-00 00:00:00. This indicates that you are not writing to the database correctly. To fix that problem, use something similar to... `UPDATE guestlog SET date=NOW() where ID=...` [download] as Joost suggested. To format the date for display, use DATE_FORMAT(col,format_str) in your SQL query `SELECT cola, colb, DATE_FORMAT(date,'%d, %H:%M') FROM guestlog ORDER B +Y date DESC` [download]	[reply] [d/l] [select]
Re: Trying to order by date by TedPride (Priest) on Aug 04, 2005 at 11:30 UTC
It's probably easiest to use an UNSIGNED INT field and just store a unix timestamp. I believe mySQL has a UNIX_TIMESTAMP() function, or you can store the current time() from Perl.	[reply]