[ic] Searching multiple fields question

[Steffen Dettmer]

* Steffen Dettmer wrote on Wed, Nov 21, 2001 at 06:52 +0100:
> Just for completness, if anyone is interessted in some example
> code for a SQL search page, drop me a note.

I received some notes per PM. So here a short info.

The example uses a scratch shop_id (I have multiple product
data sets with different properties in the same table). If you
want to play with it you will need to remove the references to
shop_id. I put it here for two reasons: first, I'm tired now
after a hard day and won't risk to remove to much code :) and
second example code is better when longer :)

oki. This is based on foundation demo shop. On the pages there is
a menu box (one click searches). The code is in
templates/compnents/category_vertical. I changed it to fetch it
not via [loop] but [query]. This example shows how to cascade
query, which is very powerful:

[comment]
"Outer" query lists areas taht are defined in our shop_id
"Inner" query processes each area and fetches the
  categories of that area.

The menu_link function formats that link in a way that it works
with the embedded java menu. This code is originally in a
VALUE="..." attribute.

[/comment]

[query
        prefix=box
        type="list"
        list_prefix="boxlist"
        sql="select
                distinct on (area.sort, area.code)
                area.code
                from area, cat
                where area.sel='[control set_selector left]'
                and area.code = cat.sel
                and cat.shop_id = [scratch shop_id]
                order by area.sort
             "]
  [boxlist]{[box-exec bar_link]area[/box-exec]}

    [set found_cat]1[/set]
    [query
            prefix=cat
            list_prefix="catlist"
            type="list"
            sql="select code, name
                    from cat
                    where sel='[box-code]'
                    order by sort"
            ]
      [catlist]{|[cat-exec menu_link]cat[/cat-exec]}[/catlist]
    [/query]

  [/boxlist]
[/query]

 


That's it :)


The SQL search is slightly more to do. First, we need a search
box - basically a text input field. Of course it's possible to
add features like scan supports, but here it has no properties,
it just searches a string in multiple fields.

The code for the search box is trivial (put it i.e. in
search_box_small component):

      <form action="[area search-catalog]" method=post>
        <input type=hidden name=mv_session_id value="[data session id]">
        <table border="0" width="95%">
          <tr>
              <td align="left">
                <INPUT MAXLENGTH=30 NAME=search_word type=text size=8>
              </td>
              <td align="right" valign="middle" width="45">
                <input type="image" alt="Search Go" width="45" 
  		     border="0" src="__THEME_IMG_DIR__go.gif">
              </td>
          </tr>
        </table>
      </form>

 
IC generates the right URL for us with the area tag. All logic is
done by IC, nice :) We need only to pass the session id in a
hidden field and of course our text field. I called it
search_word.

Of course we now need a page search-catalog.html. We do need to
put it into the pages directory and it will work. The page is
very similar to the results.html page, but instead of [item-code]
or item-whatever we need to use [sql-code] or sql-whatever. AFAIK
the most names are really the same, so I did it by global
substitution (vim :%s/item-/sql-/gc).

The [search-region] tag has to be replaced with the query tag:

[query sql="
        select sku, description, price
        from products
        where shop_id = [scratch shop_id]
        and (  lower(description) like lower('%[cgi search_word]%')
            or lower(title) like lower('%[cgi search_word]%')
            or lower(comment) like lower('%[cgi search_word]%') )
        order by category"
       type=list
       more=1
       ml=10]


The "lower()" construction is for case-insensitve matches, surely
there are better solutions for that. 

Instead of [search-list] use [list].

Well, that query allows [on-match] like search-region, instead of
item-change it's now sql-change and so on. 

Well, you could use prefix_list="search-list" and prefix="item"
which may make the tag names behaive like the ones from
search-region, but I haven't tested it.

Well, since the whole gzip'd file is smaller than this text, I'll
attach it (I hope it wouldn't hurt the list policy).

I'm sorry that the text is german (had no time for locale :)),
but since you should have the results.html you should understand
it completly.

Hope it helps a little.

Have a nice day, dear list!

oki,

Steffen

-- 
Dieses Schreiben wurde maschinell erstellt,
es trägt daher weder Unterschrift noch Siegel.