Re: [SQL] problem with select

Holm,
Can you clarify the problem a bit?

> So far so good, but I have the problem that I have the value 'abc'
> only one times !

Do you mean you only get one row as a result?

You might do better with using "group by" instead of distinct,
if you only want a distinct code. I.e.

select code,category from products where code ~* 'abc' or
category ~* 'abc' or manufacturer ~* 'abc' or description ~* 'abc'
or comment ~* 'abc' group by code;


Troy



>
> Hi all,
>
> I have an problem and I can't find a solution.
>
> I have a products database like this:
>
> code varchar,
> category varchar,
> title varchar,
> manufacturer varchar,
> description varchar,
> desc_de varchar,
> comment varchar,
> price float,
> ...
>
> What I try to find is a solution for a search in this table like this:
>
> select distinct code,category from products where code ~* 'abc' or
> category ~* 'abc' or manufacturer ~* 'abc' or description ~* 'abc'
> or comment ~* 'abc' order by code;
>
> So far so good, but I have the problem that I have the value 'abc'
> only one times !
>
> This is in conjunction with minivend, an internet shopping mall,
> the query is generated as follows:
>
> ....
>  <TD [var TABLEFG] ALIGN=RIGHT>
>          <FONT SIZE=-1 FACE=ARIAL,HELVETICA style="text-decoration: none">
>         <INPUT TYPE="hidden" NAME="mv_searchtype" VALUE="sql">
>         <INPUT TYPE="hidden" NAME=mv_matchlimit VALUE="3">
>         <INPUT TYPE="hidden" NAME=mv_sql_query
>          VALUE="select code,category from products where comment ~* ?">
>                                                                    ^^^
>         <INPUT MAXLENGTH="30" NAME="mv_searchspec" SIZE="13" VALUE=""><BR>
>         <FONT FACE="arial,helvetica" SIZE="-1">
>         <INPUT TYPE="submit" VALUE="[loc]Search[/loc]"><BR>
> ....
>
> The question mark get's replaced by the contents of the HTML input field,
> and I have no chance to create a temporary table here (security issue).
>
> I must create a view or a rule or something to get a query like this:
>
> VALUE="select foobar from myview where comment ~* ?">
>
> How can this be done ?
>
>
> Thanks in Advance,
> Holm
> --
> FreibergNet Systemhaus GbR      Holm Tiffe  * Administration, Development
> Systemhaus für Daten- und Netzwerktechnik           phone +49 3731 781279
> Unternehmensgruppe Liebscher & Partner                fax +49 3731 781377
> D-09599 Freiberg * Am St. Niclas Schacht 13    http://www.freibergnet.de/
>
>
> ************
>
>