[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,
I´ve 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>
[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>
[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>
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>
<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