populating fields with data from a pulldown menu using mysql query

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!
con2007Asked:
Who is Participating?
 
SissonConnect With a Mentor Commented:
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
 
rschoenbachCommented:
Are you looking for an ajax solution?
0
 
SissonCommented:
EDIT: sorry, it should be (in the dropdown box)
onchange="getUserData('this.options[this.selectedIndex].value')"
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
con2007Author Commented:
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
 
con2007Author Commented:
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
 
SissonCommented:
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
 
con2007Author Commented:
no problem.. just changed it and am getting another error...

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

0
 
SissonCommented:
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
 
con2007Author Commented:
yup... that's what I have... but still giving a different error:

'document.getelementbyid.user_name' is null or not an object'
0
 
con2007Author Commented:
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
 
SissonCommented:
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
 
con2007Author Commented:
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
 
con2007Author Commented:
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
 
SissonCommented:

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
 
con2007Author Commented:
there doesn't seem to be an alert box anymore.  Just that the fields are filled in with "undefined"
0
 
con2007Author Commented:
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
 
con2007Author Commented:
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
 
con2007Author Commented:
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
 
con2007Author Commented:
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
 
SissonCommented:
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
 
con2007Author Commented:
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
 
con2007Author Commented:
just curious... what does the &@ do in the beginning of the echo statement?
0
 
SissonCommented:
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
 
con2007Author Commented:
OK, I'll try that on Monday and get back to you... enjoy your vacation!!!
0
 
con2007Author Commented:
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
 
con2007Author Commented:
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
 
con2007Author Commented:
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
 
con2007Author Commented:
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
 
SissonCommented:
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
 
con2007Author Commented:
Awesome!  Thanks Sisson, you have helped me tremendously.  I really appreciate all the help!!

Hope you enjoy your vacation!!
0
 
con2007Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.