SitePoint Sponsor

User Tag List

Results 1 to 16 of 16

Thread: Date formatting

  1. #1
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Date formatting

    Hope someone can help with this.

    I have code I use that reformats dates from YYYY-MM-DD to something more user friendly, eg :

    Code:
    <?php
    $colname_dateformat = "1"; 
    if (isset($_GET['ContactID'])) { 
      $colname_dateformat = (get_magic_quotes_gpc()) ? $_GET['ContactID'] : addslashes($_GET['ContactID']); 
    } 
    mysql_select_db($database_connSearchTechUK, $connSearchTechUK); 
    $query_dateformat = sprintf("SELECT date_format(DateToContact, '&#37;%D %%b %%Y') as formatted_date FROM EmployerContacts WHERE ContactID = %s", $colname_dateformat);
    $dateformat = mysql_query($query_dateformat, $connSearchTechUK) or die(mysql_error()); 
    $row_dateformat = mysql_fetch_assoc($dateformat); 
    $totalRows_dateformat = mysql_num_rows($dateformat);
    ?>
    ..and then...

    Code:
    <?php echo($row_dateformat['formatted_date']); ?>
    To display the formatted date.

    This seems to work OK on a details page, but I've been trying it on page with a repeat region listing several records, and it just seems to draw a blank.

    Any suggestions what's going wrong here?

    Cheers.

  2. #2
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    971
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Can you show an echo of the actual queries being run (just mysql with no php) to verify that the id is getting passed correctly?

  3. #3
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yup - my repeat region code of :

    Code:
    <?php if ($totalRows_WADAEmployerContacts > 0) { // Show if recordset not empty ?>
         
             <div id="candidatelinks">
            <table border="0" cellpadding="0" cellspacing="0" width="100&#37;">
              <tr>
                <td class="labelcell" width="6%">ID</td>
                <td class="labelcell" width="27%">Name</td>
                <td class="labelcell" width="27%">Employer</td>
                <td class="labelcell" width="18%">Tel</td>
                <td class="labelcell" width="18%">Call Back</td>
              </tr>
              <?php do { ?>
                <tr class="<?php echo $WARRT_AltClass1->getClass(true); ?>">
                  <td class="fieldcell"><?php echo($row_WADAEmployerContacts['ContactID']); ?></td>
                  <td class="fieldcell"><a href="contactDetails.php?ContactID=<?php echo(rawurlencode($row_WADAEmployerContacts['ContactID'])); ?>" title="View Details"><?php echo($row_WADAEmployerContacts['FirstName']); ?>&nbsp;<?php echo($row_WADAEmployerContacts['LastName']); ?></a></td>
                  <td class="fieldcell"><?php echo($row_WADAEmployerContacts['Employer']); ?></td>
                  <td class="fieldcell"><?php echo($row_WADAEmployerContacts['Telephone']); ?></td>
                  <td class="fieldcell"><?php echo($row_WADAEmployerContacts['DateToContact']); ?></td>
                </tr>
                <?php } while ($row_WADAEmployerContacts = mysql_fetch_assoc($WADAEmployerContacts)); ?>
                
            </table>
    Correctly displays, eg

    311, Jo Bloggs, Aardvark Airlines, 020 1234 5678, 2009-08-16

    But if I change it to the formatted date, it just displays :

    311, Jo Bloggs, Aardvark Airlines, 020 1234 5678

  4. #4
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually, sorry - that's still with the PHP to echo. How do I do it without the PHP?

  5. #5
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,875
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    There was an earlier suggestion to echo the SQL so that you can see what the database call looks like with the values substituted into it. Have you tried that yet? Generally I find that when SQL doesn't work as expected it is because the SQL call isn't being correctly constructed and echoing it before running it usually makes the problem obvious.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  6. #6
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Stephen - can you let me know how to do that? I'm from a design rather than coding background, so my coding knowledge is more limited - I get by mainly with phpMyAdmin for setting up databases, and Dreamweaver to help with the construction of the web pages.

    So I just know how to echo something with PHP, eg :

    <?php echo($row_WADAEmployerContacts['DateToContact']); ?>

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    what happens if you try
    Code:
    <?php echo($query_dateformat); ?>
    disclaimer: i don't do php
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK - that outputs :

    SELECT date_format(DateToContact, '&#37;D %b %Y') as formatted_date FROM EmployerContacts WHERE ContactID = 1

    Is that WHERE ContactID = 1 an issue, or is that just pulling the first ContactID as an example?

    Actually - I think I know what the problem is, if not the solution - the code I'm using is from a Contact Details page, which GETs the contact ID from a Contact Results page.

    So if I guess the question is how do I get the formatted date to display on the resuts page, rather than the details page?

    My query looks like :

    Code:
    mysql_select_db($database_connSearchTechUK, $connSearchTechUK);
    $query_WADAEmployerContacts = "SELECT * FROM EmployerContacts LEFT JOIN Employers ON (EmployerContacts.EmployerID = Employers.EmployerID) WHERE DateToContact <= current_Date() AND DateToContact > 0000-00-00 AND CalledBack='N' ORDER BY DateToContact DESC";
    $query_limit_WADAEmployerContacts = sprintf("%s LIMIT %d, %d", $query_WADAEmployerContacts, $startRow_WADAEmployerContacts, $maxRows_WADAEmployerContacts);
    $WADAEmployerContacts = mysql_query($query_limit_WADAEmployerContacts, $connSearchTechUK) or die(mysql_error());
    $row_WADAEmployerContacts = mysql_fetch_assoc($WADAEmployerContacts);
    
    if (isset($_GET['totalRows_WADAEmployerContacts'])) {
      $totalRows_WADAEmployerContacts = $_GET['totalRows_WADAEmployerContacts'];
    } else {
      $all_WADAEmployerContacts = mysql_query($query_WADAEmployerContacts);
      $totalRows_WADAEmployerContacts = mysql_num_rows($all_WADAEmployerContacts);
    }
    $totalPages_WADAEmployerContacts = ceil($totalRows_WADAEmployerContacts/$maxRows_WADAEmployerContacts)-1;
    But my current formatted date code must be wrong :

    Code:
    <?php
    $colname_dateformat = "1"; 
    if (isset($_GET['ContactID'])) { 
      $colname_dateformat = (get_magic_quotes_gpc()) ? $_GET['ContactID'] : addslashes($_GET['ContactID']); 
    } 
    mysql_select_db($database_connSearchTechUK, $connSearchTechUK); 
    $query_dateformat = sprintf("SELECT date_format(DateToContact, '%%D %%b %%Y') as formatted_date FROM EmployerContacts WHERE ContactID = %s", $colname_dateformat);
    $dateformat = mysql_query($query_dateformat, $connSearchTechUK) or die(mysql_error()); 
    $row_dateformat = mysql_fetch_assoc($dateformat); 
    $totalRows_dateformat = mysql_num_rows($dateformat);
    ?>

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by duklaprague View Post
    Is that WHERE ContactID = 1 an issue...
    only if you wanted more than that one employee
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The actual code of the query is :

    Code:
    SELECT date_format(DateToContact, '&#37;%D %%b %%Y') as formatted_date, ContactID FROM EmployerContacts WHERE ContactID = %s
    Which I assume is doing something in reference to the other instance I was doing this, ie pulling through the contact details on a details page from a results page.

    I basically have a list of Contacts on a page, using a repeat region,

    So like :

    ContactID, Contact, Employer, Phone Number, Date to Contact
    1, Bob Smith, Aardvark Airlines, 020 1234 5678, 2009-09-01
    2, Jane Smith, Kangaroo Airlines, 020 8765 4321, 2009-10-01

    So all I need to somehow do is reformat the date in those listings to 01 Sep 2009, 01 Oct 2009 etc.....

  11. #11
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    971
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    duklaprague,
    From what you are describing it doesn't sound like the date formatting is the issue. Because if the same formatting code works on one page, it will work on another.

    Here are a few things to check:

    Is the id correct in the query every time?
    Are you returning data? In other words, does it work without the formatting.
    Do you have variables overwriting each other?

    E

  12. #12
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yup - the ID correctly displays, and the unformatted date displays OK as part of the main query, as per my original post above.

    In laymans terms I'm now wondering if the code I'm using grabs the ContactID from the clicked record of a results page, and stores that to match the ContactID of the current contact on a details page.

    But needs to be different to display the formatted_date of each Contact on a results page.

    If you see what I mean?

  13. #13
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    971
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    It looks like you are querying all the rows and then only querying the date. Try incorporating the formatting syntax in your original query calling each row you need explicitly instead of the wild card. It sounds like your variables are getting muddled.

  14. #14
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sounds like a scheme - although I'm not sure of the exact syntax for that.

    I've tried :

    Code:
    "SELECT ContactID, FirstName, LastName, Employer, Telephone, date_format(DateToContact, '&#37;%D %%b %%Y') as formatted_date FROM EmployerContacts LEFT JOIN Employers ON (EmployerContacts.EmployerID = Employers.EmployerID) WHERE DateToContact <= current_Date() AND DateToContact > 0000-00-00 AND CalledBack='N' ORDER BY DateToContact DESC"
    but with that

    Code:
    <?php echo($row_WADAEmployerContacts['formatted_date']); ?>
    is outputting :

    %D %b %Y

    for each record.

    Also tried :

    Code:
    "SELECT ContactID, FirstName, LastName, Employer, Telephone, date_format(DateToContact, '%%D %%b %%Y') FROM EmployerContacts etc"
    and

    Code:
    <?php echo($row_WADAEmployerContacts['DateToContact']); ?>
    but that's just drawing a blank again.

  15. #15
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,875
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Have you tried typing the query into the SQL screen of phpmyadmin to check just exactly what is returned from the query. If it is returning the correct valuethere then that proves that the problem is elsewhere in the code.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  16. #16
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Got it - just needed to change it to :

    date_format(DateToContact, '&#37;D %b %Y')

    in the first example above.

    Strange that it was fine in places I've used that code before on details pages though.


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
  •