YL,

>Thansk Peter. find_in_set does the job. column 'options' is odd from
>database design point of view. While from app developers' view, it's
>much easier to retrieve the option id array from one column. where
>the order matters (although this can be done with an option association
>table).

You need just one simple options table...

CREATE TABLE ttoptions (
  tt_id INT NOT NULL,
  optionno TINYINT NOT NULL DEFAULT 0,
  optionorder TINYINT NOT NULL DEFAULT 0
)
INSERT INTO ttoptions VALUES
  (1,1,1),(1,2,2),(1,3,3),
  (2,2,1),(2,3,2),(2,7,3),
  (3,3,1),(3,1,2),(3,7,3);
SELECT
  tt.id,
  tt.name,
  GROUP_CONCAT(optionno ORDER BY optionorder)
FROM tt INNER JOIN ttoptions ON tt.id=ttoptions.tt_id
GROUP BY tt.id
ORDER BY tt.name
+----+------+------------------------+
| id | name | GROUP_CONCAT(optionno) |
+----+------+------------------------+
|  1 | abc  | 2,3,1                  |
|  2 | bbc  | 2,3,7                  |
|  3 | cbc  | 3,1,7                  |
+----+------+------------------------+


PB

-----

YL wrote:
Thansk Peter. find_in_set does the job. column 'options' is odd from
database design point of view. While from app developers' view, it's
much easier to retrieve the option id array from one column. where
the order matters (although this can be done with an option association
table).

mysql> select id,name from tt where find_in_set(7,substring(options,2,length(options)-2))<>0;
+----+------+
| id | name |
+----+------+
|  2 | bbc  |
|  3 | cbc  |
+----+------+
mysql> select * from tt;
+----+------+---------+
| id | name | options |
+----+------+---------+
|  1 | abc  | (1,2,3) |
|  2 | bbc  | (2,3,7) |
|  3 | cbc  | (3,1,7) |
+----+------+---------+

  ----- Original Message ----- 
  From: Peter Brawley 
  To: YL 
  Cc: mysql@lists.mysql.com 
  Sent: Saturday, September 03, 2005 12:34 AM
  Subject: Re: string->array question. ( 2 query questions)


  YL,

(2) the rows in the table are
mysql> select * from tt;
+----+------+---------+
| id | name | options |
+----+------+---------+
|  1 | abc  | (1,2,3) |
|  2 | bbc  | (2,3,7) |
|  3 | cbc  | (3,1,7) |
+----+------+---------+
<snip>
  
(4) My intention is a query like
mysql> select id,name from tt where 7 in options;
and expect
+----+------+
| id | name |
+----+------+
|  1 | abc  |
|  2 | bbc  |
|  3 | cbc  |
+----+------+
    

  
This is obviously wrong since column 'options' is of type varchar
but look at the values of options, they are like arrays. So my question
is how to fix query (4)? In other words, is there any function that
i can use to get the result illustrated in (4)?SQL doesn't do arrays, indeed keeping more than one value in one column breaks a basic rule of database design, but MySQL does have a column type for storing multiple values (http://dev.mysql.com/doc/mysql/en/set.html), so one solution available to you is to make your options column a SET column. If your options column values are ints, though, your database would be sounder and (not accidentally) your query would be much simpler if you were to project the options values out to a child table.
    

  PB

  -----

  YL wrote: 
My 1st question, can be understood after 3 steps below:
(1) Suppose we have a table defined as:
mysql> desc tt;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     |      |     | 0       |       |
| name    | varchar(32) | YES  |     | NULL    |       |
| options | varchar(32) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
(2) the rows in the table are
mysql> select * from tt;
+----+------+---------+
| id | name | options |
+----+------+---------+
|  1 | abc  | (1,2,3) |
|  2 | bbc  | (2,3,7) |
|  3 | cbc  | (3,1,7) |
+----+------+---------+
(3) the names of the matches of rows whoes id's are in an given array:
mysql> select name from tt where id in (2,3);
+------+
| name |
+------+
| bbc  |
| cbc  |
+------+
(4) My intention is a query like
mysql> select id,name from tt where 7 in options;
and expect
+----+------+
| id | name |
+----+------+
|  1 | abc  |
|  2 | bbc  |
|  3 | cbc  |
+----+------+

This is obviously wrong since column 'options' is of type varchar
but look at the values of options, they are like arrays. So my question
is how to fix query (4)? In other words, is there any function that
i can use to get the result illustrated in (4)?

----- Original Message ----- 
From: <SGreen@unimin.com>
To: "YL" <elim@pdtnetworks.net>
Cc: <mysql@lists.mysql.com>
Sent: Friday, September 02, 2005 2:29 PM
Subject: Re: 2 query questions


  Answers blended in....

"YL" <elim@pdtnetworks.net> wrote on 09/02/2005 04:18:27 PM:

    I've 2 basic questions. thanks for responses:

(1) Suppose i have a table with a varchar colum named options which
      stores
    option id's in the form
     "(id1, id2, ...., idk)", where id1,... are string representations
      of
    positive integers. Given an integer i,
     I want to fetch all ID's for the records in the table whoes
      'options'
    value when interprets as an array,
     contains integer i. What the query looks like?
      I do not understand your question. SQL does not have arrays. It has rows
and columns and tables and lots of other things but no arrays. Programming
languages have arrays, not SQL. Are you trying to get some data and
populate an array with it? That's not completely a MySQL question, it's
more of a question about how to create arrays from strings in your
particular language. However, since there are MANY people on this list, I
am sure someone will know the answer so please explain what it is you are
doing, what language you are using to do it with, what your data looks
like (use "SHOW CREATE TABLE", and explain what you want your results to
look like.

    (2) when using mysql command to query a value of type text, the
      displayed
    value always surrounded by lots
'-'s. My question is: How to get rid of those extras? (The following is
      a
    tail of display result:)
                          template = {
               b5 = resources/articles/_backups/subIndex_b5.template;
               gb = resources/articles/_backups/subIndex_gb.template;
           };
       };
   };


      |---------------------------------------------------------------------------
  --------------------------------------------

      --------------------------------------------------------------------------
    --
  -------------------------------------------

      --------------------------------------------------------------------------
    --
  -------------------------------------------

      --------------------------------------------------------------------------
    --
  -------------------------------------------

      --------------------------------------------------------------------------
    --
  -------------------------------------------
----------------------------------------------------------+

      When using the command line client, the headers, the data areas, and the
tails are each as wide as your data but they get "wrapped" to fit your
screen buffer. To make the wrapping effect go away in Windows, change the
Screen Buffer settings for your command shell window to something wide
enough to fit your results. One way you can get to the settings is by
right-clicking the window and picking "properties".

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



    

----------------------------------------------------------------------------
----



Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.10.18/86 - Release Date: 8/31/2005


  

------------------------------------------------------------------------------



  -- 
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:    http://lists.mysql.com/mysql?unsub=elim@pdtnetworks.net


------------------------------------------------------------------------------



  Checked by AVG Anti-Virus.
  Version: 7.0.344 / Virus Database: 267.10.18/86 - Release Date: 8/31/2005

  

No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.10.18/89 - Release Date: 9/2/2005