Problems retrieving DATESTAMP/TIMESTAMP via PHP+MySQL

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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

======= date_created.php =======
<?php require_once('Connections/VoIP_Connection.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"
" ">
<title>Creation Date</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">


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

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

======= End of date_created.php =======

Re: Problems retrieving DATESTAMP/TIMESTAMP via PHP+MySQL

Quoted text here. Click to load it

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]

Re: Problems retrieving DATESTAMP/TIMESTAMP via PHP+MySQL

Quoted text here. Click to load it

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 <

Re: Problems retrieving DATESTAMP/TIMESTAMP via PHP+MySQL

Hi Bert,

On Sun, 12 Sep 2004 19:07:36 -0700, Bert Sierra

Quoted text here. Click to load it
Quoted text here. Click to load it

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.php 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. /

Site Timeline