close ad
WARNING: Do Not Install the DREAMWEAVER CC 2017 Update »
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

Multiple Table View with DA

Thread began 12/31/2009 5:32 am by tim383164 | Last modified 1/04/2010 8:15 pm by Eric Mittman | 803 views | 3 replies |

tim383164

Multiple Table View with DA

I am building a sporting comp website, where I have a primary table called fixtures which has the following structure:

fixtureID (PK)
roundID (FK) -> Rounds Table
GameField
GameTime
DivisionID (FK) -> Divisions Table
HomeTeamID (FK) -> Team Table
AwayTeamID (FK) -> Team Table
HomeTeamScore
AwayTeamScore
Ref1ID (FK) -> Referees Table
Ref2ID (FK) -> Referees Table
Ref3ID (FK) -> Referees Table

If I use the DA Wizzard and select the Fixtures Table, I can create the Insert / Update / Delete pages etc. fine. The main issue I have is that the Results page will look something like:

fixtureID: 1
roundID: 1
GameField: 2
GameTime: 6:30pm
DivisionID: 1
HomeTeamID: 23
AwayTeamID: 42
HomeTeamScore: 4
AwayTeamScore: 2
Ref1ID: 1
Ref2ID: 2
Ref3ID: 3

Where the other linked Tables would contain this type of information.

Divisions Table ->
DivisionID = 1, Division_Name = Mens
DivisionID = 2, Division_Name = Mixed

Team Table ->
TeamID = 23, Team_Name = Team A
TeamID = 42, Team_Name = Team X

Rounds Table ->
RoundID = 1, Round_Desc = Round 1
...
RoundID = 15, Round_Desc = Qtr Final

Referees Table ->
RefID = 1, RefName = Bob
RefID = 2, RefName = John
RefID = 3, RefName = Michael

Thus I would like to see the results page reflect something like this:-

fixtureID: 1
round: Round 1
GameField: 2
GameTime: 6:30pm
Division: Mens
HomeTeamID: Team A
AwayTeamID: Team X
HomeTeamScore: 4
AwayTeamScore: 2
Ref1: Bob
Ref2: John
Ref3: Michael

In order to do that I would need a multi-table select querey or view to display the results page.

I am able to create views with my MySQL implementation (and I also know that one cant update MySQL Views), so for my In/Up/Del functions I would use the Fixtures Table byitself, but would like to see the Results page in a more readable format.

Is it possible after running the DA Wizzard to revisit the Results admin page and change the recordset from the single Fixtures Table to that of say a View Table (multi-table select) which would then show the more readable content.

Or is there any other way to do this?

Sign in to reply to this post

Eric Mittman

You are on the rite track. To get the info to display the values from the other tables rather than their id's you would need to alter the recordset that is on the results page. This recordset is independent of the search on the page so you can alter this without altering the search.

If you have a query that will get at the info like this for you then you should try using that. I think it may be easiest to just start with the existing query and alter it by adding in some JOIN statements to link the tables together. I really think that this would be the easiest way to get the results showing how you would like them.

Sign in to reply to this post

jo4koalas392067

Hi

I had the same problem and found it easier to create the query and test it outside of the recordset/dreamweaver. I am new to MySQL, but I have found SQLyog (www.webyog.com) a really useful tool for this and there is a 30 day free trial, which I am using (but may end up actually getting the program as it saves so much time and typing). Their QueryBuilder is a very similar format to Microsoft Access if you have ever used that.

Once I have it working in SQLyog, I copy and paste the query into my recordset. Oh, but don't forget to remove the ";" from the end of the query in SQLyog as it really mucks things up if you keep it in there (took me ages to figure out why this was happening).

Hope this is of some help.

Jo

Sign in to reply to this post

Eric Mittman

Thanks for the suggestion, this is great advice for anyone wanting to alter a recordset or create a new custom one. After confirming that your query works adding it to the page should ensure you get the same result.

Sign in to reply to this post

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...