Re: SQL Help - multi values

Short answer - use arrays for storing and contrib/intarray for
indexed access
Regards,
    Oleg
On Sun, 10 Feb 2002, James Carrier wrote:

> Hello
>
> I've just posted this question on the MySQL list but as I'm thinking of moving
> to PostGresQL I thought I'd post to this list too... Hope someone can help!
>
> Basically the problem I am having is how best to handle multiple values for
> a specific column, in this case the values in question are coming from an
> HTML SELECT MULTI box processed by PHP.
>
> The way I have been doing this so far is to have a delimited value stored
> in a varchar column, e.g. If my select box returns the values 2,4 and 7 I
>
> insert into my table the string '|2|4|7|'.
>
> Surely there must be a better way than this - but it escapes me. In this
> setup the only way to match a specifc value when searching is to use the query:
> SELECT dataid,title FROM table WHERE category LIKE '%|4|%'
> Which obviously has a huge performance penalty - and of course you can't
> JOIN against any of these values.
>
> The only other way I thought of was to use a separate table for the
> category entries:
>
> SELECT dataid,title,category FROM table LEFT JOIN table_categories ON
> table.dataid=table_categories.dataid
>
> But in the example above this would return 3 entries, which I don't want,
> and I can't select a particular dataid which satisfies more than category,
> e.g. has categories 4 and 7 (i.e. for the example above the LIKE statement
> would be: WHERE category LIKE '%|4|%' AND LIKE '%|7|%').
>
> Any ideas? Please help!
>
> Cheers,
>
> james
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
Regards,    Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83