SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  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)

    Question Query Field, Explode, Compare, ReQuery (Or Something like that)

    Hey guys, I have a brain-buster here, I can't figure her out. Please help me I can't figure out the right logic or plan of action.

    What im trying to do:
    Im checking a field in every project to see if it the assign_users == session. If it does, I want to list all the projects where it contains this match.


    I first gather the Data
    :
    PHP Code:
    SELECT `assign_usersFROM `projects`; 
    And something like this is returned:
    PHP Code:
    $Row1_Example '1,5,3,7'// Note: This is just a VARCHAR from DB
    $Row2_Example '1,9,3,7';
    $Row3_Example '0,12'
    Then I compare the SESSION to each DIGIT Value, and Every Row.
    Im stuck here a bit, maybe use explode, but how can i loop all of them?
    PHP Code:
    If ($_SESSION['userID'] == explode(','$Row1_Example[]){
      
    $matches .= $Row1_example[0]; // or somethin 

    Then I take the match and grab the project:
    PHP Code:
    SELECT FROM `projectsWHERE `id`='$match' 
    My way doesn't work yet and I was wondering if there is an easier way to do this with MySQL?
    The problem is the explode of the array I think, I dont know how to explode it into a MySQL statement and loop through it.


    PS: I ordered Rudy Limebacks "Simply SQL" today, so I'll get better soon once it arrives

  2. #2
    John 8:24 JREAM's Avatar
    Join Date
    Sep 2007
    Location
    Florida
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Man Im an idiot, a way easier way to do it:

    Code MySQL:
    SELECT * FROM `projects` LIMIT $x SORT BY $y 
    WHERE `assign_user` LIKE '%$_SESSION[userID]%';

    I just needed a nap to refresh my mind

  3. #3
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,048
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    warning: if the usersID is 10 then 1000 will match so that won't work.

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,048
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    PHP Code:
    $sessionUserId $_SESSION['userID'];
    $matches = array();

    foreach(
    $rows as $row) {

        
    $ids explode(',',$row['assign_users']);
        
        foreach(
    $ids as $userID) {
        
            if(
    $userID == $sessionUserID) {
                
    $matches[] = // not sure what you are doing here?
            
    }
            
        }
        


  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,048
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    You could use in_array() or array_search() and set strict to false depending on what you need back.

    Honestly though, it looks as if that information should be factored out into a separate table:

    project_privileges
    -- project_id
    -- user_id
    -- primary key(project_id,user_id)

  6. #6
    John 8:24 JREAM's Avatar
    Join Date
    Sep 2007
    Location
    Florida
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh hey, I didnt see anyone posted on this I had figured it out.

    About the array being 10 I caught that also and changed the list to have the word add, before it so everything is surrounded by commas and it looks for: ',%item$,'

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    your solution won't scale -- the more rows you get, the slower the query will get

    of course, that might not matter to you...

    r937.com | rudy.ca | 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)
    I didn't think so! It will get really slow with tons of entries and that will be a pickle. I'll be reading and highlighting that precious book once it comes in the mail and try to figure something out, for now it's a temporary solution >=)

    Obviously LIMIT X wouldn't be good, it still scans a lot of rows and limits me to what Im trying to achieve in pagination. I actually can't comprehend any logical way to scale it with my limited knowledge of SQL at the moment, but will see in due time

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    what you want to do is redesign the part where you store multiple comma-delimited ids in a VARCHAR column

    instead, use another table, one row per user
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    John 8:24 JREAM's Avatar
    Join Date
    Sep 2007
    Location
    Florida
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thats probably the best idea, I didn't do it because I wanted there to be unlimited users assignable (but 10 would be okay), and it would be like:

    user_00 |
    user_01 |
    user_02 |
    user_03 | 3
    user_04 |
    user_05 |
    user_06 | 6
    user_07 |
    user_08 |
    user_09 | 4

    It would add more to the front-end.

    Does searching use a lotta juice, with the LIKE clause?

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you wouldn't store the users that aren't involved

    could you do a SHOW CREATE TABLE for me please?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    John 8:24 JREAM's Avatar
    Join Date
    Sep 2007
    Location
    Florida
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey Rudy,

    I don't totally know if this is what you ment, I haven't tried fiddling with a new table yet as I can't figure a way to do it how you say (but i wrote one below the project one) -- I need the users to be assigned to more than one project if they want.

    Code MySQL:
    projects | CREATE TABLE `projects` (
    `id` int(11) NOT NULL auto_increment,
    `title` varchar(255) NOT NULL,
    `desc` text NOT NULL,
    `price` varchar(255) NOT NULL,
    `progress` int(11) NOT NULL,
    `priority` int(11) NOT NULL,
    `status` int(11) NOT NULL,
    `assign_client` int(11) NOT NULL,
    `assign_users` varchar(255) NOT NULL,
    `date_created` varchar(255) NOT NULL,
    `date_modified` varchar(255) NOT NULL,
    `date_due` varchar(255) NOT NULL,
    PRIMARY KEY  (`id`)
    ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

    If I have a new table..
    Then okay.. I see how this might actually work now that i write it out and think harder..

    (I wrote in text editor lol)
    Code:
    Table: additional_users
    
    ID       |    PROJECT      |    USER     |    USER 2 
    -------------------------------------------------------------
    1        |    0            |    0        |    2        |
    2        |    1            |    3        |    1        |
    3        |    0            |    4        |    0        |
    4        |    3            |    5        |    1        |
    Because the MySQL would say,
    Check additional_users -- grab each user field, and allow them permission. (lol)
    I am not sure WHY I am thinking this LIMITS Users to only ONE additional project.


    Is that right?

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    remove assign_users from that table, and create the following additional table --

    CREATE TABLE project_users
    ( project_id INTEGER NOT NULL
    , user_id INTEGER NOT NULL
    , PRIMARY KEY ( project_id , user_id )
    );

    then instead of a value such as you had in assign_users like '1,5,3,7', you would have the following rows in project_users table (let's assume the project had a project_id of 106) --
    Code:
    project_users
    project_id  user_id
       106         1
       106         5
       106         3
       106         7
    this is called a relationship or association table, as it implements the many-to-many relationship between projects and users

    simple, innit


    p.s. besides assign_users, you also had several other columns in your table with VARCHAR(255), but this datatype is appropriate only for the title column -- the others need to be fixed
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    John 8:24 JREAM's Avatar
    Join Date
    Sep 2007
    Location
    Florida
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    WOW That makes much more sense. I like that a lot more Thanks man I am going to do it that way.

    The idea behind that opens a lot more improvements than I could have thought up!

  15. #15
    John 8:24 JREAM's Avatar
    Join Date
    Sep 2007
    Location
    Florida
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well for Price, I did
    price INT -- because it uses periods in the number, like 555.51
    the Dates use -- 44/44/44 slashes, so i thoguht they had to be var

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    prices should be DECIMAL, not VARCHAR

    dates should be DATE or DATETIME, not VARCHAR
    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
  •