?
Solved

populating fields with data from a pulldown menu using mysql query

Posted on 2007-08-09
31
Medium Priority
?
450 Views
Last Modified: 2013-12-12
What I am trying to do is first, pull user info from a database that contains their username, firstname, lastname, password and role.  Using this data, I have created a pulldown menu which will allow a user to select the person's username.  Based on the username that is selected, the remaining form fields will be populated with the corresponding data.  Once the data is loaded, the user will then have the option to modify any fields and save the modification, or delete the user, based on two separate buttons.  Here is what I have so far (in PHP):

<?php
session_start();
...
$query = "SELECT username, firstname, lastname, role FROM userdb";
$all_users = mysql_query($query,$mysql);
?>
...
some html
...
Select a user from the pulldown:
<form method='POST' action='moduser.php'>
<select name='userid'>
<?php
     while ($data = mysql_fetch_array($all_users, MYSQL_ASSOC)) {
          echo "<option value='{$data['username']}'>{$data['firstname']},{$data['username']}</option>\n";
     }
?>
</select>
Firstname:
<input type='text' name='firstname'>
Lastname:
<input type='text' name='lastname'>
Username:
<input type='text' name='username'>
Password:
<input type='text' name='password'>
Role:
<select name="role">
  <option value="user">User</option>
  <option value="admin">Admin</option>
</select>
<input type='buton' value='Modify'>
<input type='buton' value='Delete'>
</form>
...
</html>

One last note, the role is based on another pulldown menu (to prevent typos).  Is there a way to also select the appropriate selection box, based on what role the user is currently set to?

Thanks!
0
Comment
Question by:con2007
  • 21
  • 9
31 Comments
 
LVL 4

Expert Comment

by:rschoenbach
ID: 19664658
Are you looking for an ajax solution?
0
 
LVL 3

Accepted Solution

by:
Sisson earned 2000 total points
ID: 19664858
sure, create another file called userAJAX.php (warning- lots of code ahead!!)

--in the file---
if (isset($_GET['user'])) {
      $sql = 'SELECT * FROM userdb WHERE username=' . $_GET['user'];
      $q = mysql_query($sql);
      $row = mysql_fetch_assoc($q);

      echo "&@username=" . $row['username'];
      echo "&@firstname=" . $row['firstname'];
      echo "&@lastname=" . $row['lastname'];
      echo "&@password=" . $row['password'];
      echo "&@role=" . $row['role'];
}
---end of file--

ok, the rest of this requires the following AJAX scripts.

---IN A JAVASCRIPT FILE---
function getAjaxObject() {
      var ao;
      try {
            ao = new XMLHttpRequest();
            ao.overrideMimeType('text/html');
      }catch(e) {
            try {
                  ao = new ActiveXObject("Msxml2.XMLHTTP");
            }catch(e) {
                  try {
                        ao = new ActiveXObject("Microsoft.XMLHTTP");
                  }catch (e) {
                        alert("Your browser cannot support this page!");
                        return false;
                  }
            }
      }
      
      return ao;
}

function ajax(url, parsefunc, addParams) {
      xmlhttp = getAjaxObject();
      xmlhttp.open("POST", url, true);
      
      xmlhttp.onreadystatechange = function() {
            if (xmlhttp.readyState == 4) {
                  parsefunc(xmlhttp.responseText, addParams);
            }
            
      }
      xmlhttp.send("");
}

function ajaxResponseArray(res) {
      var rval = Array();
      var ns;
      var q = res.split(/&@/g);
      
      for (i in q) {
            if (q[i].indexOf("=" != -1)) {
                  ns = q[i].indexOf("=");
                  rval[q[i].substr(0, ns)] = q[i].substr(ns+1, q[i].length);
            }
      }
      
      
      return rval;
}

function getUserData(username) {
      ajax("userAJAX.php?user=" +username, function(res) {
            var row = ajaxResponseArray(res);
            document.getElementById['user_name'] = row['username'];
            document.getElementById['user_firstname'] = row['firstname'];
            document.getElementById['user_lastname'] = row['lastname'];
            document.getElementById['user_password'] = row['password'];
            document.getElementById['user_role'].selectedIndex = ((row['role'] == 'admin') ? 1 : 0);
      });
}
---END OF JAVASCRIPT---

ok, now in your dropdown box's tag (the one with the users), you are going to add the following attribute:
onchange="getUserData(this.options[this.selectedIndex].value)"

and in your field objects that are to be filled, you need to give them the "id" attributes:
user_name
user_firstname
user_lastname
user_password
user_role

hope that helps (=
0
 
LVL 3

Expert Comment

by:Sisson
ID: 19664885
EDIT: sorry, it should be (in the dropdown box)
onchange="getUserData('this.options[this.selectedIndex].value')"
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:con2007
ID: 19664943
rschoenbach:  yes, as I am under the impression that ajax is the only way to do this without refreshing the website.

Sisson: thanks for this code - I'm reviewing it now to try and follow...  be back in a sec...
0
 

Author Comment

by:con2007
ID: 19666081
Hello Sisson:

I seem to be getting an error on this line:

document.getElementById['user_name'] = row['username'];

error is:

"object doesn't support this property or method"

any thoughts?
0
 
LVL 3

Expert Comment

by:Sisson
ID: 19666089
oh shoot.  it should be:

document.getElementById['user_name'].value = row['username'];

sorry, you are going to have to add the .value to the rest of them
0
 

Author Comment

by:con2007
ID: 19666476
no problem.. just changed it and am getting another error...

'document.getelementbyid.user_name' is null or not an object

0
 
LVL 3

Expert Comment

by:Sisson
ID: 19666633
your function should look like this now:

function getUserData(username) {
      ajax("userAJAX.php?user=" +username, function(res) {
            var row = ajaxResponseArray(res);
            document.getElementById['user_name'].value = row['username'];
            document.getElementById['user_firstname'].value = row['firstname'];
            document.getElementById['user_lastname'].value = row['lastname'];
            document.getElementById['user_password'].value = row['password'];
            document.getElementById['user_role'].selectedIndex = ((row['role'] == 'admin') ? 1 : 0);
      });
}
0
 

Author Comment

by:con2007
ID: 19666654
yup... that's what I have... but still giving a different error:

'document.getelementbyid.user_name' is null or not an object'
0
 

Author Comment

by:con2007
ID: 19666681
Sisson:

I noticed the end parenthesis is after all the document.getElementById's.  Should that instead be before it as such? :

function getUserData(username) {
      ajax("userAJAX.php?user=" +username, function(res)) {  // **parenthesis moved to end of this line**
            var row = ajaxResponseArray(res);
            document.getElementById['user_name'].value = row['username'];
            document.getElementById['user_firstname'].value = row['firstname'];
            document.getElementById['user_lastname'].value = row['lastname'];
            document.getElementById['user_password'].value = row['password'];
            document.getElementById['user_role'].selectedIndex = ((row['role'] == 'admin') ? 1 : 0);
      }; // **parenthesis removed from this line**
}

or is the ajax funtion taking all those parameters?  
0
 
LVL 3

Expert Comment

by:Sisson
ID: 19667038
Oh wow, i am sorry.  the getelementbyid syntax was incorrect.  i fixed that. Anyway...
the rest of the syntax in there is right.  It is an anonymous function, passing a function variable to something used inside of the script.  It is the correct syntax- this should be the final revision of this function:

function getUserData(username) {
      ajax("userAJAX.php?user=" + username, function(res) {
            var row = ajaxResponseArray(res);
            document.getElementById('user_name').value = row['username'];
            document.getElementById('user_firstname').value = row['firstname'];
            document.getElementById('user_lastname').value = row['lastname'];
            document.getElementById('user_password').value = row['password'];
            document.getElementById('user_role').selectedIndex = ((row['role'] == 'admin') ? 1 : 0);
      });
}
0
 

Author Comment

by:con2007
ID: 19667601
well, we're definitely closer...  that didn't error out, but mozilla didn't load anything when I selected a name.  However, I'm using Eclipse (with php plugins) and the internal browser at the bottom added this to the username, firstname, etc fields:

".$row['firstname'];      echo "
".$row['username'];      echo "
etc...

instead of the actual values.  I'm not sure where it's getting the echo either?  Do you think the embedded php is causing it to do this?
0
 

Author Comment

by:con2007
ID: 19667704
oh wait... I just realized that I put the javascript file into a sub-directory.  So now, I went back and changed this line:
    ajax("userAJAX.php?user=" +username, function(res) {

to this:

    ajax("../userAJAX.php?user=" +username, function(res) {

OK, now I'm getting "undefined" in the fields...
0
 
LVL 3

Expert Comment

by:Sisson
ID: 19667807

function getUserData(username) {
      ajax("userAJAX.php?user=" + username, function(res) {
            var row = ajaxResponseArray(res);
            alert(res);
            document.getElementById('user_name').value = row['username'];
            document.getElementById('user_firstname').value = row['firstname'];
            document.getElementById('user_lastname').value = row['lastname'];
            document.getElementById('user_password').value = row['password'];
            document.getElementById('user_role').selectedIndex = ((row['role'] == 'admin') ? 1 : 0);
      });
}

tell me what the alert box says.
I will try to answer this ASAP... i am going on vacation tomorrow and dont know if the hotel with have internet access or not.  If not,  the most i can tell you is that the php file probably isnt echoing correctly.  If that isnt the error, then i am afraid i dont know what is.  you will have to tell me what that alert() says.
0
 

Author Comment

by:con2007
ID: 19667910
there doesn't seem to be an alert box anymore.  Just that the fields are filled in with "undefined"
0
 

Author Comment

by:con2007
ID: 19667929
ugh... how long will your vacation be? I wouldn't feel good asking you to reply on your vaca.  I know how much we need that time off!!!

 If you cannot get to it, I'll just wait until you get back, unless someone else comes along...

I guess I'll have to work on the other pages until we can sync back up again...

Thanks and have a great time off!!
0
 

Author Comment

by:con2007
ID: 19667972
oh wait... sorry... I just noticed your alert window...

here is what the alert says: (I've changed some of the fields from what was originally posted)

if (isset($_GET['user'])) {
      $sql = 'SELECT * FROM userdb WHERE username='.$_GET['user'];
      $q = mysql_query($sql);
      $row = mysql_fetch_assoc($q);

      echo "&@username=" . $row['username'];
      echo "&@firstname=" . $row['firstname'];
      echo "&@lastname=" . $row['lastname'];
      echo "&@password=" . $row['password'];
      echo "&@role=" . $row['role'];
}

Then, an error shows:

'document.getElementById(...)' is null or not an object
0
 

Author Comment

by:con2007
ID: 19668016
ok, I found a missing id name for password, so no error, but the popup looks the same and the fields now have these in them again:

" . $row['firstname'];      echo "
" . $row['lastname'];      echo "

etc...

but this time the pathnames are ok.

0
 

Author Comment

by:con2007
ID: 19668044
call me crazy, but I think there's something up with the function ajaxresponsearray().  As I've just noticed that the output in each field is swapping the echo statements around.  In the userajax.php file, this is one of the lines:

echo "&@username=" . $row['username'];

then in the field (after you select a name), this populated in it:

" . $row['username'];      echo "

not sure why it's swapping it around like that.  I apologize as I am new with php regex and I cannot seem to follow all you have done in your functions...


0
 
LVL 3

Expert Comment

by:Sisson
ID: 19668482
Haha.  your error is that your php file isnt processing.  This may sounds stupid, but did you name the file after a .php AND is your PHP server running?


http://www.php.net/  is where the PHP engine resides.
or you could try: http://www.apachefriends.org/en/xampp.html
0
 

Author Comment

by:con2007
ID: 19670427
AH!  I did forget to put \<?php ?\> in the contents of the file (beginning and end)... doh!!!

OK, so now I've added it as a php file but am getting the following popup:

&@username=&@firstname=$@lastname=&@role=

but nothing populates the fields yet...
0
 

Author Comment

by:con2007
ID: 19670854
just curious... what does the &@ do in the beginning of the echo statement?
0
 
LVL 3

Expert Comment

by:Sisson
ID: 19675819
the &@'s are variable dividers... i chose those because of the rare change that they would appear in your string.  I should probably come up with a better method that only splits on a &@(.*?)=, but not now.  The error now is probably that it is searching for a user that doesnt exist, and therefore has no data to echo.  Try testing the file itself (it doesnt need to be accessed through AJAX) and seeing if there is a mysql error or a blank query.

try var_dump()'ing $row.
0
 

Author Comment

by:con2007
ID: 19676170
OK, I'll try that on Monday and get back to you... enjoy your vacation!!!
0
 

Author Comment

by:con2007
ID: 19687032
OK, I've had to make a few changes to the code to get it to work.  For some reason mysql_fetch_assoc just wasn't giving me anything.  When I changed it to mysql_fetch_array, it worked fine.

Now, I know the html code works (and javascript) b/c I hard-coded the username into the SELECT statement and it did populate all the fields correctly.  However, when I put _GET['userid'] or _POST['userid'], it does not pass the variables, and thus I am getting undefined on the html form page when I select a name from the dropdown menu.  Is this because it's calling a javascript function (onclick) and not actually submitting the entire form to the userajax.php file?  How can I grab the value from that pulldown menu and pass it correctly to userajax.php.  The method above does not appear to be working...
0
 

Author Comment

by:con2007
ID: 19687087
oh wait... I see you're doing a GET from your javascript... let me go through that again and see where it's bombing....
0
 

Author Comment

by:con2007
ID: 19687151
OK, so I echo'ed what the value of $_GET['user'] is in that alert box and this is what came out:

this.options[this.selectedIndex].value&

why is is passing the javascript line instead of it's actual value?
0
 

Author Comment

by:con2007
ID: 19687305
OK, I figured out what was going on... you have to remove the tick marks (') around

this.options[this.selectedIndex].value&

and it grabbed everything and placed it in it's appropriate fields.

This works great now!!  Thank you very much!!!!

One last question before I award the points and close this session.  I noticed you managed to get the appropriate pulldown value for Role.  Now I've only given 2 options (admin and user), what if I had, say, 15 different types of roles.  How would you adjust your code for this?  I'm curious to know b/c I cannot figure out how you did your code for that.

Many Thanks!


0
 
LVL 3

Expert Comment

by:Sisson
ID: 19691803
Well, i was able to do that because there were only two options.  If you wanted to do more, you would have to use another function that maps "role" strings to select-field indexes. Each option value in the SELECT box has an index 0-n, where n is the number of indices... kind of like an array.  The way i had it set up is that if it was "admin", it would select 0, otherwise it would select 1.

however to do this with more fields, you could do something like...

function role_to_index(rolename) {
  if (rolename == "admin") {
    return 0;
  }else if (rolename == "user") {
    return 1;
  }else if (rolename == "mod") {
    return 2;
  }etc...
}

and then instead of
document.getElementById('user_role').selectedIndex = ((row['role'] == 'admin') ? 1 : 0);

you would do
document.getElementById('user_role').selectedIndex = role_to_index(row['role']);
0
 

Author Comment

by:con2007
ID: 19692199
Awesome!  Thanks Sisson, you have helped me tremendously.  I really appreciate all the help!!

Hope you enjoy your vacation!!
0
 

Author Comment

by:con2007
ID: 19703541
Sisson,

I don't know if you're still there, but I just realized that this only works in Internet Explorer.  It doesn't work in Firefox.  Could you advise me on what changes need to take place for this to work in Firefox?

Thanks!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question