Is a variable "datetime"? If "yes" then convert to DD/MM/YYY 2

[ABOzIT]

Hello!

I'm very new to PHP and MySQL and I have managed to construct some PHP code to extract all records from a table and display them. The only hurdle is the "datetime" fields. This is waht I have so far:

// printing table rows
while($row = mysql_fetch_row($result))
{
    echo "<tr>";
    // $row is array... foreach( .. ) puts every element
    // of $row to $cell variable
    foreach($row as $cell)
if ($cell==NULL) {
        echo "<td nowrap='nowrap'><font face='Verdana'><font size='1'>$noentry</font></td>";
} else {
        echo "<td nowrap='nowrap'><font face='Verdana'><font size='1'>$cell</font></td>";
}
    echo "</tr>\n";
}

This checks to see if the field is empty and substitutes "na" ($noentry variable) if it is but I would also like to check if the field is a "date" and convert it to DD/MM/YYYY if it is.

Any thoughts or suggestions would be greatly appreciated.

Thanks!

 

[jpadie]

ensure you set your timezone properly before using this

if (preg_match('/\d{4}-\d{2}-\d{2}/', $cell)){
	$cell = date('d/m/Y',strtotime($cell));
}

 

[ABOzIT]

Thanks jpadie for taking the time to reply! Unfortunately this doesn't seem to do what I need. Maybe I didn't provide enough detail originally.....sorry!

Most of the columns in the MySQL table are type varchar(255)but 3 of them are datetime. The datetime values are appearing in the table like this - 2007-10-02 00:00:00 but I'd like them to appear like this 02-10-2007 and without the time part.

So, within the while loop I need each cell to be checked like this: Is this cell a timedate? If no then echo the cell value. If the cell is a timedate then convert to DD-MM-YYYY.

I placed your code like this but I still got an unchanged value:

// printing table rows
while($row = mysql_fetch_row($result))
{
    echo "<tr>";
    // $row is array... foreach( .. ) puts every element
    // of $row to $cell variable
    foreach($row as $cell)
if ($cell==NULL) {
        echo "<td nowrap='nowrap'><font face='Verdana'><font size='1'>$noentry</font></td>";
if (preg_match('/\d{4}-\d{2}-\d{2}/', $cell)){
    $cell = date('d/m/Y',strtotime($cell));
}
} else {
        echo "<td nowrap='nowrap'><font face='Verdana'><font size='1'>$cell</font></td>";
}
    echo "</tr>\n";
}

Any further thoughts?

Thanks!

 

[jpadie]

I am surprised your code worked at all as there are syntax errors.

there is no reason I can see why the regex I provided would not match a date time cell

while ($row = mysql_fetch_assoc($results)):
  echo '<tr>';
  foreach ($row as $field=>$value):
     echo '<td>';
       if (preg_match('/\d{4}-\d{2}-\d{2}/', $cell)):
         echo date('d/m/Y', strtotime($cell));
       elseif (empty($cell)):
         echo $noentry;
       else:
         echo htmlspecialchars($cell);
       endif;
     echo '</td>'; 
  endforeach;
endwhile;

 

[vacunita]

Its not the regex its the placement.

while($row = mysql_fetch_row($result))
{
    echo "<tr>";
    // $row is array... foreach( .. ) puts every element
    // of $row to $cell variable
    foreach($row as $cell)
[red]if ($cell==NULL) [COLOR=red yellow]{[/color][/red]
        echo "<td nowrap='nowrap'><font face='Verdana'><font size='1'>$noentry</font></td>";
[blue]if (preg_match('/\d{4}-\d{2}-\d{2}/', $cell)){
    $cell = date('d/m/Y',strtotime($cell));[/blue]
}
[COLOR=red yellow]}[/color] else {
        echo "<td nowrap='nowrap'><font face='Verdana'><font size='1'>$cell</font></td>";
}
    echo "</tr>\n";
}

You are attempting to preg match a cell that is Null. So no match, no change.

Moving it outside of the If statement would make it work.


----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Behind the Web, Tips and Tricks for Web Development.

http://behindtheweb.blogspot.com/

 

[ABOzIT]

Hmmmm, still getting some weird results but you have both pointed me in the right direction. I'll play around with it and see what I can come up with. That's all part of the fun of programming right??

Have a star each for your troubles!

 

[jpadie]

with my code the reference to $cell should in fact be to $value.