Problems retrieving DATESTAMP/TIMESTAMP via PHP+MySQL

[Bert Sierra]

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:
RecordCreatedTi mestamp DATETIME
RecordModifiedT imestamp TIMESTAMP(14)


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

mysql> SELECT RecordCreatedTi mestamp, RecordModifiedT imestamp
-> FROM providers;
+------------------------+-------------------------+
| RecordCreatedTi mestamp | RecordModifiedT imestamp |
+------------------------+-------------------------+
| 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(Rec ordCreatedTimes tamp, "%W, %M %D, %Y %r")
-> AS RecordCreatedNi ceTimestamp,
-> DATE_FORMAT(Rec ordModifiedTime stamp,"%W, %M %D, %Y %r")
-> AS RecordModifiedN iceTimestamp
-> FROM providers;
+----------------------------+-----------------------------+
| RecordCreatedNi ceTimestamp | RecordModifiedN iceTimestamp |
+----------------------------+-----------------------------+
| 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.ph p
source (included below) produces the following terse PHP error message:

Parse error: parse error in date_created.ph p 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@com mspeed.net


======= date_created.ph p =======
<?php require_once('C onnections/VoIP_Connection .php'); ?>
<?php
mysql_select_db ($database_VoIP _Connection, $VoIP_Connectio n);
$query_Creation _Date =
"SELECT RecordCreatedTi mestamp from providers";
$Creation_Date = mysql_query($qu ery_Creation_Da te, $VoIP_Connectio n)
or die(mysql_error ());
$row_Creation_D ate = mysql_fetch_ass oc($Creation_Da te);
$totalRows_Crea tion_Date = mysql_num_rows( $Creation_Date) ;

mysql_select_db ($database_VoIP _Connection, $VoIP_Connectio n);
$query_Creation _Date_Nice =
"SELECT RecordCreatedTi mestamp, \
DATE_FORMAT(Rec ordCreatedTimes tamp, "%%W, %%M %%D, %%Y %%r") \
AS RecordCreatedNi ceTimestamp \
FROM providers";
$Creation_Date_ Nice = mysql_query($qu ery_Creation_Da te_Nice, \
$VoIP_Connectio n) or die(mysql_error ());
$row_Creation_D ate_Nice = mysql_fetch_ass oc($Creation_Da te_Nice);
$totalRows_Crea tion_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>Creati on Date</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>

<table border="2">
<tr>
<td>RecordCreat edTimestamp</td>
</tr>
<?php do { ?>
<tr>
<td><?php echo $row_Creation_D ate['RecordCreatedT imestamp']; ?></td>
</tr>
<?php } while ($row_Creation_ Date =
mysql_fetch_ass oc($Creation_Da te)); ?>
</table>

<table border="2">
<tr>
<td>RecordCreat edTimestamp</td>
<td>RecordCreat edNiceTimestamp </td>
</tr>
<?php do { ?>
<tr>
<td><?php echo $row_Creation_D ate_Nice['RecordCreatedT imestamp'];
?></td>
<td><?php echo
$row_Creation_D ate_Nice['RecordCreatedN iceTimestamp']; ?></td>
</tr>
<?php } while ($row_Creation_ Date_Nice =
mysql_fetch_ass oc($Creation_Da te_Nice)); ?>
</table>
</body>
</html>
<?php
mysql_free_resu lt($Creation_Da te);

mysql_free_resu lt($Creation_Da te_Nice);
?>
======= End of date_created.ph p =======

[Jochen Daum]

Re: Problems retrieving DATESTAMP/TIMESTAMP via PHP+MySQL

Hi Bert,

On Sun, 12 Sep 2004 19:07:36 -0700, Bert Sierra
<bert_sierra@co mmspeed.net> wrote:
[color=blue]
>Hello --
>[/color]
(...)[color=blue]
>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.ph p
>source (included below) produces the following terse PHP error message:
>
> Parse error: parse error in date_created.ph p on line 3
>[/color]
[color=blue]
>
>======= date_created.ph p =======
><?php require_once('C onnections/VoIP_Connection .php'); ?>
><?php
> mysql_select_db ($database_VoIP _Connection, $VoIP_Connectio n);[/color]

if this is line 3, its probably in VOIP_Connection .php

Most missing brackets come as "missing }" or as " unexpected $_end",
so I think its a mising closing quote (' or "). You can prove my
assumption by removing the first line of date_created.ph p completely.

If you take the functions or lines in that file out step by step, you
will find where it is.

HTH, Jochen
--
Jochen Daum - Cabletalk Group Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.

http://sourceforge.net/projects/phpdbedittk/

[Bert Sierra]

Re: Problems retrieving DATESTAMP/TIMESTAMP via PHP+MySQL

In article <bert_sierra-7B0946.19073512 092004@news.com mspeed.net>,
Bert Sierra <bert_sierra@co mmspeed.net> wrote:
[color=blue]
> $query_Creation _Date_Nice =
> "SELECT RecordCreatedTi mestamp, \
> DATE_FORMAT(Rec ordCreatedTimes tamp, "%%W, %%M %%D, %%Y %%r") \
> AS RecordCreatedNi ceTimestamp \
> FROM providers";[/color]

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]

[Tim Van Wassenhove]

Re: Problems retrieving DATESTAMP/TIMESTAMP via PHP+MySQL

In article <bert_sierra-977958.22323512 092004@news.com mspeed.net>, Bert Sierra wrote:[color=blue]
> 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][/color]

Or if people only would RTFM (read the fine manual), especially on the
string type variable.

you had something like $query = " DATE_FORMAT(" .. ") ";


--
Tim Van Wassenhove <http://home.mysth.be/~timvw>