jquery/php/mysql - populate form fields

I would like to be able to populate my form fields with data from a mysql database but am having no luck. How can I go about this? I can't find a way to get the data from mysql and insert it into the .val() function.

              $(function() {
            $('.updbutton').live('click',function() {
                  var upd_id = $(this).attr("id");
                  var $query = "select * from customers WHERE custid=" + upd_id;

                  $("#custid").val();
                  $("#company").val();
                  $("#contact").val();
                  $("#address").val();
                  $("#city").val();
                  $("#state").val();
                  $("#zip").val();
                  $("#country").val();
                  $("#phone").val();
                  $("#email").val();
            });
      });

<a href='#' id='".$row->custid."' class='updbutton'>Update</a>
LVL 16
JF0Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Julian HansenCommented:
What are you actually trying to do?

Database is not directly accessible from Javascript - you need to go through an intermediate server side script using AJAX to get the data.

Describe what it is you want to do and we can take it from there.
0
JF0Author Commented:
I want to be able to populate my form fields with data from my database when a link is clicked. The ID of the link contains the ID of the record I want to pull.


0
Julian HansenCommented:
Ok, then you need to either do a post back to a script that re-gen's the page with the variables in after querying the dB or else use AJAX to make the call to a script that retrieves data from DB and returns it to your javascript code which you then use to populate your forms.

Post back here if you need assistence with this - I am signing off now but will pick up tomorrow
0
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

hieloCommented:
you need a script on the server to execute the query and send back the results. Try:
$(function() {
            $('.updbutton').live('click',function() {
                  var upd_id = $(this).attr("id");

			   $.ajax({
			   		url:'dataRetriever.php'
					,data:{"custid":upd_id}
					,type:"POST"
					,dataType:"json"
					,success:function(result){
						for( k in result )
						{
							$('#'+k).val( result[k] );
						}
					}
			   });
			   /*
                  $("#custid").val();
                  $("#company").val();
                  $("#contact").val();
                  $("#address").val();
                  $("#city").val();
                  $("#state").val();
                  $("#zip").val();
                  $("#country").val();
                  $("#phone").val();
                  $("#email").val();
			   */
            });
      });
	 
dataRetriever.php
<?php
if( isset($_POST) && !empty($_POST) )
{
	$data=array(	"custid"=>""
				,"company"=>""
				,"contact"=>""
				,"address"=>""
				,"city"=>""
				,"state"=>""
				,"zip"=>""
				,"country"=>""
				,"phone"=>""
				,"email"=>""
			);
	//connect to db first AND select the db
	//...
	
	$sql = "SELECT " . implode(", ", array_keys($data)) . " FROM customers WHERE custid=" + mysql_real_escape_string($_POST['custid']);
	$result = mysql_query() or die( mysql_error() );
	if(mysql_num_rows($result)==1)
	{
		$data=mysql_fetch_assoc($result);
	}
	echo "{";
	$t="";
	foreach($data as $k=>$v){
		$t .= sprintf(',"%s":"%s"', str_replace('"',"\\" . '"',$k),   str_replace('"',"\\".'"',$v));		
	}
	echo substr($t,1);
	echo "}";
}
exit;
?>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JF0Author Commented:
heilo I have spent an hour last night and an hour this morning to no avail. Can you please double check the code or offer some advice on debugging?
0
JF0Author Commented:
I have managed to get a response from the dataretriever page. This is the result:

{"custid":"58","company":"Mr. Plow","contact":"Homer","address":"742 Evergreen Terrace","city":"Springfield","state":"XX","zip":"99999","country":"United States","phone":"555-PLOW","email":"chunkylover53@aol.com"}

However it does not appear to return 'k'.
0
Julian HansenCommented:
In the PHP file try replacing these lines

      echo "{";
      $t="";
      foreach($data as $k=>$v){
            $t .= sprintf(',"%s":"%s"', str_replace('"',"\\" . '"',$k),   str_replace('"',"\\".'"',$v));            
      }
      echo substr($t,1);
      echo "}";


with this:

$encoded = json_encode($status);
die($encoded);
0
Julian HansenCommented:
Damn - typo

change last two lines to

$encoded = json_encode($data);
die($encoded);
0
JF0Author Commented:
Ok, I got it figured out. Here it goes:

Problem 1:  $result = mysql_query() or die( mysql_error() ); was obviously missing a parameter to mysql_query(). I caught this right off the bat when I was adding my connection info. No biggie.

Problem 2: The url in .ajax() is case sensitive. I named my file dataretriever.php instead of dataRetriever.php. This took a bit longer to figure out.

Problem 3: The killer. $sql = "SELECT " . implode(", ", array_keys($data)) . " FROM customers WHERE custid=" + mysql_real_escape_string($_POST['custid']); That plus sign should be a dot.


0
hieloCommented:
JF0, my apologies for the late response, I just noticed your question [I do have quite a few participated questions :)]

>>Problem 1:
Oops, hopefully the "..or die(...)" revealed the problem right away

>>Problem 2: The url in .ajax() is case sensitive.
Ouch.

>>Problem 3: The killer
It seems I mixed the javascript syntax (which uses "+" to concatenate strings) with php syntax :(
0
JF0Author Commented:
hielo - your mistakes helped me learn more than I would have without them. Except for problem 3, that was just mean. =]

can you help me understand this:
for( k in result )
                                    {
                                          $('#'+k).val( result[k] );
                                    }

I am doing some debugging and when I do alert(k) I get an incrementing number. When I do alert(result[k]) I get the corresponding character sequence from the result.

So I do not understand how $('#'+k).val( result[k] ) is populating valid results. I would expect alert(k) to return for example 'company' and alert(result[k]) to return 'mcdonalds'.
0
JF0Author Commented:
If you could also help me with one more thing I am trying to do I would appreciate it. Using the same methodology as you provided I am trying to update some html.

success: function(result){
$('#group_58 #record_company').html( "test" );
}

This works but of course I want to update several things just as before so I try this:

success: function(result){
                        for( k in result )
                        {
                            $('#group_'+custid + '#record_'+k).html( result[k] );
                        }

But this is not working.
0
hieloCommented:
>>I am doing some debugging and when I do alert(k) I get an incrementing number
I would need to know exactly what it is you have in result. It sounds like it is an actual indexed/numeric array
result = ['a','b','c']

given the above:
for( k in result )
{
  alert( k + ' ' + result[k] )
}

should give you:
0 a
1 b
2 c


 instead of an object:
result={"lettera":"a","letterb":"b", "letterc":"c"};
given the above:
for( k in result )
{
  alert( k + ' ' + result[k] )
}

should give you:
lettera a
letterb b
letterc c


so when I was using this:
  $('#'+k).val( result[k] );

I was expecting the server to be sending:
{"custid":"a","company":"b","contact":"c","address":"d","city":"e","state":"f","zip":"g","country":"h","phone":"i","email":"j"}

so that on every iteration the k would be:
custid, then company, then contact, etc
and the corresponding result[k] would have been:
a, then b, then c, etc

so in essence the statement above would have been:
$('#custid').val('a')
$('#company').val('b')
$('#contact').val('c')

It sounds like the value you are returning is not like the one I was expecting. Update your success function so that it looks a follows:
success:function(result, textStatus, XMLHttpRequest){
 alert( XMLHttpRequest.responseText );

}
If the alerted value does not look like:
{"custid":"a","company":"b","contact":"c","address":"d","city":"e","state":"f","zip":"g","country":"h","phone":"i","email":"j"}

then modify your server code so that it looks like it.
0
JF0Author Commented:
This is what the server is returning:
{"custid":"58","company":"Mr. Plow","contact":"Homer Simpson","address":"742 Evergreen Terrace","city":"Springfield","state":"XX","zip":"99999","country":"United States","phone":"555-Plow","email":"chunkylover53@aol.com"}

Which looks like what you expected.

And as such the code you gave me does work - the textboxes are populated:
$('#'+k).val( result[k] );

Which is why I am confused as to why when I do:
success: function(result){
                       for( k in result )
                       {
                           alert( k + ' ' + result[k] );
                           
                       }
}

I get:
0 {
1 "
2 c

0
hieloCommented:
strange. If you took the original script I gave you and add the alert():

                     $.ajax({
                                 url:'dataRetriever.php'
                              ,data:{"custid":upd_id}
                              ,type:"POST"
                              ,dataType:"json"
                              ,success:function(result){
                                    for( k in result )
                                    {
                                          $('#'+k).val( result[k] );
                                                        alert(k +'  '+ result[k]);
                                    }
                              }
                     });


then you should be seeing the alerts as I described above. Otherwise this:
$('#'+k).val( result[k] );

wouldn't work and the fields wouldn't  get populated.
0
Julian HansenCommented:
This worked fine for me.
// json.php
<?php

$x = array(
	"custid" => "58",
	"company" => "Mr. Plow",
	"contact" => "Homer Simpson",
	"address" => "742 Evergreen Terrace",
	"city" => "Springfield","state" => "XX",
	"zip" => "99999","country" => "United States",
	"phone" => "555-Plow",
	"email" => "chunkylover53@aol.com");

$result = json_encode($x);
die($result);
?>

// json.html
<html>
<head>
<script type="text/javascript" src="js/jquery.js"></script>
<script type="text/javascript">
$(function() {
    $('#go').click(function(event){
        event.preventDefault();
        $.ajax({
            url:'json.php'
            ,type:"POST"
            ,dataType:"json"
            ,success:function(result){
                $('#result').html(result);
                for( k in result )
                {
                    $('#'+k).val( result[k] );
                }
            }
        });
    });
});
</script>
</head>
<body>
    <a href="javascript.void(0)" id="go">Click me</a>
    <input type="text" value="" id="custid" />
    <input type="text" value="" id="company" />
    <input type="text" value="" id="contact" />
    <input type="text" value="" id="address" />
    <input type="text" value="" id="city" />
    <input type="text" value="" id="zip" />
    <input type="text" value="" id="phone" />
    <input type="text" value="" id="email" />
</body>
</html>

Open in new window

0
Julian HansenCommented:
Ignore line 31 - that was a debug statement I tried but forgot to delete - shouldn't be in the solution.
0
JF0Author Commented:
I'm going to accept your original answer for now since it works. I obviously have bigger issues going on here so once I narrow them down I will post a new question.

Thanks for the help so far!
0
JF0Author Commented:
This works with the following adjustments:

1 - Missing parameter in mysql_query():  $result = mysql_query() or die( mysql_error() );

2 - incorrect concatenation, should not use + symbol: $sql = "SELECT " . implode(", ", array_keys($data)) . " FROM customers WHERE custid=" + mysql_real_escape_string($_POST['custid']);
0
hieloCommented:
I'm disappointed to see the grade given here.  Those minor syntax problems were the least of your problems.  The TRUE value of the answer here lies in the methodology I used to update the page - namely the json-encoded data.

Quite frankly it's insulting!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
jQuery

From novice to tech pro — start learning today.