[ic] Using SQL Queries - Please Help

[Thomas J.M. Burton]

Hello all,

I know there's plenty of threads in the archive pertaining to various
SQL questions, but searching through them has become frustrating. I've
read a lot of them, as well as portions of the docs and still can't
figure out how to do what I'm trying to do.

Originally, I had a page containing:

[query list=1
  sql="SELECT p.*,b.brand_label,mc.mc_label,s1.sc1_label,s2.sc2_label
  FROM products p,brands b,maincat mc,subcat1 s1,subcat2 s2
  WHERE p.mc_id=mc.mc_id
  AND p.sc1_id=s1.sc1_id
  AND p.sc2_id=s2.sc2_id
  AND p.brand_id = b.brand_id
  AND p.sc2_id='[value sc2]'
  ORDER BY b.brand_label"
  ml=20]

Accessed using the URL:
http://new.adadance.com/cgi-bin/ada/scan/va=sc2=2/sp=sqltest

This worked just how I wanted it to until I tried the links generated by
the [more] tag. If you try it, you'll see that it doesn't actually go to
the next page but reloads the first page of the search instead.


After searching through the docs and maillist archives, I decided to try
the search as a link:

(line breaks inserted for readability, sq is actually one line)

[page scan st=sql
  sq="SELECT r.*,b.brand_label,mc.mc_label,s1.sc1_label,s2.sc2_label
  FROM products r,brands b,maincat mc,subcat1 s1,subcat2 s2
  WHERE r.mc_id=mc.mc_id
  AND r.sc1_id=s1.sc1_id
  AND r.sc2_id=s2.sc2_id
  AND r.brand_id = b.brand_id
  AND r.sc2_id='2'
  ORDER BY b.brand_label"
  sp=sqltest3
  ml=10
]Page Method[/page]

The link generated by this code and a couple of other attempts can be
found at:
http://new.adadance.com/cgi-bin/ada/linktest


The result here was [more]-generated links that worked perfectly, but
the wrong query. These links return all products in the database. Rather
than passing on the mv_sql_query specified, it's just doing "select *
from products".

When clicking one of these links, the log shows:

011221 12:23:13  350562 Connect    interch@localhost as anonymous on ada
                 350562 Query      select * from products
011221 12:23:14  350562 Quit


I've even tried setting it up as a search profile and got the same
results as I did with the link method.

I'm sure I'm just missing something simple here. Probably not formatting
my link correctly. If you can help, please tell me what's missing. If
there is a thread I should reference, please include a link in your
reply. I've spent the last two days searching the archives and haven't
found anything that's solved my problem.

I really need to utilize the speed of SQL queries in this and another
project, so I must know how to get this working properly.

Thanks in advance,
TJMB