SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question PHP/mySQL DATETIME Issue

    Okay so say I have to insert the current date and time into the mysql database, I want it to be formatted differently than I did in my below code:

    Code:
    PHP Code:
    $sql "INSERT INTO $table_name (dateuploaded) VALUES (NOW() )"//stores records in database 
    So I tired date format and every way I could think of and it still inserted the generic string into the database no matter what. I wanted to format it as:

    Code:
    PHP Code:
    %%%%%
    This way it shows 12 hour time with pm/am and the day of week etc.. like Thursday June 9th 2009, 6:23 PM

    Can anyone help me with this? Thanks in advance.

  2. #2
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT date_format(dateuploaded,'%W %M %e %Y %r'); does the thing?

  3. #3
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am using DATETIME in mysql field setting. Was that what you were looking for?

    Format: Thursday June 9th 2009, 6:23 PM

    Thanks

  4. #4
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    K, I'll try that right now

  5. #5
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not quite sure how to make that
    PHP Code:
    SELECT date_format(dateuploaded,'%W %M %e %Y %r'); 
    work with my current setup:

    Upload Part of Script to insert:

    PHP Code:
     $sql "INSERT INTO $table_name (dateuploaded) VALUES (NOW() )"
    View part of script to list records:

    PHP Code:
    $list_files .= "<tr><td>". [COLOR="Red"]$row['dateuploaded'][/COLOR] ."</td></tr>"

  6. #6
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To retrieve data from table, you must use SELECT query. Not INSERT

    In that SELECT query you must use date_format()
    for example
    SELECT date_format(dateuploaded,'&#37;W %M %e %Y %r') as dateuploaded_f, * FROM $table_name

    and then use $row['dateuploaded_f'] in your script

  7. #7
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, okay; this is the solution to my problem, thanks.

    Have a great day

  8. #8
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wait after more testing that solution doesn't work with the all asterik, or without it; it says,
    PHP Code:
    You have an error in your SQL syntaxcheck the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM uploaddatatable' at line 
    And if you use none it won't return the name and stuff, just the formatted date.

    For example the changed code tested for the $result:

    PHP Code:
    $result mysql_query("SELECT date_format(dateuploaded,'%W %M %e %Y %r') as dateuploaded_f, * FROM uploaddatatable") or die(mysql_error());  
        while (
    $row mysql_fetch_array($result)) {
            
    $id $row['id'];         
    $url_img $row['file'];
            
    $namfile basename($url_img);
            
    $list_files .= "<tr><td>"$row['id']  ."</td><td>"$row['firstname'] ."</td><td>"$row['lastname'] ."</td><td>"$row['title'] ."</td><td>"$row['dateuploaded_f'] ."</td><td>"$row['downloaded'] ."</td><td>"$row['dodelete'] ."</td><td><a href='"$row['file'] ."' class='null' target='_blank'>$namfile</a> <a href='print.php?id=$id&url_img=$url_img'><img class='png' src='css/cancel.png' alt='Delete' title='Delete' /></a></td></tr>"

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    replace the asterisk with the list of the columns that you actually want
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah, by bad.
    placing asterisk first
    PHP Code:
    SELECT *, date_format(dateuploaded,'%W %M %e %Y %r') as dateuploaded_f 
    will be ok

  11. #11
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Awesome that worked with the asterik infront

    Thanks again for the help all.


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
  •