Manage Relational Table question

I've been through the WA Manage Relational Table tutorial with the Blue Sky Music database, and got it all working. I just want to know the best way of displaying the genre list in the items_detail.php page. Should I change the recordset to one that joins all 3 tables that filters the ItemID URL variable, then add the genres.genrename to the detail table, then apply a Repeat Selection SB to this binding?

Or is there an easier way?

Perhaps you should include this in the tutorial.

Cheers
Dave

I've got this working on the detail page as I suggested with the repeating selection. Quite easy. Recordset:

SELECT *
FROM items, genres, itemgenres
WHERE itemgenres.ItemGenreItemID=items.ItemID AND itemgenres.ItemGenreGenreID=genres.GenreID AND itemID=varItem

varItem = $_GET['ItemID'] (from the URL variable)

But it would still be good to include this in the how-to.

Cheers
Dave

it would be better to use an explicit join query and only return the GenreName column

for example:

SELECT genres.GenreName
FROM genres
INNER JOIN itemgenres 
ON itemgenres.ItemGenreGenreID = genres.GenreID
INNER JOIN items
ON itemgenres.ItemGenreItemID=items.ItemID
WHERE itemID = varItem

Using the Where clause for creating the joins is valid, but it's easier to read if you use the JOIN syntax. Also, it allows you to easily modify the query to use a LEFT or RIGHt JOIN, or even an OUTER JOIN depending on how you want the results to act.


I have forwarded this to the documentation team, thanks for the feed back.

Thanks Jason - that sounds more efficient.
So there would be 2 separate recordsets:

1. main table data: rsGenres as in the tut
2. options table data: rsGenreName with the 2 JOINs

Now so much easier to understand and use for my project that will be using an options table for images, so that each record in the main table will contain several images referenced by their filename and selected from the images gallery using checkboxes.

May need your help with this later on.

Cheers
Dave

well, actually you could use one recordset to return the Item Info and the genre names related to that item by using:

SELECT items.*, genres.GenreName
FROM genres
INNER JOIN itemgenres 
ON itemgenres.ItemGenreGenreID = genres.GenreID
INNER JOIN items
ON itemgenres.ItemGenreItemID=items.ItemID
WHERE items.itemID = varItem

Hi Jason - going great guns with the MRT on my own project.
I have another slight issue with the checkboxes in the repeat region not clearing after insert. A new insert page always shows the previous selection. It happens with the WA MRT tutorial and my project.

the session that is created by the insert needs to cleared.


for example, in the tutorial the session variable is named "WADA_Insert_Items", so add the following code at line 1 of the insert page:

<?php 

@session_start();

if(isset($_SESSION['WADA_Insert_Items'])) unset($_SESSION['WADA_Insert_Items']);

?>

Thanks Jason - that worked!