I have two datasets:

recordset1 which along with other information contains the index to
corresponding recordset2

I have set up a repeating region and need a field from recordset2 to display
with record1 data. I really have tried everything but there seems no way to
control which order cs3 puts the php code for the mysql read. I can easily put
in a list menu and cs3 will do the code to select recordset2 correctly but it's
a display and I am sure there is something simple I am missing and appreciate
in advance your time and advice.



I am running php 5 with mysql 5 on my IIS vista machine.

>recordset1 which along with other information contains the index to
corresponding recordset2

Hard to without seeing the bigger picture, but if the two recordsets are
related can't you just combine them into a single recordset query?

recordset1 is an accessory table
recordset2 is a brand table

Repeating region displays recordset1 and contains an index to the brand table
and I want to display the correct brand name based on the index in recordset1

azunoman wrote:
> I have two datasets:
>
> recordset1 which along with other information contains the index to
> corresponding recordset2
>
> I have set up a repeating region and need a field from recordset2 to display
> with record1 data. I really have tried everything but there seems no way to
> control which order cs3 puts the php code for the mysql read. I can easily put
> in a list menu and cs3 will do the code to select recordset2 correctly but it's
> a display and I am sure there is something simple I am missing and appreciate
> in advance your time and advice.
>
>
>
> I am running php 5 with mysql 5 on my IIS vista machine.

You only need one recordset, and that one will use whats called a JOIN.
You implement the join in your SQL statement. Do a google on mysql join
and see if it makes any sense. If not, post your 2 existing SQL
statements and we will see if we can do it for you.

Steve

I am having alot of SQL trouble with this can someone tell me how to join it i am having the same problem

Norwichhat wrote:
> I am having alot of SQL trouble with this can someone tell me how to join it i am having the same problem

Read this first: http://www.w3schools.com/Sql/sql_join.asp

Then post what you have tried in a new post and then we will see if we
can .

Steve

>Repeating region displays recordset1 and contains an index to the brand table
and I
>want to display the correct brand name based on the index in recordset1

Yes, this should all be done in a single recordset by joining the two tables
in the sql statement. Trying to do this using 2 recordsets and scripts is
cumbersome and kludgey. If you don't know SQL yet, it's time to learn.
http://www.w3schools.com/sql/default.asp

Thanks everyone...I was counting on behaviors to behave..I cluged it by
inserting a menu/list with dynamic data and then deleteing the form options.
While it did display the brand correctly I deleted it because as large as my
project it should be done correctly, i.e. Join. I will post back my status
later on my efforts there

I don't think however its a far stretch to say that for a less experienced
person that a behavior that works like a dynamic menu/list item in a form that
could be used foor repeating displays could also be provided but I
digress....but given how robust dw might be one is still requried to be
somewhat proficent in knowledge.

I like webassist products and wonder if their dataassist product might add
more database clickable behaviors.

Cheers, off to get Join coded up....

Wooohoooo!...Hats off to everyone for pointing me to some great information.

I put in a Left Join code and much to my GLEE dw had the field name under
recordset1's bindings so I bound the field in recordset2 to the repeating
region display and it works. Fantastico.

Again, hats off to dw for including BrandName in the record Binding Panel
under recordset1...

azunoman....111 today

SELECT recordset1.*, recordset2.BrandName
FROM recordset1
LEFT JOIN recordset2 ON recordset1.AccBrandID=recordset2.BrandID
ORDER BY AccTitle ASC

>I put in a Left Join code

Are you sure you need an outer join for this? It's better to use an inner join unless you are sure that there may be unmatched rows you want returned.

[Q]>I put in a Left Join code [/Q]

I picked Left Join because I need recordset1 to display regardless of the
results getting the BrandName out of recordset2. All these database are new
and in some cases I have as yet to modify the tables to the correct recordset2
so I wanted to ensure all recordset1 rows display in my repeating region.

>I picked Left Join because I need recordset1 to display regardless of the results getting the
>BrandName out of recordset2.

Good, outer join is correct in that case.

azunoman wrote:
> Wooohoooo!...Hats off to everyone for pointing me to some great information.
>
> I put in a Left Join code and much to my GLEE dw had the field name under
> recordset1's bindings so I bound the field in recordset2 to the repeating
> region display and it works. Fantastico.
>
> Again, hats off to dw for including BrandName in the record Binding Panel
> under recordset1...
>
> azunoman....111 today
>
> SELECT recordset1.*, recordset2.BrandName
> FROM recordset1
> LEFT JOIN recordset2 ON recordset1.AccBrandID=recordset2.BrandID
> ORDER BY AccTitle ASC

I was in the same boat as you about 7 years ago when I started to do
database stuff with Dreamweaver, and it was very frustrating until I got
myself SAMs Teach Yourself SQL in 10 minutes. It gave me such an insight
into how it all works, I was soon making all sorts of little
applications for my websites, it was such an eye opener.

Dreamweaver is great, it lets you start to see the bigger picture, but
once you learn more, it lets you be the bigger picture. Its amazing how
some knowledge can really expand Dreamweaver and your abilities.

We don't tell people to learn first without good reason, you can do so
much more once you have learnt the basics.

Steve