List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:September 3 2005 4:31pm
Subject:Re: string->array question. ( 2 query questions)
View as plain text  
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
Thread
Trouble with revoke allScott Haneda2 Sep
  • Re: Trouble with revoke allGleb Paharenko2 Sep
    • Re: Trouble with revoke allScott Haneda2 Sep
      • Re: Trouble with revoke allMichael Stassen2 Sep
        • Re: Trouble with revoke allScott Haneda3 Sep
          • Re: Trouble with revoke allMichael Stassen3 Sep
            • Re: Trouble with revoke allScott Haneda3 Sep
              • Multiple query execution (probably OT)Alvaro Cobo3 Sep
                • Re: Multiple query execution (probably OT)Gleb Paharenko3 Sep
                • How to migrate from V4.0.23 to V5?Siegfried Heintze4 Sep
                  • Re: How to migrate from V4.0.23 to V5?Gleb Paharenko5 Sep
      • Re: Trouble with revoke allGleb Paharenko2 Sep
        • Re: Trouble with revoke allScott Haneda3 Sep
    • 2 query questionsYL2 Sep
      • Re: 2 query questionsJason Pyeron2 Sep
      • Re: 2 query questionsSGreen2 Sep
        • string->array question. ( 2 query questions)YL3 Sep
          • Re: string->array question. ( 2 query questions)Peter Brawley3 Sep
        • Re: string->array question. ( 2 query questions)YL3 Sep
          • Re: string->array question. ( 2 query questions)Peter Brawley3 Sep