...

View Full Version : Tricky PHP and MySQL issue



misfortune
01-15-2010, 07:57 AM
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! :thumbsup:

JAY6390
01-15-2010, 01:04 PM
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

misfortune
01-15-2010, 07:18 PM
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.

oracleguy
01-15-2010, 07:28 PM
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.

misfortune
01-15-2010, 08:12 PM
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.

JAY6390
01-15-2010, 08:27 PM
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

misfortune
01-15-2010, 08:36 PM
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.

JAY6390
01-15-2010, 09:07 PM
serialize() (http://www.php.net/serialize) unserialize() (http://www.php.net/sunerialize)
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

oracleguy
01-15-2010, 09:07 PM
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.

JAY6390
01-15-2010, 09:10 PM
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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum