Formatting date in SQL gives "unexpected '%' in PH

software development

#1

I am trying to get Dreamweaver 2004 to help me retrieve MySQL table records into a dynamic table in PHP. Everything was working beautifully in the DW way so defining a dataset and putting the records into a repeating area of a table except the date I retrieved displayed as 2008-01-03 00:00:00 which took too much space when all I really need was year, month day. I tried again defining a record set with a SQL statement like
Select DATE_FORMAT(chkDt, “%Y-%m-%e”), APchkA, APchkJ, ACchkA, ACchkJ, APcohoA, APcohoJ, ACcohoA, ACcohoJ, WildSthd, HatchSthd From tblFishPassLyle
where datediff(curdate(),chkDt) < 40
order by chkDt

but when I test I get an error unexpected '%" in line 4. DW made line 4 look like:
$query_FormattedDate = “Select DATE_FORMAT(chkDt, “%Y-%m-%e”), APchkA, APchkJ, ACchkA, ACchkJ, APcohoA, APcohoJ, ACcohoA, ACcohoJ, WildSthd, HatchSthd From tblFishPassLyle where datediff(curdate(),chkDt) < 40 order by chkDt”;

The SQL tests fine in MySQL.
It didn’t help to put backspaces in front of the %.

This signature line intentionally blank.


#2

Try pulling back the date normally (ie, “SELECT chkDt, APchkA, CPchkJ,…,…,…”), but in your PHP code where you write the data to your table, do this:

<?= date("Y-m-d", strtotime($vchkDt) ); ?>
You’lll need to set $vchkDt on each iteration, or change it to pull it straight from your recordset.

One bit of advice from a guy who’s been using PHP, MySQL, Dreamweaver, UltraDev, etc since the 90’s… don’t use Dreamweaver’s “dynamic” tools to do your PHP or SQL code. It’s nasty, inflexible, and often doesn’t work properly and you’ll be stuck where to make changes.

You’ll be much better of learning PHP & MySQL properly. Dreamweaver is good at what it was designed for - HTML. Its “dynamic” functions fall on a similar level of faith as i have in a local electrician offering to re-slate my roof.

Cheers,
Karl

web design, development, pay per click marketing (PPC) & Search Engine Optimisation (SEO) by DigitalVibe


#3

The trouble looks like nested quotes. Note that you’re using double quotes (") both to start and end the query string and to start and end the date format template. What PHP sees is that the bold portion is inside your string, and the rest is outside:

$query_FormattedDate = "[b]Select DATE_FORMAT(chkDt, [/b]"%Y-%m-%e"[b]), APchkA, APchkJ, ACchkA, ACchkJ, APcohoA, APcohoJ, ACcohoA, ACcohoJ, WildSthd, HatchSthd From tblFishPassLyle where datediff(curdate(),chkDt) < 40 order by chkDt[/b]"You’ll notice that the % signs are outside the bold part, meaning that they’re outside the string, meaning that PHP thinks that those are actual PHP syntax… which, of course, they are not.

The simple solution, since you don’t appear to be doing variable interpolation, is to use single quotes to delimit the string, like so:

$query_FormattedDate = 'Select DATE_FORMAT(chkDt, "%Y-%m-%e"), APchkA, APchkJ, ACchkA, ACchkJ, APcohoA, APcohoJ, ACcohoA, ACcohoJ, WildSthd, HatchSthd From tblFishPassLyle where datediff(curdate(),chkDt) < 40 order by chkDt';


#4

That was my first reaction and what I would have suggested … but I figured DigitalVibe knows a lot more about this stuff than I do, so I just kept my mouth shut.

It’s nice to know that others think like I do though! :wink:

–rlparker


#5

Hehe,

The reason i said what I said is because the OP implies that he was using Dreamweaver’s in-built SQL query generator, which affords you much less scope for doing things properly… and generally isn’t very good at what it tries to do. I’ve had problems changing the Dreamweaver generated SQL in the past where it’s ended up “breaking Dreamweaver”, so if anyone is using it at all, i’d usually advise them to remove as much complication as possible from the SQL… although I appreciate this takes away from the nature of MySQL’s capabilities, it makes things easier with DW.

Which is also why i suggested a hand-rewrite.

Either solution should work, but i’ve had to get people to do the double/single quotes thing, then backtrack so thought i’d avoid doing the backtrack dance :wink:

Cheers,
Karl

web design, development, pay per click marketing (PPC) & Search Engine Optimisation (SEO) by DigitalVibe


#6

Yep! I can appreciate that, and I agree with your general assessment of MySQL by Dreamweaver. :wink:

–rlparker


#7

Thanks. That works for me.

I see what your saying about Dreamweaver and dynamic tools. I’ve only been trying to do this stuff part time for the last couple years. But I remembered when I got DW 2004, it had some features that worked with PHP and MySQL. I managed to work through the tutorial, although if I knew absolutely nothing about MySQL, I wouldn’t have been able to set up the tutorial data. (e.g. Somebody with a Dreamhost account must have already done the DW tutorial, because that database name wasn’t still available on DH.) Then I peaked back at the code DW produced, and thought “That’s a pretty strange way to do it, but OK it works.” Like just setting up the MySQL connection and saving it as an “application binding” didn’t seem to save any effort or make it more understandable.

Then this project came up, and I thought “Hey, this is just like that tutorial. I’ll give it a try” I was hoping for a fast turn around on this, but then I had to a complication, like just retrieve the last 40 days. I found that DW had an advanced tab that let me copy and paste a SQL statement that I knew worked. That worked but then I had to add another complication “format the date”. I tested the SQL statement in Navicat, the formated date "Select DATE_FORMAT(chkDt, ‘%Y-%m-%e’),… " tested fine, but copied into the DW code, it broke. Too many complications.

Testing my SQL statement in Navicat worked because there were no double quotes around the whole statement in that environment. Copied into php the double quotes triggered the parse problem with the line defining the sql statement. But just going to single quotes around the date format string wasn’t enough, I guess because of the way DW set up the table rows in a “repeatable area”:

<?php do { ?> <?php echo $row_last40days['chkDt']; ?> <?php echo $row_last40days['APchkA']; ?> <?php echo $row_last40days['APchkJ']; ?> <?php echo $row_last40days['ACchkA']; ?> <?php echo $row_last40days['ACchkJ']; ?> <?php echo $row_last40days['APcohoA']; ?> <?php echo $row_last40days['APcohoJ']; ?> <?php echo $row_last40days['ACcohoA']; ?> <?php echo $row_last40days['ACcohoJ']; ?> <?php echo $row_last40days['WildSthd']; ?> <?php echo $row_last40days['HatchSthd']; ?> <?php } while ($row_last40days = mysql_fetch_assoc($last40days)); ?>

What worked was read in the date without formating and changing a couple lines at the top of the loop:

<?php do { ?> <?php $vchkDt = $row_last40days['chkDt']; ?> <?php echo date("Y-m-d", strtotime($vchkDt) ); ?>

It works but I still don’t like it. I’d like to right justify the numbers in the table cells. Complication 3. I wonder if I can specify this in the DW design window or will I end up starting over with the write my own code approach.

This signature line intentionally blank.


#8

How does this part work? DW uses mysql_fetch_assoc, a function I haven’t used, to read in the row into an associative array. I had to look it up.

If I had changed the SQL statement to
"Select DATE_FORMAT(chkDt, ‘%Y-%m-%e’),…"
or
"Select date(chkDt), … "

what’s then the index of that first item in the row to replace ‘chkDt’ in the DW generated code for the first item in the ouput table?

<?php echo $row_last40days['chkDt']; ?>

This signature line intentionally blank.


#9

To answer my own question about indexing associative arrays, I think aliasing in the SQL statement is how you do it. Like “Select DATE_FORMAT(chkDt, ‘%Y-%m-%e’) as fmtDate, …”. Then the items would be retrieved as

<?php echo $row_last40days['fmtDate']; ?>

Now the customer has asked if it would be possible to put totals at the bottom row of the table. Complication 4. I should have started out without DW.

This signature line intentionally blank.