How do I refer to the return of a MySQL function in a select statement

Select DATE_FORMAT(pgmEditTimestamp, '%Y-%m-%d %T') from table;

[frappyjohn]

I'm trying to format a MySQL timestamp for display in PHP.

I can run this select statement just fine in mySQL, but can't figure out how to print the result in PHP:

In MySQL:

select DATE_FORMAT(pgmEditTimestamp, '%Y-%m-%d %T') from programs;

returns:
+----------------------------------------------+
. DATE_FORMAT(pgmEditTimestamp, '%Y-%m-%d %T')?
+----------------------------------------------+
. 2002-12-30 05:30:50 .
. 2002-12-31 09:56:17 .

IN PHP I can print out the raw timestamp just fine, but when I try the DATE_FORMAT function:

$sql = "select DATE_FORMAT(pgmEditTimestamp, '%Y-%m-%d %T') from programs";

$result = mysql_query($sql, $conn);

while ($rec = mysql_fetch_object($result)) {
. echo("<tr>
. <td>$rec->pgmEditTimestamp</td>
. </tr>");
. }

. . . I get nothing for $rec->pgmEditTimestamp.

So I thought maybe I needed to "select function AS variable" and tried this:

$sql = "select DATE_FORMAT(pgmEditTimestamp, '%Y-%m-%d %T') as prettyTimestamp from programs";

$result = mysql_query($sql, $conn);

while ($rec = mysql_fetch_object($result)) {
. echo("<tr>
. <td>$prettyTimestamp</td>
. </tr>");
. }

But then I get:

PHP Warning: Undefined variable: prettyTimestamp in /etc/httpd/. . .

Am I close?

TIA

--John

[jamesa]

try this:

while ($rec = mysql_fetch_object($result)) { 
 echo("<tr> 
. <td>$rec[prettyTimestamp]</td> 
. </tr>"); 
}

[Xuefer]

echo $rec["DATE_FORMAT(pgmEditTimestamp, '%Y-%m-%d %T')"];

or: select DATE_FORMAT(pgmEditTimestamp, '%Y-%m-%d %T') as hoho from programs;
echo $rec['hoho'];

[frappyjohn]

Thanks jamesa and Xuefer!

You both suggested the $rec["column"] format, and that was the key.

However, this means switching from the mysql_fetch_object function to mysql_fetch_assoc.

In the process, I found that -- for this function at least -- I could not embed my $variables in the strings but had to concatenate them instead.

So my final code reads as follows:

$sql = "select pgmCode, pgmName, pgmEditTimestamp,
DATE_FORMAT(pgmEditTimestamp, '%Y-%m-%d %T') as prettyTimestamp
from programs order by pgmCode";
$result = mysql_query($sql, $conn);

while ($rec = mysql_fetch_assoc($result)) {
echo("<tr><td>" . $rec["pgmCode"] . "</td><td>" . $rec["pgmName"] . "</td>"
. "<td><a href='edit.php?program=" . $rec["pgmCode"] . "'>Edit</a></td>"
. "<td><a href='display.php?program=" . $rec["pgmCode"] . "' target='_blank'>View</a></td>"
. "<td>" . $rec["prettyTimestamp"] . "</td>"
. "</tr>");
}

But Xuefer, I couldn't get your first suggestion (to apply a PHP DATE_FORMAT function to the mysql timestamp) to work.

There is no DATE_FORMAT function in PHP. And all of the PHP functions that deal with timestamps appear to deal with Unix timestamps. In this case, we are dealing with a string returned via SQL, not an integer. Plus I believe the Unix timestamp is a different value from the mysql timestamp even if they were both cast to the same type.

Still, you both were correct to suggest the associative array rather than the object fetch.

(I wonder if this is a bug? Why shouldn't the object returned by mysql_fetch_object include the column alias?)

Thanks again,

--John

[jamesa]

Actually I always use mysql_fetch_array, which might solve the other problem.

[andreasfriedrich]

Your second example will probably work if you actually use the returned object´s prettyTimestamp property:

$sql = "select DATE_FORMAT(pgmEditTimestamp, '%Y-%m-%d %T') as prettyTimestamp from programs";

$result = mysql_query($sql, $conn);

while ($rec = mysql_fetch_object($result)) {
. echo("<tr>
. <td>$rec->prettyTimestamp</td>
. </tr>");
. }

Andreas