Can't resolve MySQL error

Thank you for the thorough reply. I can't say I understand it completely, but I'm going to spend some time studying this and Ch. 11 again during the next week or two. I will try to report back with the results -- assuming I don't have any further questions.

Thanks again,

Vera

veramilomilo wrote:
> Can you possibly direct me to any additional info in "Foundation PHP" or "PHP
> Solutions" to focus on?

Most of the information you need is already in Chapter 11 of "Foundation
PHP for Dreamweaver 8", although my forthcoming book, "The Essential
Guide to Dreamweaver CS4 with CSS, Ajax, and PHP" (due out in December)
gives more precise instructions on how to insert a date from those
drop-down boxes.

Basically, your Insert Record server behavior needs to select just one
of the drop-down menus. You then substitute the value of $mysqlFormat
for the $_POST variable of the menu you selected.

Say you have three drop-down menus called "year", "month, and "day".
Select "year" as the value that will be inserted into your date column.
The section of the INSERT query that handles it will look like this:

GetSQLValueString($_POST['year'], "date")

If you have used the validation technique described in "Foundation PHP
for DW8", the full date will be stored as $mysqlFormat. So, change the
preceding line like this:

GetSQLValueString($mysqlFormat, "date")

To deal with the update form, use MySQL date functions and aliases to
get the date parts:

SELECT YEAR(myDate) AS year, MONTH(myDate) AS month,
DATE(myDate) AS day, otherColumn1, otherColumn2
FROM myTable
WHERE primary_key = colname

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

I originally had a single field but later split it into separate drop-down lists on my form so I could control the input. (I also use parts of the date items to dynamically generate product codes.) I started out with the method for validating/formating dates in "Foundation PHP for DW8" (p. 425) except I made all three fields dropdowns. The problem (for me) was that the conversion example was in its own form and I couldn't figure out how to incorporate it into my form or send it to a single field in the db. I also have to be able to do the reverse of this for update forms. As I mentioned, I'm very new to this (PHP & MySQL). Separate fields ended up being all I could wrap my brain around to "successfully" (even if not efficiently) pull off.

Can you possibly direct me to any additional info in "Foundation PHP" or "PHP Solutions" to focus on? Indexes (including those on PHP & MySQL sites) only help when I know the terms I need to look up.

veramilomilo wrote:
> SELECT CONCAT_WS('-', EVENTS.startPostYear, EVENTS.startPostMonth,
> EVENTS.startPostDay) AS startPostDate,
> CONCAT_WS('-', EVENTS.stopPostYear, EVENTS.stopPostMonth,EVENTS.stopPostDay)
> AS stopPostDate
> FROM EVENTS
> WHERE NOW() >= startPostDate AND NOW() < stopPostDate

Column aliases cannot be used in a WHERE clause. To get your SQL to work
you would need to apply CONCAT_WS() to the columns in the WHERE clause.
However, this is an extremely inefficient way to do it.

Your problem stems from the decision to split the dates into their
component parts. This is totally unnecessary, as MySQL has functions to
extract date parts and format dates. You should redesign your database
to store the dates as YYYY-MM-DD, and then use the following SQL:

SELECT startPostDate, stopPostDate
FROM EVENTS
WHERE NOW()>= startPostDate AND NOW() < stopPostDate

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/