SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    John 8:24 JREAM's Avatar
    Join Date
    Sep 2007
    Location
    Florida
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Explode or Something

    Happy Holidays

    I have a question because I don't know if this is possible in MySQL alone, if it is I might restructure some tables.

    1: If I have a row with this:
    Code MySQL:
    VARCHAR "1,2,3,4,5"

    2: I do a simple sub-query:
    Code MySQL:
    SELECT	*
    FROM	otherTable
    WHERE	id = 12
    AND			id NOT IN	(
     
    	SELECT lists FROM firstTable
    	WHERE id = 12
     
    )

    Q: Instead of comparing items NOT IN the string of "1,2,3,4,5" ---
    Is there a way to make those values separate so you can compare it with 1 then 2 then 3, etc?

    I realize this might cause 10x more work on MySQL's part, but I'll only be using this for one set of items at a time.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    could you redesign the table?

    storing multiple values in a single column breaks the rule of first normal form

    it makes your queries unnecessarily complex, not to mention slow (and hard to light)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    John 8:24 JREAM's Avatar
    Join Date
    Sep 2007
    Location
    Florida
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    On Christmas Eve the man of the hour lurks, HAHA.

    I am just wondering because, this seems like it's wasting space to have a table for "additional users".

    Code:
    +----+-------------+----------------+
    | id | assign_user | assign_project |
    +----+-------------+----------------+
    | 16 |           1 |              4 |
    | 26 |           3 |              1 |
    |  3 |           2 |              4 |
    | 29 |           4 |              1 |
    |  5 |           4 |              2 |
    | 28 |           2 |              1 |
    |  7 |           0 |              0 |
    |  8 |           2 |              0 |
    I guess its not a huge deal, I just thought there was a smarter way to do it -- haha (There probably is, but A_SIMPLE_LAYOUT and DESIGN are very different things!)

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    first of all, the additional users table should ~not~ have its own id column

    secondly, i'm having a real hard time connecting this table with what you posted in the beginning

    what are you actually trying to do?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    John 8:24 JREAM's Avatar
    Join Date
    Sep 2007
    Location
    Florida
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I was trying to select all the users that are not assigned to a given project so that the person doesn't attempt to add a user already in there from a drop down list (I also check this in the SQL).

    I only put the ID there because I thought I had to have a Primary Key. I've only used PK's and UNIQUE keys, and this has two rows that BOTH would have to be unique -- maybe I should combine those into 1

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you mean columns, not rows -- both columns together need to be the primary key

    but what about your comma-separated list of values? where's that?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by JREAM View Post
    (I also check this in the SQL).
    you shouldn't, you should just issue the INSERT and trap any error messages i.e. "duplicate key"

    twice as efficient (i.e. 100% better)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    John 8:24 JREAM's Avatar
    Join Date
    Sep 2007
    Location
    Florida
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay i will try doing this! Thanks

    I didnt make the column with the example "1,2,3,4' i was just thinking about it wehn i woke up

    cool i had no idea you cuold have 2 PKs LOL that will work nice
    Code MySQL:
    create table xxx (
     test int(11) not null,
     test2 int(11) not  null,
     primary key(test, test2)
    )

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by JREAM View Post
    cool i had no idea you cuold have 2 PKs LOL
    lol indeed

    you might need an additional index on (test2,test)

    for searches in the opposite direction
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •