Dreamweaver MX 2004 & MySQL Filters

[Guest]

I have made a PHP page that displays all these records and a MySQLL database where I made the following...

seriesID
seriesName
seriesDesc
seriesDate

The problem is when I make a record set and apply a repeat region it shows all of my dates. I want to only show the most current dates only.

For example if I have 3 series for September 21, 2007 and 5 series for September 22, 2007, I do not want to show all of my dates from the repeat region. I only want to show the last current date which is September 22, 2007 all 5 entries, how is this done through MySQL? Is it through sort order and if so, how do I apply it using the recordset. I am using Dreamweaver MX 2004 if that makes a big difference.

[Newsgroup_User · Accepted solution]

AdonaiEchad wrote:
> For example if I have 3 series for September 21, 2007 and 5 series for
> September 22, 2007, I do not want to show all of my dates from the repeat
> region. I only want to show the last current date which is September 22, 2007
> all 5 entries, how is this done through MySQL?

It depends which version of MySQL your server is running. If it's MySQL
4.1 or higher, you can use a subselect query like this:

SELECT * FROM myTable
WHERE seriesDate = (SELECT MAX(seriesDate) from myTable)

If your server is running an earlier version of MySQL, you need to use
two SELECT queries in succession: the first to create a SQL variable,
the second to perform the actual selection like this:

SELECT @latest := MAX(seriesDate) FROM myTable

SELECT * FROM myTable
WHERE seriesDate = @latest

The way that you do this in Dreamweaver is slightly complicated, so I
won't go into it unless you're using an early version of MySQL.

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

[Guest]

Thank you David, that did the job. I had to use the MySQL 4.1 or higher code
SELECT * FROM myTable
WHERE seriesDate = (SELECT MAX(seriesDate) from myTable)

It works like a charm. Thank you again.

[Newsgroup_User]

AdonaiEchad wrote:
> It works like a charm.

Great. It's a lot easier with MySQL 4.1 and above.

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