Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    New to the CF scene
    Join Date
    Jan 2010
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Question Tricky PHP and MySQL issue

    Hello there. Maybe you guys can help me.
    Here is my problem:

    I have a table with user info in it(id number, DoB, Last Login, ect.).
    I have several other (Application) tables with other info in it along with select user IDs from the first table(so the App can check to see if that user has access to it and save data for that user).

    I am looking for a way to keep track of what tables each user ID is entered into. I need to print out (to the logged in user) links to all the applications that use the tables that he/she is entered into.

    For example,
    UserID: 001 is entered into Tables App1, App3, and App5.
    UserID: 002 is entered into Tables App1, App2, and App4.
    When 001 logs in he needs to see links to App1, App3, and App5.

    Thanks in advance if anyone can help!

  • #2
    Regular Coder
    Join Date
    Dec 2009
    Location
    UK
    Posts
    495
    Thanks
    0
    Thanked 58 Times in 58 Posts
    I would actually keep a separate table just for this with
    userid tablename
    and then associate a username with a tablename in that table, so you would have three entries for user 001

    001 App1
    001 App3
    001 App5
    Then when you want to check which tables the user can access you can use this table, and also just list all the users tables from this using a select along with the userid in the where clause
    My site: JayGilford.com
    Resources:
    PHP Pagination Class | Getting all page links | Handling PHP Errors properly
    If you like a users help, show your appreciation with the rep and thanks buttons :)

  • Users who have thanked JAY6390 for this post:

    misfortune (01-15-2010)

  • #3
    New to the CF scene
    Join Date
    Jan 2010
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by JAY6390 View Post
    I would actually keep a separate table just for this with
    userid tablename
    and then associate a username with a tablename in that table, so you would have three entries for user 001

    001 App1
    001 App3
    001 App5
    Then when you want to check which tables the user can access you can use this table, and also just list all the users tables from this using a select along with the userid in the where clause
    Thanks for the reply! This was my original idea but I was thinking this could get kinda slow since I am expecting upwards of 5000 users and at least 500 applications. Am I right about this? I mean if 1000 people subscribe to all 500 applications that's a lot of info to store in separate rows. Of course I could be wrong and this method be faster than some how storing each accessible application in one cell. Maybe explode it and throw it into an array? I don't know, you tell me.

  • #4
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    Thanked 328 Times in 324 Posts
    Quote Originally Posted by misfortune View Post
    Thanks for the reply! This was my original idea but I was thinking this could get kinda slow since I am expecting upwards of 5000 users and at least 500 applications. Am I right about this? I mean if 1000 people subscribe to all 500 applications that's a lot of info to store in separate rows.
    Depends what you do with the data but in your example that is only half a million rows. But from what you said you are doing, Jay's solution should work just fine.
    OracleGuy

  • #5
    New to the CF scene
    Join Date
    Jan 2010
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by oracleguy View Post
    Depends what you do with the data but in your example that is only half a million rows. But from what you said you are doing, Jay's solution should work just fine.
    Thanks, I'll probably go with that method. Question though, at what number of rows does the DB start getting slow? All I'm doing with that table is printing a list of accessible applications to the user. For example, when the user logs in he will see the apps he has access to on the main homepage.

  • #6
    Regular Coder
    Join Date
    Dec 2009
    Location
    UK
    Posts
    495
    Thanks
    0
    Thanked 58 Times in 58 Posts
    In that case perhaps have a cache table with each users apps serialized in a single field, updated each time the apps they have access to change
    My site: JayGilford.com
    Resources:
    PHP Pagination Class | Getting all page links | Handling PHP Errors properly
    If you like a users help, show your appreciation with the rep and thanks buttons :)

  • #7
    New to the CF scene
    Join Date
    Jan 2010
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by JAY6390 View Post
    In that case perhaps have a cache table with each users apps serialized in a single field, updated each time the apps they have access to change
    Could you explain the serialized part? Never done anything like that.

  • #8
    Regular Coder
    Join Date
    Dec 2009
    Location
    UK
    Posts
    495
    Thanks
    0
    Thanked 58 Times in 58 Posts
    serialize() unserialize()
    Basically it just stores your array of values as a single line of text so that it can be saved to your database or whatever for future use. It's also use for objects etc and passing between sessions
    My site: JayGilford.com
    Resources:
    PHP Pagination Class | Getting all page links | Handling PHP Errors properly
    If you like a users help, show your appreciation with the rep and thanks buttons :)

  • #9
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    Thanked 328 Times in 324 Posts
    Quote Originally Posted by misfortune View Post
    Thanks, I'll probably go with that method. Question though, at what number of rows does the DB start getting slow? All I'm doing with that table is printing a list of accessible applications to the user. For example, when the user logs in he will see the apps he has access to on the main homepage.
    The slowness has nothing to do with the size of the db per-se. It has more to do with how many people on accessing the database at once and what kind of hardware the database server is running on.
    OracleGuy

  • #10
    Regular Coder
    Join Date
    Dec 2009
    Location
    UK
    Posts
    495
    Thanks
    0
    Thanked 58 Times in 58 Posts
    Yeah DBs are structured to be pretty damn efficient, so it's not going to have much effect on your processing speed unless you've a large number of people calling requests to the db at the same time
    My site: JayGilford.com
    Resources:
    PHP Pagination Class | Getting all page links | Handling PHP Errors properly
    If you like a users help, show your appreciation with the rep and thanks buttons :)


  •  

    Tags for this Thread

    Posting Permissions

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