Brian B. Burton's Guide to WebDNA Coding

Nested Searches and More Fun with Formatting Found Records into a Table
On page two we learned a little more about how to make our search results look appealing. Let's cover a little more of this, along with nested searches. Way back when (on page 4) we created a way to display our found items in a nice looking table. It looked like this:

Address	Bedrooms	Bathrooms	Square Feet
We found 3 records that matched your search for Parsippany
123 West Main	3	2.5	2000
13 Oak Street	1	1	950
88 Eighth Ave	2	3	880
Average Square Feet:			1276.666666666

Nice. If you'd like to review the code that created the table, please just follow the link above to see it. But what if we wanted a page that would show all of the records for each city, with each city in it's own little table? One way to do this would be a nested search. What's a nested search? It's a search that lives inside of another search. For the first search (or the outer search), we want to search for all of the records in the database, showing only one record from each unique city. While we're at it, let's sort it alphabetically. The second search (the inner search) if going to do the heavy lifting of showing the information all nicely formatted. While we're at it, I'm going to introduce you to a little showif trick to color the background of every other row of a HTML table.

<body>
[search db=listings.db&neCITYdata=[blank]&CITYsumm=T&CITYsort=1]
[founditems] 
   
[search db=listings.db&eqCITYdata=[city]]
There are <B>[numfound]</B> houses for sale in <B>[city]</B><BR>
<table border="1" width="450">
<TR><TD width=10></TD><TH>Address</TH><TH>Bedrooms</TH>
<TH>Bathrooms</TH><TH>Square Feet</TH></TR>
[founditems]
   <TR[showif [index]\2] bgcolor="#DDDDDD"[/showif]>
     <TD>[index]</TD><TD>[Address]</TD><TD>[Bedrooms]</TD>
     <TD>[Baths]</TD><TD>[Sq_ft]</TD>
   </TR>  
[/founditems]
[/search]
</table><BR>

[/founditems]
[/search]
</body>

Ok, the first search finds all of the records of the database, only showing records with a unique value in the City field, and with the city sorted alphabetically. After that, the first founditems tag, then, what's this, another search?!? Yep. That's the whole idea behind nested searches, it's a search within a search. Why do we do this? Because we want to take the data from the city field, and use it in the next search. (Remember, we're going to get each city one time, in alphabetical order)

The inner search is pretty standard. We use a numfound tag again. Notice it's used outside the founditems tag. And then, a new something... <TR[showif [index]\2] bgcolor="#DDDDDD"[/showif]> Wow, this is learning two new things at once. The easier of the two is [index]. [index] gives a numerical value of the placement of the record in the founditems. So, the first record retrieved has an index value of 1, the second record has a value of 2, etc. To help show this, I've added the index value as the first thing shown on each line of the retrieved data.

The second tag is [showif] We're going to cover [showif] in depth later, but for now the code shown reads "show if the index of the record is divisible by two." Therefor, the background color will be shown only on even rows.

What does this look like?

There are 3 houses for sale in Dover

	Address	Bedrooms	Bathrooms	Square Feet
1	836 Main St	4	3	2400
2	7 Lucky Lane	5	5.5	3500
3	432 Beverwyck	3	1	2200

There are 1 houses for sale in Huntsville

	Address	Bedrooms	Bathrooms	Square Feet
1	48 Rock Ave	2	2	1700

There are 1 houses for sale in Newton

	Address	Bedrooms	Bathrooms	Square Feet
1	Route 3 Box 52	3	2.5	2200

There are 3 houses for sale in Parsippany

	Address	Bedrooms	Bathrooms	Square Feet
1	123 West Main	3	2.5	2000
2	13 Oak Street	1	1	950
3	88 Eighth Ave	2	3	880

There are 2 houses for sale in Rockaway

	Address	Bedrooms	Bathrooms	Square Feet
1	99 Century Blvd	2	2	1500
2	62 Valley View	1	2	1200

After reviewing this page, you might think to yourself, "Ok, that's half interesting, but what about the real world? Why would I ever need to know this?" Imagine a page where you want to show a list of products you sell, neatly arranged by category. Maybe this does have practical uses? :-)

That said, nested searches have a downside: they cause the server to suffer a performance hit. In the example above, our database has 10 records. To search it once and shown the records, only takes 10 record examinations. But to nest the search, we have to multiply the number of records times the number of items in the foundset of the parent search (the number of cities in this case) plus one more pass through the database for the initial search. This is (10 records x 5 cities) +10 records = 60 records examined by WebCatalog to generate the page. That's 6 times the work of a normal search. To be sure, this happens amazingly quickly on such a small database. But sometimes you need to plan for the future. If you have a page, getting hit 10,000 times a day, doing nested searches on a 1000 record database, that's a lot of load. Could this be programmed in a simpler manner?

<-- Previous Index Next -->

This whole page, and everything on it ©2001 Brian B. Burton. This page and the text contained herein may not be reproduced on any other site, or via any other means. Really, how hard is it just to link to this site, so people get to see the latest updates? http://www.burtons.com/webdna/ Thank You.

Site design by Clint Davis. Thank You Clint!