[ic] bra finder puzzle

John Matecsa matecsaj at picassofish.com
Fri Oct 29 15:31:53 EDT 2004

For a lingerie site, in the advanced search page, I'm trying to add a
"bra finder". After selecting band and cup size, then pressing search,
the woman should see just the bras that will fit her.

(Yes, everyone in the office wants to work on this client's site!)

The site is running Interchange 4.8 with MySql on RedHat.


On the Interchange demo site, the equivalent challenge would be add a
"paint brush finder" to the advanced search page. The user would select
a bristle of None, Human hair, Camel hair*, or Synthetic from a drop
down menu. Then the user would select a color White or Black from
another drop down menu. Then press the Search button. On the results
page would appear all paint brushes that have the chosen options.


I roughed in the UI as follows:

<form action="[area search]" method=post>
<input type=hidden name=mv_session_id value="[data session id]">
<input type=hidden name=mv_matchlimit value=[scratch matchlimit]>
<input type=hidden name=mv_substring_match value="Yes">

<b>Bra Finder:</b><br>

<input type="image" alt="Search" src="__THEME_IMG_DIR__go.gif" border=0>

<br/>If you require a DD check E also.
<br/>If you require a DDD check F also.
<br/>If you require a FF check G also.


Then I created the following which will return the skus for all 38-C

SELECT products.sku
FROM products, options as cup, options as size
WHERE products.sku = cup.sku
AND cup.sku = size.sku
AND cup.o_label = 'Cup'
AND size.o_label = 'Size'
AND cup.o_value LIKE '%C%'
AND size.o_value LIKE '%38%'


I tried to express the select statement using mv_sql_query but
discovered that it does not support the dot notation. The following
trivial example demonstrates the failure: 

<form action="[area search]" METHOD=POST>
<INPUT TYPE="HIDDEN" NAME="mv_profile" VALUE="testprofile">
<input type=text name="search_cat" size=20 maxlength=20>
<INPUT TYPE="hidden" NAME="mv_doit" VALUE="search">
<INPUT TYPE="HIDDEN" NAME="mv_sql_query" VALUE="
                          SELECT products.sku FROM products
                                  WHERE products.category = search_cat

<INPUT TYPE="submit" VALUE="[L]Search[/L]">


Then I tried to express the select statement using "Coordinated and
Joined Searching". 

I couldn't find a way to express the equivalent of table aliases, so
that I could join the options table twice, so that I could filter on
both size and cup. Or in my case this part of the SQL statement

FROM products, options as cup, options as size

I also couldn't figure out how to express the relationship between
tables.  Or in my case this part of the SQL statement

WHERE products.sku = cup.sku AND cup.sku = size.sku

My test code was so long and nasty that I left it out.


Then I scoured the docs and mail list archives to no avail. 


I'm starting to see double! No pun intended.
Thanks in advance for your merciful suggestions.

John Matecsa <matecsaj at picassofish.com>
Picasso Fish Corporation

More information about the interchange-users mailing list