Limiting dropdown options based on userID

Question

I am asking this question because I'm unsure of the best way to solve my problem.

Problem: I have a pre-populated drop down list of 1,000 or so numbers. I need to limit which numbers appear in the drop down based on which user is using the drop down.

Solution I thought of:

1. Hide all numbers with jQuery
2. Use jQuery / AJAX to call a database, passing a userID
3. DB returns a list of values based on the userID
4. Show options in drop down that have same values as numbers returned from the DB

Lets say this is my HTML:

<select>
<option>Please Select..</option>
<option value="101"> CC 101 </option>
<option value="102"> CC 102 </option>
<option value="103"> CC 103 </option>
<option value="104"> CC 104 </option>
<option value="105"> CC 105 </option>
<option value="106"> CC 106 </option>
</select> 

And this is my DB table:

=======================
|   User1    |  101   |
|   User2    |  101   |
|   User2    |  102   |
|   User2    |  103   |
|   User3    |  103   |
=======================

I need to figure out, for example, how to pass user2 and then return 101,102,103.

I only know basic js/jQuery, and I am not very experienced with DBs, so I am welcome to any suggestions that can help me accomplish my end goal.

EDIT/NOTE: As dumb as this sounds.... Security is not a huge deal here. This will be used on an internal company website, where if a user felt the need to hack around and select a different number, it really wouldn't matter that much. I also don't foresee any of the employees of the company having the desire/need/want to select a different option than they are allowed.

Also, the list must be pre-populated then numbers hidden. Its the way the platform I am using is set up, so I have to use show/hide, or something similar.

Answer 1

I would go only with steps 2-3 of your approach; however, I would not store the numbers in the way you showed. A better approach would be to store them in a table called user_value -or something like that-:

user_id  |  value
---------+-------
user1    |  101
user1    |  102
user2    |  101

Just because you can then easily add/remove/update values in the future as opposed to having to parse the comma-delimited value.

I would avoid using jQuery to simply "hide" things because Javascript can be disabled by the user and he may end up submitting whatever value he wants (visible or invisible) - Never trust user input.

In conclusion do this:

1. Use jQuery / AJAX to call a database, passing a userID
2. DB returns a list of values based on the userID
3. populate the dropdownlist with the values returned from the database.
4. Validate the form on the server side to make sure that the value submitted is present in the user_value table.

Answer 2

If that's the example Database Table and an example of a Select element. Then I think the best method would be to not write anything on your own and just let the Database choose what to share and where to share.

Here we go with the coding. I will try to explain what I am writing and how I am writing; since you're a newbie :-)

HTML code for you

The HTML code for your job, would be simple as this

<select name="someName">
  <!-- No options please! -->
</select>

Getting the UserId

Now, once the user has logged in. Try to get the UserId by any one of the following method.

jQuery

To use jQuery you will need to be using the Server's generated value, because jQuery cannot interfere the Server Requests and code. So, you can create a simple hidden input and give it the value of the current logged in user. Here is the example

<input type="hidden" id="userId" value="@WebSecurity.CurrentUserId" />

Now, using jQuery you can get the value for this input. Here would be the code for that

var userId = $('#userId').val();

ASP.NET

To use ASP.NET, you don't do anything. All you do is you use the Built-in method of the ASP.NET as

WebSecurity.CurrentUserId;

and it would give you an integer value. Which would be the userId of the currently logged in user.

Usage of WebSecurity

The WebSecurity as the following link states, is a Class of data. Which can be used in your application, to lessen down the code and to help you get the User's properties at a better rate.

This class is used as a value for the variable, or as a parameter. This method would return a particular user's property. For example, you can get User's UserId, his UserName, his CreateDate, or you can use WebSecurity to Login a user, or Log him out.

Here would be an example of using WebSecurity. Just the way you create a variable in jQuery and get its value. You use this method and get the value.

jQuery Method

var userId = $('someElement').val(); 
/* get the value of some element as userId */

WebSecurity.CurrentUserId Method

var userId = WebSecurity.CurrentUserId;

Then you can use it inside the Database query,

db.Query("SELECT * FROM UserProfile WHERE UserId =@0", userId);

or, write it inside the document

Response.Write(userId);

Or do what ever you want to do. You can learn the syntax of the Class and other stuff in the links of MSDN. :-)

http://msdn.microsoft.com/en-us/library/webmatrix.webdata.websecurity(v=vs.111).aspx

Sending the Ajax request

Now send the Ajax request. If you know, then wonderfull! If not, then here is the example of the Ajax request to be sent

$.ajax({
  url: 'page_url',
  data: 'userId=' + userId, // userId that you got from input
  success: function (data) { // note the data parameter
    /* write in the select statement */
    $('select').html(data); // usage of data parameter
  }
});

Once it is done. It would update the Select element's options. But wait, what would it add to it?

It you, who control it too. You edit the server-side code to the following.

Getting the Data from Database.

If you're a newbie to the Databases and ASP.NET you need to first learn a bit.

http://www.asp.net/web-pages/tutorials/data/5-working-with-data

Ok, you can learn that a bit later too. I will still explain all my code to you. :-)

So, for Database you first need to create a Connection to the database and then you can search its tables and other content. Here is the code

var db = Database.Open("databaseName"); // Open a connection
var userId = Request.QueryString["userId"]; // get userid from ?userId=int
var selectQuery = "SELECT * FROM table_name WHERE UserId =@0"; // Query
var results = db.Query(selectQuery, userId); // Get data in a variable

After getting all these values, all that you need to do is to create a Response, to be sent to the client.

I hope, you're using Web Pages technology. Good! You're one step safer than others here.

Just press Page Down and move down to the last few lines and create a new Div element

<div>
  <!--empty div element -->
</div>

Now, write an if else statement in it and create a Response which would be added to the select element.

P.S Other method (Actual method) of giving out a Response, is using the Actuall HttpResponse class and then giving the values to it. Like this

Response.Write("Hello World!");

If you write the above line, it would add this string to your select statement (although illegal) but that would make you understand its usage.

http://msdn.microsoft.com/en-us/library/system.web.httpresponse.aspx