Using the results of one recordset to filter another.

[Smudginald1]

Hi

I have a dynamically generated web page that uses a recordset to gather most of the relevant information that is needed on it. However, what i need to do is create a second record set on the same page and filter that second recordset using one of the results generated from the first recordset. The problem that i am having is that the first recordset displays its information as an echo <?php echo $row_Recordset1['move_type']; ?> but i obviously cannot use this echo code in the filter for the second recordset, the code obviously needs tweaking but i am not sure how.

If it were as simple as copy and paste the query i would need for the recordset2 is as follows but i am unsure how to code the echo from the first recordset. I am well aware that the following code could never work but i hope it works as simplified example of my intention.

SELECT ID, move_name, catagory_link_path, move_type, mem_on_off
FROM moves
WHERE move_type = <?php echo $row_Recordset1['move_type']; ?>
ORDER BY move_name ASC

Any help would be appreciated

[Lon_Winters]

I've only done this in ASP, so I can't give you the exact code, but the process should work the same. Create your first recordset normally, then create the second recordset using the "Entered Value" parameter. In code view, after the first recordset and and the before the second one, declare a variable and populate it with the result from the first recordset. Then just use that variable name as the value for the second recordset. (you may have to use one of the other filter options when first setting tp the second recordset, such as URL Parameter. Then replace the Request statement with the variable name)

[Rob Hecker2]

I can think of two ways this can be done. One way, (which may or may not be appropriate in your case) is to use an INNER JOIN. Here is an example:

$presenterset1=mysql_query("SELECT * FROM presenters INNER JOIN presenters_course USING (presenter_id) WHERE course_id='$course_id' ORDER BY presenter_rank");

Do a search for tutorials on INNER JOIN for information about how to use it.

Another possible solution is to nest one SQL statement within the result of another: like this:

$sql1=mysql_query("SELECT one, two, three from table1 WHERE one='$one'");

while ($result1=mysql_fetch_assoc($sql1)){

extract ($result1);

$sql2=mysql_query("SELECT apple, orange, pear from table2 WHERE fruit='$three'");

while ($result2=mysql_fetch_assoc($sql2)){

extract ($result2);

}

echo "$apple and $orange" and $pear<br/>";

}

[Lon_Winters]

I don't think the first suggestion will work, as it doesn't produce a result to be used for any other part of the statement. However, the Nested query, or also called the Nested Select should work. it actually does the same thing I first mentioned but in a single recordset. It's also the most likely the proper method, whervy my solution would be considered a work around. I've used Nested Selects in the past and they do work, they're just a little confusing to create and troubleshoot.

[Smudginald1]

Thanks for your help all.

I'm still a bit green when it comes to mysql / php and rely heavily on Dreamweavers functionality to generate the code for me. I'm at the stage where i can now tweak the results to my needs.

Lon - I tried your solution even before I posted the question, as it seems like the most logical approach, but I could not get the result from the first record set to populate the  declared variable for the second record set. Unfortunately due to my lack of knowledge on the subject I still don’t know if its my fault or that its just doesn’t work for some reason.

Rob - your second suggestion of the nested  queries was also something I considered but is a little beyond my expertise so with your suggested code I will tweak accordingly and have a fiddle to see if it works.

In the mean time I have found a workaround that works but seems a bit slap-dash. I have just carried an extra url variable through from the links on my Master pages to the details page ( where I am having the problem with 2 record sets) I then populate the  filter option of the second recordset with the required url parameter .

Thanks for your help. I continue to learn and practice.

[Lon_Winters]

If the URL parameter that you used for the second recordset is what it needs to be coming from the previous page, then that is actually the best way to do it, in my opinion. You only need one of the other methods if the output from the first recordset is an unknown value until the recordset runs. Are both recordsets using the same table? If so, the only other thing I would take a look at, wold be to see if all you really may need is a single recordset that is filters by both parameters either using AND or OR. If you'd like to describe a little about what you're trying to accomplish, we can talk about that, otherwise, I think you're good to go. Also, I wouldn't mind if anyone else would confirm or disagree with my assessment.

[Smudginald1]

Thanks for letting me know that passing another variable in the url is not a complete bodge job. To explain a bit more and maybe take it one step further.

The site I’m working on is a site that teaches dance moves. The pages / recordsets i have had the problems with work like this.Firstly all of the information is on one table.The main page (master page) contains a list of all of available dance moves, the names of these moves are linked to the details page.When a move is selected form the master page the variables 'ID' and 'Move Type' are carried to the details page. The details page then shows the move name, degree of difficulty, start position and a video teaching the move ( the path to the video is stored in the moves record on the database)Now the reason for my original question is that each move generally falls within a 'move type' meaning that there are other moves that have similarities. I wanted the second record set to show all of these 'similar moves' next to the video of the move they were currently looking at. As explained i have done this by using the move_type as url variable and then filtered the second recordset using 'move_type‘ to show the similar moves.My next question would be how many url variables can i carry from the master to the details page, for example some moves fall in to 2 different 'move_types' which means i could have a 3rd record set filtering 'move_type_2' from the url, if you see what i mean.What effect does carrying multiple variables have on the database usage, is it good practice, what’s the alternatives? It seems to work but is there a price to pay?

[bregent]

>My next question would be how many url variables

>can i carry from  the master to the details page,

There is no limit, other than the maximum size of a url string. I don't recall what that is, but it is far greater than what you will need.

>for

>example some moves fall in to 2  different 'move_types'

>which means i could have a 3rd record set  filtering

>'move_type_2' from the url, if you see what i mean.

Sure. But I wouldn't create another recordset for each new move type. Just add that value to the SQL WHERE clause and pull all related moves together in one recordset.

>What effect does carrying multiple variables have on the database  usage,

>is it good practice, what’s the alternatives? It seems to work  but is there a price to pay?

You mean having more than one variable passed in the querystring? It has no impact on the database when compared with the alternate of pulling the data from your first recordset.

Either way, you'll need to find a way to deal with each value and get it into the WHERE clause. By now you've realized that you have exceeded the capabilites of DW server behaviours and will need to become more familiar with SQL and PHP.

[Lon_Winters]

I definitely defer to bregent when it comes to SQL and databases ( and most likely other things as well!).  The only thing I would add, is, the flow of going from the master to the detail page is spot on with displaying related move types on the detail page. And, depending on how many records there are on the master page, and if this is likely to grow, you may want to look into a filtering or sorting option there. You could even get fancier on the master page depending on how much detail is shown there vs how much detail is on the detail page. If you think it may be helpful to the users to optionally show a little more detail on the master page before they make their selection, yo can show an intermediate step. I'm thinking of something like NetFlix. The Master page has a thumbnail, title and brief description. The detail page shows all the details in depth. However on the master page, a roll over produces a small, nicely formatted pop up bubble with an overview. This isn't the only way to show intermediate detail, but it could also include related move types as what is on the detail page. Just some brainstormed suggestions, but the main thing for now is to get the detail page down with the most efficient methods of creating you record sets.