php/mysql convert string date to date

[cjkeane]

hi everyone.

 

i have a database storing emails with the following fields:

date, subject. sender, format, toaddress.

 

the date field is a varchar datatype which takes dates in as 'dd mmm, time'. i'd like to convert that to dd-mmm using php when i display the data on a page. I'm not sure which function can convert varchar data to a date so any helpful hints you can provide would be appreciated. i'm using the following code to display the results of the query to the page:

 

whether i sort date ASC or date DESC, the order of the date column doesn't sort properly

 

<?php

$string = "SELECT id, date, substr(subject,1,20) as subject, sender, format, toaddress FROM email ORDER BY date ASC"; 
    $query = mysql_query($string) or die (mysql_error());
    $num_rows = mysql_num_rows($query);

if($num_rows>0)
{
$pages = new Paginator;
$pages->items_total = $num_rows;
$pages->paginate();
echo $pages->display_pages();
echo $pages->display_items_per_page();
echo '<hr>';
} else {
echo "<div id='titles_smaller'>No data available.</div><br />";
}
echo "<table class='sortable' width='100%' border='0' cellpadding='1'>";
        echo "<tr> <th nowrap>Date</th> <th nowrap>Subject (partial)</th><th nowrap>Sender</th><th nowrap>Format</th><th nowrap>To Address</th></tr>";
$string = $string."$pages->limit";
$query = mysql_query($string) or die (mysql_error());
$result = mysql_fetch_array($query);
if($result==true)
{ 
do 
{ 

echo "<tr>";
echo '<td nowrap>' . $result['date'] . '</td>';
echo '<td nowrap>' . $result['subject'] . '</td>';
echo '<td nowrap>' . $result['sender'] . '</td>';
echo '<td nowrap>' . $result['format'] . '</td>';
echo '<td nowrap>' . $result['toaddress'] . '</td>';
echo "</tr>"; 
    }
while($result = mysql_fetch_array($query));
}                
     // close table>
     echo "</table><hr>"; 
      
if($num_rows>0)
{
echo $pages->display_pages().$pages->display_items_per_page();
}
     // end pagination

?>

[cjkeane]

i duplicated the message by mistake instead of editing it.

[PFMaBiSmAd]

You cannot sort using your existing date format because in order to be able to sort something, you must be able to compare the values by magnitude. A mmm month abbreviation has no magnitude to compare and the fields making up the format must be arranged from left to right, highest significant part (year) to least significant part (day). This is why the YYYY-MM-DD format is used for the DATE data type in the database. It can be sorted because the fields making up the value meet the requirements needed to sort values.

 

Your first step to solving your problem would be to add a column with a DATE data type and copy/update your existing values into that column with the correct format (you can use the mysql STR_TO_DATE() function.) Once you do this, you can sort using dates and you can retrieve the values in any format that you want using the mysql DATE_FORMAT() function in your query.

[cjkeane]

i see the problem. thanks for the hint. i got the date sorting properly now.

 

i do have another issue though.

when data for the sender is imported, it goes in the db as john smith <johns@home.ca>

when the data is displayed on the page it just displays as john smith with no email address.

the datatype for the sender is varchar(200) latin1_swedish_ci.

do i need to use php to convert the 'sender' field to include htmlspecial chars?

i'm not quite sure which php function to use to display the entire contents of 'sender'. any ideas?