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@stripped
> 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@stripped>
>To: "YL" <elim@stripped>
>Cc: <mysql@stripped>
>Sent: Friday, September 02, 2005 2:29 PM
>Subject: Re: 2 query questions
>
>
> Answers blended in....
>
>"YL" <elim@stripped> 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=1
>
>
>------------------------------------------------------------------------------
>
>
>
> 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
>
>
Attachment: [text/html]
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.10.18/89 - Release Date: 9/2/2005
