2. PHPRunner General questions
  3. Selecting Multiple values for a user
This topic is locked

Selecting Multiple values for a user

 		5/3/2011 5:51:51 PM
PHPRunner General questions
greggk author

I just upgraded to the 5.3 version so that I could fix a problem with a project I had previously created. It works great, but now the users have asked for another feature <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=16953&image=1&table=forumtopics' class='bbc_emoticon' alt=':o' /> . I thought it wouldn't be a difficult thing to do. Right now the tables are users/vendors/products/notes

The products belong to certain vendors, and they are linked by VendorID. The users are linked to the vendors, by the same VendorID.

Previously, each user was assigned a vendor, and when they logged in, they could see only the products/notes associated with that vendor, that works great. But now they want some users to be assigned multiple vendors. The first thing I did was to change the dropdown list when selecting a vendor for a user, to the checkbox list. At first this didn't work. Each time I selected multiple vendors, it would only allow one. I figured that was because the VendorID was an INT. So I changed the VendorID for the users to varchar. That worked. Now I'm able to select multiple vendors. GREAT, however now that the user logs in, they can't see any products, eventhough when I look in the database, the user has both vendor IDs, (10,6) for example. What am I missing here? Here is a simple structure of the database

Vendor -> VendorID, VendorName,VendorAddress

Product -> ProductID, VendorID,ProductName,ETC

User -> UserID,UserName,UserPassword,VendorID
If anyone can point me in the right direction, it would be most appreciated <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=16953&image=2&table=forumtopics' class='bbc_emoticon' alt=':)' />

Thanks.
Admin 5/4/2011

You will need to implement this dependency (user assigned multiple vendors) manually.
You will need to pull a list of vendors available to current user from the database and modify SQL query on the fly accordingly.

Here is an example of doing this:

http://xlinesoft.com/phprunner/docs/modify_sql_query_on_the_fly.htm
greggk author 5/4/2011



You will need to implement this dependency (user assigned multiple vendors) manually.
You will need to pull a list of vendors available to current user from the database and modify SQL query on the fly accordingly.

Here is an example of doing this:

http://xlinesoft.com/phprunner/docs/modify_sql_query_on_the_fly.htm


I will see if I can come up with an sql query for this. At first I thought I would use the example of many to many relationships, and create a new table called vendor_users, but if I can do it this way, it would be better.
greggk author 5/6/2011

Ok, I tried the many to many relationships, and that is OK, but still doesn't do exactly what I want it to do.

I'll try to explain with a simple example.

I have 3 tables as shown here.


As you can see, Vendors is linked to Products via vendorID

I created the dynamic security page using the users table.

Under security I also did advanced settings for products, using "users can only see and edit their own data" by vendor ID.

This works great if the user is assigned one vendor only. When that user logs in, they can only see products associated with that vendor. As soon as I select multiple vendors for the users, when they log in, they no longer see any products.






I have not been able to figure a way around this using the security settings, creating another table to work many to many relationships. I know this can't be something complicated, it looks simple enough, just can't get around it?
Admin 5/6/2011

Greggk,
I'm not sure if I explained it well enough.
The standard security methods like Dynamic Pemrissions or Advanced Security won't work for you here. You need to implement BeforeSQLQuery event crafting a proper WHERE clause based on IDs of selected vendors for the current user.
Hope this makes sense.
greggk author 5/6/2011



Greggk,
I'm not sure if I explained it well enough.
The standard security methods like Dynamic Pemrissions or Advanced Security won't work for you here. You need to implement BeforeSQLQuery event crafting a proper WHERE clause based on IDs of selected vendors for the current user.
Hope this makes sense.


So, that is the only route? I guess I'll have to dig up some mysql books to see if I can come up with an appropriate statement to pull the multiple vendorIDs from the user table. <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=58145&image=1&table=forumreplies' class='bbc_emoticon' alt=':huh:' />
E
 		electromotive 5/7/2011



So, that is the only route? I guess I'll have to dig up some mysql books to see if I can come up with an appropriate statement to pull the multiple vendorIDs from the user table. <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=58148&image=1&table=forumreplies' class='bbc_emoticon' alt=':huh:' />


Hi Gregg. Although frustratingly simple, what you are encountering here is a classic relational database design problem. My suggestion is go back to basics of your database design and determine the SQL you'll need to use to establish the relationships you want, what the foreign keys are, etc. Build a data model on a napkin, or speak to an SQL database designer. You'll have be be able derive the relationships using SQL joins before you can use a tool like PHPR or any other database product. A little work on this now will save you nightmares in the future.