SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Possible to select result set as a comma delimited list?

    Hey all, I am trying to optimize my code, I am currently using MySQL but I posted this in here because I would prefer something that would work with multiple SQL databases (e.g. PostGreSQL, MSSQL). What I am trying to do is return a result set that is comma delimited. For instances, I am selecting all the role_ids of a particular user. I would like to store those role_ids in a variabe as a comma separated list so I can use it in a WHERE IN statement instead of having to do a loop and use WHERE role_id =. Is this possible while working with the above databases and MySQL or if not, is there a MySQL only solution?

    Thanks so much,
    Darren
    Have a good day.

  2. #2
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,576
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Could you do this? Yes. Could you poke yourself in the eye with a sharp stick? Yes.

    Just about any language I can think of has some sort of "find in array" function which is at least as, if not more, efficient than "find in string" for small datasets. Nevermind that arrays are much, much more flexible than some csv.

  3. #3
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes but the array the data is from is multidimensional.
    Have a good day.

  4. #4
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Never mind, I have created a multidimensional in_array function.
    Have a good day.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Darren884 View Post
    I would like to store those role_ids in a variabe as a comma separated list so I can use it in a WHERE IN statement instead of having to do a loop and use WHERE role_id =.
    what you're searching for is a join query, most likely

    dead simple sql, if the tables are designed properly

    constructing comma-delimited strings is not efficient in sql, so perhaps were you thinking of doing consecutive queries?

    a join would still be better

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy, after thinking a while I wen d'oh. Thanks so much here is something like I did:
    Code:
    SELECT * 
    FROM gp_users_roles
    LEFT JOIN gp_permissions
    ON gp_permissions.role_id = gp_users_roles.role_id
    WHERE (module = '' OR module IS NULL)
    AND (controller = '' OR controller IS NULL)
    AND(method = '' OR method IS NULL)
    AND gp_users_roles.role_id = 1;
    Have a good day.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    could you please indicate which table the module, controller, and method columns are in?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile

    Hi Rudy, here is the final actual query:

    Code SQL:
    SELECT COUNT(*)
    FROM `gp_users_roles`
    JOIN `gp_permissions` ON `gp_permissions`.`role_id` = `gp_users_roles`.`role_id`
    WHERE (module = '' OR module IS NULL)
    AND (controller = 'users' OR controller IS NULL)
    AND (method = 'dashboard' OR method IS NULL)
    AND `gp_users_roles`.`user_id` = 2

    I didn't specify where module, controller, and method are coming from because I am using an active record class with a database prefix and it will defeat the database prefix cause because of the way I have to write those WHERE with OR statements in them. I can assure you though they are not ambiguous.
    Have a good day.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    sorry, your explanation made no sense to me whatsoever, but the fault is entirely mine, i assure you

    the reason i asked is because i think some of those conditions actually belong in the ON clause rather than the WHERE clause
    r937.com | rudy.ca | 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
  •