Date formatting

**bonsai99** · Aug 16, 2009 15:24

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.

**eruna** · Aug 16, 2009 18:03

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?

**bonsai99** · Aug 16, 2009 23:53

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

**bonsai99** · Aug 17, 2009 00:54

Actually, sorry - that's still with the PHP to echo. How do I do it without the PHP?

**felgall** · Aug 17, 2009 01:55

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.

**bonsai99** · Aug 17, 2009 05:16

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']); ?>

**r937** · Aug 17, 2009 06:37

what happens if you try

Code:

<?php echo($query_dateformat); ?>

disclaimer: i don't do php

**bonsai99** · Aug 17, 2009 06:51

OK - that outputs :

SELECT date_format(DateToContact, '%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);
?>

**r937** · Aug 17, 2009 06:57

Originally Posted by duklaprague

Is that WHERE ContactID = 1 an issue...

only if you wanted more than that one employee

**bonsai99** · Aug 17, 2009 07:26

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.....

**eruna** · Aug 17, 2009 07:30

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

**bonsai99** · Aug 17, 2009 07:37

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?

**eruna** · Aug 17, 2009 19:36

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.

**bonsai99** · Aug 18, 2009 01:50

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.

**felgall** · Aug 18, 2009 02:39

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.

**bonsai99** · Aug 18, 2009 03:14

Got it - just needed to change it to :

date_format(DateToContact, '%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.

User Tag List

Thread: Date formatting

Thread Tools

Display

Date formatting

Bookmarks

Bookmarks

Posting Permissions