Dreamweaver - Problems retrieving DATESTAMP/TIMESTAMP via PHP+MySQL

Author

Problems retrieving DATESTAMP/TIMESTAMP via PHP+MySQL

Bert Sierra

2004-09-13, 4:15 am

Hello --

I have what appears to be a simple PHP+MySQL query, but Dreamweaver
consistently generates PHP code which won't parse. I've stared at the
PHP code for several hours now, and I can't see the problem. Thanks in
advance for any input you might have to the problem.

[I am cross-posting between Dreamweaver and PHP newsgroups, since the
problem appears to straddle both topics. Sorry for that!]

I have an mysql database with two timestamp records, shown below:
RecordCreatedTimestamp DATETIME
RecordModifiedTimestamp TIMESTAMP(14)

My test database contains the following six records, shown in their
default format:

mysql> SELECT RecordCreatedTimestamp, RecordModifiedTimestamp
-> FROM providers;
+------------------------+-------------------------+
| RecordCreatedTimestamp | RecordModifiedTimestamp |
+------------------------+-------------------------+
| 2004-09-01 11:50:59 | 20040912152939 |
| 2004-09-02 10:29:09 | 20040912153118 |
| 2004-09-02 14:07:11 | 20040912153904 |
| 2004-09-02 15:09:15 | 20040912153600 |
| 2004-09-06 15:03:11 | 20040912154145 |
| 2004-09-08 16:27:04 | 20040912122418 |
+------------------------+-------------------------+
6 rows in set (1.01 sec)

I want to reformat these values using the DATE_FORMAT() function so that
they are more presentable. The following mysql query works just fine
(I've clipped the output to fit this posting):

mysql> SELECT
-> DATE_FORMAT(RecordCreatedTimestamp, "%W, %M %D, %Y %r")
-> AS RecordCreatedNiceTimestamp,
-> DATE_FORMAT(RecordModifiedTimestamp,"%W, %M %D, %Y %r")
-> AS RecordModifiedNiceTimestamp
-> FROM providers;
+----------------------------+-----------------------------+
| RecordCreatedNiceTimestamp | RecordModifiedNiceTimestamp |
+----------------------------+-----------------------------+
| Wednesday, Septemb... etc. | Sunday, September... etc. |
| Thursday, Septembe... etc. | Sunday, September... etc. |
| Thursday, Septembe... etc. | Sunday, September... etc. |
| Thursday, Septembe... etc. | Sunday, September... etc. |
| Monday, September... etc. | Sunday, September... etc. |
| Wednesday, Septemb... etc. | Sunday, September... etc. |
+----------------------------+-----------------------------+
6 rows in set (0.47 sec)

The problem is that when I construct Dreamweaver Record Sets and
hand-type advanced mysql queries similar to those above, Dreamweaver
generates PHP code that won't parse. For example, the date_created.php
source (included below) produces the following terse PHP error message:

Parse error: parse error in date_created.php on line 3

I can't for the life of me figure out where the syntax error is.

I know:
o The mysql query syntax is correct. If I hand-type the SQL query in my
MySQL command-line interface, I get the properlt formatted tables.
o My Connections/VoIP_Connection.php script is not the problem. I can
generate other queries just fine over the same connection.
o If I eliminate the second 'nice' query and associated table, the
problem goes away.
o I am running Mac OSX 10.3.5, Apache 1.3.29, mysql Standard 4.0.16, and
Dreamweaver MX 2004. My testing server is a WindowsNT box running
Microsoft-IIS 5.0 and PHP 4.1.1.

TIA for any help!!

-- Bert Sierra
Tempered MicroDesigns
Prescott, AZ
bert_sierra@commspeed.net

======= date_created.php =======
<?php require_once('Connections/VoIP_Connection.php'); ?>
<?php
mysql_select_db($database_VoIP_Connection, $VoIP_Connection);
$query_Creation_Date =
"SELECT RecordCreatedTimestamp from providers";
$Creation_Date = mysql_query($query_Creation_Date, $VoIP_Connection)
or die(mysql_error());
$row_Creation_Date = mysql_fetch_assoc($Creation_Date);
$totalRows_Creation_Date = mysql_num_rows($Creation_Date);

mysql_select_db($database_VoIP_Connection, $VoIP_Connection);
$query_Creation_Date_Nice =
"SELECT RecordCreatedTimestamp, \
DATE_FORMAT(RecordCreatedTimestamp, "%%W, %%M %%D, %%Y %%r") \
AS RecordCreatedNiceTimestamp \
FROM providers";
$Creation_Date_Nice = mysql_query($query_Creation_Date_Nice, \
$VoIP_Connection) or die(mysql_error());
$row_Creation_Date_Nice = mysql_fetch_assoc($Creation_Date_Nice);
$totalRows_Creation_Date_Nice = mysql_num_rows($Creation_Date_Nice);
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Creation Date</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>

<table border="2">
<tr>
<td>RecordCreatedTimestamp</td>
</tr>
<?php do { ?>
<tr>
<td><?php echo $row_Creation_Date['RecordCreatedTimestamp']; ?></td>
</tr>
<?php } while ($row_Creation_Date =
mysql_fetch_assoc($Creation_Date)); ?>
</table>

<table border="2">
<tr>
<td>RecordCreatedTimestamp</td>
<td>RecordCreatedNiceTimestamp</td>
</tr>
<?php do { ?>
<tr>
<td><?php echo $row_Creation_Date_Nice['RecordCreatedTimestamp'];
?></td>
<td><?php echo
$row_Creation_Date_Nice['RecordCreatedNiceTimestamp']; ?></td>
</tr>
<?php } while ($row_Creation_Date_Nice =
mysql_fetch_assoc($Creation_Date_Nice)); ?>
</table>
</body>
</html>
<?php
mysql_free_result($Creation_Date);

mysql_free_result($Creation_Date_Nice);
?>
======= End of date_created.php =======

Bert Sierra

2004-09-13, 4:15 am

In article <bert_sierra-7B0946.19073512092004@news.commspeed.net>,
Bert Sierra <bert_sierra@commspeed.net> wrote:

> $query_Creation_Date_Nice =
> "SELECT RecordCreatedTimestamp, \
> DATE_FORMAT(RecordCreatedTimestamp, "%%W, %%M %%D, %%Y %%r") \
> AS RecordCreatedNiceTimestamp \
> FROM providers";

OK -- I figured out my problem after dinner and a relaxing cup of tea.
Hope the earlier posting didn't cause too much of a distraction!

My PHP problem is in the format string. It should read '%W, %M %D, %Y
%r' with single quotes, not "%%W, %%M %%D, %%Y %%r" with double quotes.

Thanks again! If only PHP offered less cryptic error messages.... [sigh]