Showing "Today" "Yesterday" working dates

[scrupul0us]

I've got a date field in mysql... i call it to my script using:

echo date('m/d/Y @ g:i:s A',strtotime($row["newsDATE"]));

where $row["newsDATE"] is the untouched mysql time stamp... what I'd like todo is be able to say:

Today @ Time
Yesterday @ Time
2 Days Ago @ Time
Date @ Time

as my posts age... i did some googling but havent found much... thank in advance!

[Installer]

Something like this?

$query = "SELECT DATEDIFF(CURDATE(), newsDATE) AS news_date_diff, " .
         "DATE_FORMAT(newsDATE, '%l:%i:%s') AS news_time FROM my_table";
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
$date_diff = $row['news_date_diff'];
if ($date_diff == 0) {
    $str = 'Today';
} elseif ($date_diff == 1) {
    $str = 'Yesterday';
} else {
    $str = $date_diff . 'Days Ago';
}
$str .= ' @ ' . $row['news_time'];

(Untested)

[scrupul0us]

i guess i should have posted the entire script

				$query = "select * from news order by newsDATE desc"; 
				$result = mysql_query($query);
				$rowcount=mysql_num_rows($result);

			if(mysql_num_rows($result)>0)				
			{	
				$x = 0;
				while ($row = mysql_fetch_assoc($result))
				{
					$x++;

					?>
					<div id="containers_<?php echo $row["newsID"]; ?>" class="content_containers" style="width: 532px;">
						<div id="headers_<?php echo $row["newsID"]; ?>" class="content_headers">
						<img 
							src="images/item_min.gif" 
							style="float:right" 
							onClick="toggle('hide','bodies_<?php echo $row["newsID"];?>')"/>
						<img 
							src="images/item_max.gif" 
							style="float:right" 
							onClick="toggle('show','bodies_<?php echo $row["newsID"];?>')"/>
						<img 
							src="images/header_arrow.gif" 
							style="float:left;"/><?php echo $row["newsSUBJECT"]; ?>
						</div>
						<div id="bodies_<?php echo $row["newsID"]; ?>" class="content_bodies">

						<table>
						<tr valign="middle">
						<td><img src="images/icons/announce.gif"/></td>
						<td>
						<b>Posted By: </b><?php echo $row["newsWHO"];?><br/>
						<b>When: </b><?php echo date('m/d/Y @ g:i:s A',strtotime($row["newsDATE"]));?><br/>
						<b>Subject: </b><?php echo $row["newsSUBJECT"];?>
						</td>
						</tr>
						<tr>
						<td></td>
						<td><?php echo $row["newsITEM"] ?></td>
						</tr>
						</table>

						</div>
					</div>
					<?php
					if($rowcount !== $x)
					{echo "<br/>";}					
				}						
			}

			else
			{
			echo "No News or DB is Down";
			}

as in not just querying the date

[Installer]

That's fine, as in just add the datediff and date_format terms to your query.

[scrupul0us]

good form Installer:

final code:

				<?php
				$query = "select DATEDIFF(CURDATE(), newsDATE) AS news_date_diff, DATE_FORMAT(newsDATE, '%l:%i:%s %p') AS news_time, newsID,newsSUBJECT, newsWHO, newsITEM from news order by newsDATE desc";

			$result = mysql_query($query);
			$rowcount=mysql_num_rows($result);

			if(mysql_num_rows($result)>0)				
			{	
				$x = 0;
				while ($row = mysql_fetch_assoc($result))
				{
					$x++;
					$date_diff = $row['news_date_diff'];
					if ($date_diff == 0) {
						$date_str = 'Today';
					} elseif ($date_diff == 1) {
						$date_str = 'Yesterday';
					} else {
						$date_str = $date_diff . ' Days Ago';
					}
					$date_str .= ' @ ' . $row['news_time']; 

					?>
					<div id="containers_<?php echo $row["newsID"]; ?>" class="content_containers" style="width: 532px;">
						<div id="headers_<?php echo $row["newsID"]; ?>" class="content_headers">
						<img 
							src="images/item_min.gif" 
							style="float:right" 
							onClick="toggle('hide','bodies_<?php echo $row["newsID"];?>')"/>
						<img 
							src="images/item_max.gif" 
							style="float:right" 
							onClick="toggle('show','bodies_<?php echo $row["newsID"];?>')"/>
						<img 
							src="images/header_arrow.gif" 
							style="float:left;"/><?php echo $row["newsSUBJECT"]; ?>
						</div>
						<div id="bodies_<?php echo $row["newsID"]; ?>" class="content_bodies">

						<table>
						<tr valign="middle">
						<td><img src="images/icons/announce.gif"/></td>
						<td>
						<b>Posted By: </b><?php echo $row["newsWHO"];?><br/>
						<b>When: </b><?php echo $date_str;?><br/>
						<b>Subject: </b><?php echo $row["newsSUBJECT"];?>
						</td>
						</tr>
						<tr>
						<td></td>
						<td><?php echo $row["newsITEM"] ?></td>
						</tr>
						</table>

						</div>
					</div>
					<?php
					if($rowcount !== $x)
					{echo "<br/>";}					
				}						
			}

			else
			{
			echo "No News or DB is Down";
			}
			?>

my only follow up question is, can I use the asterisk after the date stuff instead of having to define each column to pull?

e.g.

$query = "select DATEDIFF(CURDATE(), newsDATE) AS news_date_diff, DATE_FORMAT(newsDATE, '%l:%i:%s %p') AS news_time, * from news order by newsDATE desc";

[bradgrafelman]

I suppose you can, if you're sure you're always going to be using every column...

[scrupul0us]

in this particular case yes... thanks brad!

[Installer]

Try this:

$query = "SELECT *, DATEDIFF(CURDATE(), newsDATE) AS news_date_diff, " . 
         "DATE_FORMAT(newsDATE, '%l:%i:%s') AS news_time FROM news ORDER BY newsDATE DESC";

(Again untested. 🙂 )