SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 28
  1. #1
    SitePoint Enthusiast glsbrakes's Avatar
    Join Date
    Nov 2007
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Convert date to datetime and output date_format

    Well, I am attempting to update a error I receive with a date in a mysql table.
    The table is and I am told to switch to datetime. The error I keep getting is the table does not index the logs and the select grabs a date from one log and a time from another.
    --
    -- Table structure for table `log_login`
    --

    CREATE TABLE `log_login` (
    `username` varchar(20) default NULL,
    `date` varchar(30) default NULL,
    `time` varchar(30) default NULL,
    `ip_addr` varchar(20) default NULL,
    `oper_sys` varchar(20) default NULL,
    `brow` varchar(20) default NULL,
    KEY `date` (`date`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    and the function.php to insert into is - in part

    //sets date and time variables
    $last = gmdate("m-d-Y", time() + $zone);
    $time = gmdate("g:i:a", time() + $zone);

    //make the connection to the database
    $connection = @mysql_connect($server, $dbusername, $dbpassword) or die(mysql_error());
    $db = @mysql_select_db($db_name,$connection)or die(mysql_error());

    //build and issue the query
    $sql ="INSERT INTO log_login VALUES
    ('$_SESSION[user_name]', '$last', '$time', '$ip', '$platform', '$browser')";
    $result = @mysql_query($sql,$connection) or die(mysql_error());
    and the select

    <?$connection = @mysql_connect($server, $dbusername, $dbpassword)
    or die(mysql_error());

    $db = @mysql_select_db($db_name,$connection)
    or die(mysql_error());

    $sql="SELECT * FROM log_login WHERE username = '$_SESSION[user_name]' GROUP BY time ORDER BY date DESC LIMIT 1,1";
    $result = @mysql_query($sql, $connection) or die(mysql_error());

    while ($sql = mysql_fetch_object($result))
    {
    $whent = $sql -> date;

    echo "<font size=\"2\" face=\"Tahoma\"><b>Date: </b>$whent</font><br>";
    }
    ?>
    </p>

    <p align=center style='text-align:center'>
    <?$connection = @mysql_connect($server, $dbusername, $dbpassword)
    or die(mysql_error());

    $db = @mysql_select_db($db_name,$connection)
    or die(mysql_error());

    $sql="SELECT * FROM log_login WHERE username = '$_SESSION[user_name]' GROUP BY time ORDER BY date DESC LIMIT 1,1";
    $result = @mysql_query($sql, $connection) or die(mysql_error());

    while ($sql = mysql_fetch_object($result))
    {
    $whent = $sql -> time;

    echo "<font size=\"2\" face=\"Tahoma\"><b>Time: </b>$whent</font><br>";
    }
    ?>
    </p>
    Every time I mess with this code I just make things, well, more complicated then need be. Right?

  2. #2
    SitePoint Enthusiast glsbrakes's Avatar
    Join Date
    Nov 2007
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    http://www.glsbrakes.com/GLS_Login/login

    username php
    password coders

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    in addition to the very poor design of using VARCHAR for dates and times, the problem with your SELECT results is caused by your GROUP BY

    could you explain, without code, what you're trying to do?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    p.s. you ~really~ don't want to publish passwords where a bazillion people, some of whom are not nice at all, can see it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast glsbrakes's Avatar
    Join Date
    Nov 2007
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The password is to a test page? I thought that would be pretty safe.

    I would like to insert into a log, a date and time, when a person logs in. Then list the last login date and time they logged in.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    by "last login" you imply a sequencing of data values, yes?

    you are hamstringing yourself by storing a varchar, especially in view of the fact that 11/25/2009 comes after 12/25/2008

    also, you shouldn't use separate date and time columns, that makes for much more complex SQL

    please, if you are able, redefine your table and use DATETIME
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    weird, for some reason i am unable to edit my last post

    i meant to say that 12/25/2008 comes after 12/24/2009

    VARCHAR dates only work if they are in major-to-minor sequence

    DATETIME columns are much better
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast glsbrakes's Avatar
    Join Date
    Nov 2007
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I did reset once, date to datetime in the type field and all of the data goes to 0000-00-00 00:00:00 and all logins are the same.

  9. #9
    SitePoint Enthusiast glsbrakes's Avatar
    Join Date
    Nov 2007
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So my gmdate() need to be changed to ??? datetime("Y-m-d\TH:i:sP", time() + $zone);

  10. #10
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Off Topic:


    Quote Originally Posted by r937 View Post
    weird, for some reason i am unable to edit my last post
    This has been happening to me sporadically starting today. My edit button either is not there, or sometimes the submit button doesn't work in the edit pane.

  11. #11
    SitePoint Enthusiast glsbrakes's Avatar
    Join Date
    Nov 2007
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok I do get, 2009-08-04 05:49:47 with date("Y-m-d\TH:i:sP", time() + $zone);

  12. #12
    SitePoint Enthusiast glsbrakes's Avatar
    Join Date
    Nov 2007
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Work time zone, off by 5 hours

  13. #13
    SitePoint Enthusiast glsbrakes's Avatar
    Join Date
    Nov 2007
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I indexed the date column and changed the date to date("Y-m-d\TH:i:sP");
    But the table looks like this.

    jim 2009-08-04 11:31:20 1:31m
    jim 2009-08-04 11:31:30 1:31m
    jim 2009-08-04 11:33:17 1:33m
    jim 2009-08-04 11:32:22 1:32m
    php 2009-08-04 11:31:38 1:31m
    php 2009-08-04 11:30:55 1:30m
    jim 2009-08-04 11:31:03 1:31m
    php 2009-08-04 11:29:51 1:29m
    php 2009-08-04 11:26:38 1:26m
    php 0000-00-00 00:00:00 1:16m
    php 0000-00-00 00:00:00 11:53:am
    php 0000-00-00 00:00:00 12:07m
    php 0000-00-00 00:00:00 12:26m
    php 2009-08-04 05:49:47 12:49m
    php 2009-08-04 10:56:58 12:56m
    php 0000-00-00 00:00:00 12:58m
    php 2009-08-04 10:59:34 12:59m
    php 2009-08-04 11:05:18 1:05m
    php 2009-08-04 11:07:15 1:07m
    php 2009-08-04 11:10:20 1:10m
    php 2009-08-04 11:11:17 1:11m

  14. #14
    SitePoint Enthusiast glsbrakes's Avatar
    Join Date
    Nov 2007
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The time is behind 2 hours,
    jim 2009-08-04 11:31:30 1:31pm the 1:31pm is from the time column.

  15. #15
    SitePoint Enthusiast glsbrakes's Avatar
    Join Date
    Nov 2007
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So, I need to add 2 hours from my server. Which is in LA and I and most of my customer base is in Central time zone.

  16. #16
    SitePoint Enthusiast glsbrakes's Avatar
    Join Date
    Nov 2007
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool

    This is what I have so, far.

    php 2009-08-04 16:22:20 for 8-04-2009 4:22pm central time, this is correct. Hay Hay!!!!

    I adjusted the config.php file to... $zone=3600*+2; from -5

    and

    I adjusted the login file

    //sets date and time variables
    $last = date("Y-m-d\TH:i:sP", time() + $zone);
    $time = date("g:i:a", time() + $zone); //for looks and it may be used later?

    Now, If you feel that looks right and the output is the only thing I need to adjust from
    <?$connection = @mysql_connect($server, $dbusername, $dbpassword)
    or die(mysql_error());

    $db = @mysql_select_db($db_name,$connection)
    or die(mysql_error());

    $sql="SELECT * FROM log_login WHERE username = '$_SESSION[user_name]' GROUP BY time ORDER BY date DESC LIMIT 1,1";
    $result = @mysql_query($sql, $connection) or die(mysql_error());

    while ($sql = mysql_fetch_object($result))
    {
    $whent = $sql -> date;

    echo "<font size=\"2\" face=\"Tahoma\"><b>Date: </b>$whent</font><br>";
    }
    ?>
    </p>

    <p align=center style='text-align:center'>
    <?$connection = @mysql_connect($server, $dbusername, $dbpassword)
    or die(mysql_error());

    $db = @mysql_select_db($db_name,$connection)
    or die(mysql_error());

    $sql="SELECT * FROM log_login WHERE username = '$_SESSION[user_name]' GROUP BY time ORDER BY date DESC LIMIT 1,1";
    $result = @mysql_query($sql, $connection) or die(mysql_error());

    while ($sql = mysql_fetch_object($result))
    {
    $whent = $sql -> time;

    echo "<font size=\"2\" face=\"Tahoma\"><b>Time: </b>$whent</font><br>";
    }
    ?>
    </p>
    Any suggestions or Ideas. One Select from the datetime to ?? date_format, which I have never worked with before.

  17. #17
    SitePoint Enthusiast glsbrakes's Avatar
    Join Date
    Nov 2007
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    Ok, I changed GROUP to ORDER and this does retrieve the last login, pretty sure.
    Now all I need is to format the
    2009-08-04 16:22:20 in the mysql database

    To:

    Date 8-4-2009
    Time 4:22pm

    <?$connection = @mysql_connect($server, $dbusername, $dbpassword)
    or die(mysql_error());

    $db = @mysql_select_db($db_name,$connection)
    or die(mysql_error());

    $sql="SELECT * FROM log_login WHERE username = '$_SESSION[user_name]' ORDER BY date DESC LIMIT 1,1";
    $result = @mysql_query($sql, $connection) or die(mysql_error());

    while ($sql = mysql_fetch_object($result))
    {
    $whent = $sql -> date;

    echo "<font size=\"2\" face=\"Tahoma\"><b>Date: </b>$whent</font><br>";
    }
    ?>
    </p>
    HELP !

  18. #18
    SitePoint Enthusiast glsbrakes's Avatar
    Join Date
    Nov 2007
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have been working on a datetime in a mysql table and date_format to m-d-Y
    but I just can not get the syntax right.

    From

    echo "<font size=\"2\" face=\"Tahoma\"><b>Time: </b>$whent</font><br>";
    TO:

    echo "<font size=\"2\" face=\"Tahoma\"><b>Date: </b>date_format($whent, 1), "m-d-Y\TH:i:sP")</font><br>";
    What I'm I doing wrong?

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by glsbrakes View Post
    What I'm I doing wrong?
    trying to use a mysql function in php -- that's what you're doing wrong
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Enthusiast glsbrakes's Avatar
    Join Date
    Nov 2007
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I went to php.net and under all of the date_format I did not see anything of function or did I missed something?

    the date_format is the way to go for echo.

    Not really a full time coder, more like a whan a be. I read through some of php.net for code and some from dev.mysql.com. Not sure way I keep syntaxing out expecting ''," or ";" somewhere.

    Not sure.

  21. #21
    SitePoint Enthusiast glsbrakes's Avatar
    Join Date
    Nov 2007
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you are saying date_format is a mysql function... derrrrr. I see

    did not know there was a difference.

  22. #22
    SitePoint Enthusiast glsbrakes's Avatar
    Join Date
    Nov 2007
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, this should work, but it does not.

    echo "<font size=\"2\" face=\"Tahoma\"><b>Date: </b>$whent->format("d-m-Y H:i:s")</font><br>";
    I receive a error of

    Parse error: syntax error, unexpected T_STRING, expecting ',' or ';' in /home/content/p/o/w/powellsite/html/glsbrakes.com/testpage.php on line 48

  23. #23
    SitePoint Enthusiast glsbrakes's Avatar
    Join Date
    Nov 2007
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Give up. I posted on ScriptLance.com, This is driving me crazy.

  24. #24
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by glsbrakes View Post
    date("Y-m-d\TH:i:sP", time() + $zone);
    1) why do you have a tab between the date and time? A space is sufficient.

    2) Mixing and matching timezone information is going to lead to confusion. Typically, I store all of my datetimes in server time or GMT (depending on the app) since that will be consistent. When I access the data, I then adjust for the client's timezone if needed. In your case, I would drop P from the date format and drop the + $zone also. When you select the date, that's when you use the $zone adjustment (before displaying).

    Summed up, I would switch that to
    PHP Code:
    date('Y-m-d H:i:s'time()); 
    MySQL v5.1.58
    PHP v5.3.6

  25. #25
    SitePoint Enthusiast glsbrakes's Avatar
    Join Date
    Nov 2007
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Get er done....

    I finally am happy with the code I ended up with. Thanks for the help and suggestions.
    I ended up with,

    <?$connection = @mysql_connect($server, $dbusername, $dbpassword)
    or die(mysql_error());

    $db = @mysql_select_db($db_name,$connection)
    or die(mysql_error());

    $sql="SELECT * FROM log_login WHERE username = '$_SESSION[user_name]' ORDER BY date DESC LIMIT 1,1";
    $result = @mysql_query($sql, $connection) or die(mysql_error());

    while ($sql = mysql_fetch_object($result))
    {
    $whent = $sql -> date;

    $TheDate = "Date: " . date ("n-j-y", strtotime ($whent));
    $TheTime = "Time: " . date ("g:i A", strtotime ($whent));

    echo "<font size=\"2\" face=\"Tahoma\"><b>$TheDate </b></br> &nbsp;&nbsp;&nbsp; <b>$TheTime &nbsp;&nbsp;&nbsp; </font><br>";
    }
    ?>
    and I did get some help from another website too.....
    Dave


Bookmarks

Posting Permissions

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