Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2005
    Posts
    203

    Talking Unanswered: problem displaying records in order of time and date in php

    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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what did you actually expect the db to do? on the face of it the db is returning the dfata correctly, its your data that is not being stored in the manner in which you understand it to be.

    store the data in the appropriate date type in the db, use a formatting rule to translate that value into something you recognise (either as part of the SQL select or the front end).

    what datatype are you using for your column "timestamp". btw I wouldn't use the reserved word timestamp for a column name within a db, its asking for trouble. first off store date / time values as DATETIME or TIMESTAMP

    at what point do you want to capture this value - is it defined outside the system (ie triggered by something outside the db) or is it a simple date of occurance.

    the problem of using a timestamp datatype is that each time you change the record the timestamp will change. carefull physical design can get round that, but it isn't always the most appropriate solution - its best when used in audit logs and the like.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2005
    Posts
    203
    well could u just show me the insert statments that stores time and date in away that it can be quried and sorted by date and time. I am just trying to keep records of time and date that people visit my website. So my insert statments is triggred when a new visitors comes. i be happy if u show me the insert statemens and data type. i want to output the logs in order of time of visit and date .Thanks

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you never change the data in the row, then consider using the TIMESTAMP datatype
    if you allow changes to your data then consider usign the the DATETIME datatype.
    I'm guessing at present you are using the CHAR datatype, which is inappropriate for date values if you want to sort on that value
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2005
    Posts
    203
    could u just show me how to store date and time and also how to query them so we get output in order of data and time. Thanks

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you use the TIMESTAMP datatype then you do not need to do anything more, every time the row changes MySQL automatically updtes the column defined as TIMESTAMP with the date and time that the event occured.

    if you need to store a date and time triggered by somehting else (say the date and time a customer entereed your shop) then consider using a DATETIME datatype.

    MySQL stores date/time values as "YYYY/MM/DD hh:mm:ss"

    I can't tell yoiu how you want to stroe the informtion, b ecause I don't know your applciation. I think you probabkly want to use the TIMESATMP datatype, however that will only work if you never ever change any value in any column in the row containing this information. Thats why I provided the alternative.

    if you need to use the alterantive then when you attempt to write the value to that column then format the data in your PHP script to something like
    date("Y m d H G i s")

    when you extract the value form MySQL then consider either formatting within a PHP code block, or using the MySQL function
    Code:
    DATE_FORMAT(<myDateTimeColumn>,"%r %W, %M %e %Y")
    As a general rule I'd do it within your PHP code, rather than your SQL just in case you decide to switch SQL backend, but as ever tis up to you.

    I'd suggest you read the appropriate section(s) in the MySQL manual to fully understand the differences between the data types, and how MySQL handles/stores dates and what functions you can use to manipulate dates
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •