[ic] Trouble with select statement and scan tag!

jojo@buchonline.net jojo@buchonline.net
Thu, 9 Nov 2000 16:58:40 +0100 (CET)


Hi list,

Ive a big database. If the visitor is searching for "action", he get 

----snip------
Matches xyz of 659 found

Pages: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 
----snip------

in this case, I want to add a links like

[if value mv_search_match_count > 500]
[seti keyalpha][value-extended name=mv_searchspec joiner=" " index="*"][/seti]
[seti alpha]1[/seti]
[seti alphacheck]1[/seti]

etc...

[seti morelist]<BR>
[page href=scan arg="fi=[scratch productsfile]/un=1/cs=0/os=0/st=sql/co=0/su=0/sq=select * from [scratch productsfile] where (title LIKE '[scratch keyalpha]' OR category LIKE '[scratch keyalpha]' OR producer LIKE '[scratch keyalpha]' OR cast LIKE '[scratch keyalpha]' OR director LIKE '[scratch keyalpha]' OR author LIKE '[scratch keyalpha]') AND title LIKE 'a' ORDER BY title/ml=200"]A</a>&nbsp;
[page href=scan arg="fi=[scratch productsfile]/un=1/cs=0/os=0/st=sql/co=0/su=0/sq=select * from [scratch productsfile] where (title LIKE '[scratch keyalpha]' OR category LIKE '[scratch keyalpha]' OR producer LIKE '[scratch keyalpha]' OR cast LIKE '[scratch keyalpha]' OR director LIKE '[scratch keyalpha]' OR author LIKE '[scratch keyalpha]') AND title LIKE 'b' ORDER BY title/ml=200"]B</a>&nbsp;
[page href=scan arg="fi=[scratch productsfile]/un=1/cs=0/os=0/st=sql/co=0/su=0/sq=select * from [scratch productsfile] where (title LIKE '[scratch keyalpha]' OR category LIKE '[scratch keyalpha]' OR producer LIKE '[scratch keyalpha]' OR cast LIKE '[scratch keyalpha]' OR director LIKE '[scratch keyalpha]' OR author LIKE '[scratch keyalpha]') AND title LIKE 'c' ORDER BY title/ml=200"]C</a>&nbsp;

etc...

[page href=scan arg="fi=[scratch productsfile]/un=1/cs=0/os=0/st=sql/co=0/su=0/sq=select * from [scratch productsfile] where (title LIKE '[scratch keyalpha]' OR category LIKE '[scratch keyalpha]' OR producer LIKE '[scratch keyalpha]' OR cast LIKE '[scratch keyalpha]' OR director LIKE '[scratch keyalpha]' OR author LIKE '[scratch keyalpha]') AND title LIKE 'z' ORDER BY title/ml=200"]Z</a>&nbsp;
<BR>
[/seti]

etc.

[/if]

and the resultspage looks like

----snip------
Searchkey: action 
Matches xyz of 123 found

Go to the results page: A  B  C  .... Z 
Current resultspage: A
Pages: 1 2 3 4 5 6
----snip------

but, the sql query string does not work. For example:

mysql> select <some fields> from cleanproducts where (title LIKE 'action' OR category LIKE 'action' OR producer LIKE 'action' OR cast LIKE 'action' OR director LIKE 'action' OR author LIKE 'action') AND (title LIKE 'A%') ORDER BY title;

show me

+-----------+---------------------------------+-------------+
| code      | title                           | countrycode |
+-----------+---------------------------------+-------------+
| 0469102   | A Life Less Ordinary            |           2 |
| WHV 11786 | Above The Law                   |           0 |
| CBF 000008| Abyss, The - Special Edition    |           1 |
| P009884   | Abyss, The - Special Edition    |           2 |
| WHV 816   | Action Jackson                  |           1 |
| PAR 839762| Active Stealth                  |           0 |
| Tai 80392 | Adventurous                     |           0 |
| CTS 71889 | Air Force One                   |           1 |
| IM 29474  | Air Force One                   |           2 |
| IM 30825  | Alarmstufe 1                    |           0 |
| IM 29921  | Alarmstufe Rot 2                |           2 |
| IM 29826  | Alarmstufe: Rot - Under Siege   |           2 |
| IM 29948  | American Eagle                  |           2 |
| IM 12345  | American Yakuza                 |           2 |
| MAD 9021  | Anna Karenina                   |           1 |
| PAR 323867| Another 48 Hours                |           1 |
| TRI 7030D | Another Day In Paradise         |           0 |
| IM 30080  | Another Day In Paradise         |           2 |
| UDR 90018 | Apollo 13                       |           2 |
| IM 29870  | Apollo 13                       |           2 |
| MCA 20461 | Apollo 13 - DTS                 |           1 |
| MCA 20153 | Apollo 13 - Special Edition     |           1 |
| BUV 15369 | Armageddon                      |           1 |
| IM 29467  | Armageddon                      |           2 |
| PIBF-1173 | Armageddon  DTS                 |           0 |
| CRI 16720 | Armageddon - Special Edition    |           1 |
| IM 29261  | Assassins                       |           2 |
| CTS 28359 | Assignment, The                 |           0 |
| IM 30002  | Auf brennendem Eis - On Deadly  |           2 |
| P008017   | Auf Den Schwingen Des Adlers -  |           2 |
| IM 29262  | Auf der Flucht                  |           2 |
| IM 29402  | Auf Der Jagd - U.S. Marshals    |           2 |
| IM 30124  | Ausser Kontrolle - Chain Ractio |           2 |
| IM 29558  | Avengers, The - Mit Schirm, Cha |           2 |
| WHV 15773 | Avengers, The - The Movie       |           1 |
+-----------+-------------------------------- +-------------+
35 rows in set (0.52 sec)

mysql> 

(the above output is comming from select code, title, countrycode from
.... to make my email readable)

With IC Version 4.6.0 and

[page href=scan arg="fi=cleanproducts/un=1/cs=0/os=0/st=sql/co=0/su=0/sq=select * from cleanproducts where (title LIKE 'action' OR category LIKE 'action' OR producer LIKE 'action' OR cast LIKE 'action' OR director LIKE 'action' OR author LIKE 'action') AND title LIKE 'a' ORDER BY title/ml=20"]TEST sql</a>

I get

--------snipp------
  A Life Less Ordinary                      Action 
  Auf brennendem Eis - On Deadly  Ground    Action 
  Civil Action, A                           Drama 
  Death Wish 5 - The Face Of Death          Action 
  Die Hard 3 - With A Vengeance             Action 
  Die Hard Trilogy - 3 DVD Box Set -        Action 
  Escape From L.A.                          Action 
  Geist und die Dunkelheit, Der -           Action 
  Knightriders                              Action 
  Love And A 45                             Action 
  Project A                                 Action 
  Set It Off                                Action 
  Set It Off                                Action 
  Spawn - Director's Cut                    Action 
--------snipp------

14 items instead of 35 items and IC does not get the right items.
Without un=1 or with un=0, each items appears twice. 

What is wrong? Why am i not able to use

title LIKE 'a%'

or

title RLIKE '^A.*'

in my scan tag?

Any helps, hints and suggestions are very welcome.

Thanks!


	Joachim


-- 
Hans-Joachim Leidinger
buch online                 jojo@buchonline.net
Munscheidstr. 14            FAX: +49 209 1671441
45886 Gelsenkirchen         FAX: 0209 1671441