ajax call to populate form fields from database query when select value changes

Question

I have been looking through the questions on here and cant find an exact answer to what i am after :( but i have managed to get something.

i have a form select field which i populate from a db query

<select style="width:100%;" class="quform-tooltip chosen-select" id="company_select" name="company_select" title="Company Select" onChange="showUser(this.value)">
<option value="">Please select</option>
<?php
$userID = $user->getUserID();
$query = $user->database->query("SELECT * FROM tbl_businesses as business LEFT JOIN tbl_user_businesses as biz_user ON business.businessID = biz_user.businessID WHERE biz_user.userID ='$userID'");

while($row=$user->database->fetchArray($query))
{
    $bizID = $row['businessID'];
    $bizName = $row['businessName'];
    echo "<option value='$bizID'>$bizName</option>";
}?>
</select>

and then there are currently 2 other textboxes (might increase eventually) which i want to populate when the above select box value is changed/selected

<input id="company_name" type="text" name="company_name" value="" />
<input id="company_email" type="text" name="company_email" value="" />

so i have an onchange function on my select box which is this

<script>
function showUser(str)
{
if (str=="")
{
    document.getElementById("company_name").innerHTML="";
    return;
}
if (window.XMLHttpRequest)
{// code for IE7+, Firefox, Chrome, Opera, Safari
    xmlhttp=new XMLHttpRequest();
}
else
{// code for IE6, IE5
    xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.onreadystatechange=function()
{
    if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
        var data = JSON.parse(xmlhttp.responseText);
        for(var i=0;i<data.length;i++) 
        {
          document.getElementById("company_name").innerHTML += data[i].id + ' - ' + data[i].name + ' - ' + data[i].web;
        }
    }
}
xmlhttp.open("GET","formdata.php?q="+str,true);
xmlhttp.send();
}
</script>

and my formdata.php file is like so

    <?php
include("include/user.php");

$q = intval($_GET['q']);

$sql="SELECT * FROM tbl_businesses WHERE businessID = '".$q."'";

$result = $user->database->query($sql);
$info = array();
while($row=$user->database->fetchArray($result))
{
    $cID = $row['bussinessID'];
    $cName = $row['businessName'];
    $cWeb = $row['businessWebsite'];
    $info[] = array( 'id' => $cID, 'name' => $cName, 'web' => $cWeb );
}
echo json_encode($info);?>

which is making the ajax call correctly and returning the data expected but i now need help to populate the textbox values?

can anyone please help me with this, have literatly spent ages trying to figure it out, im not familiar with javascript/json so not sure where to begin

i want the company_name textbox value to be set to $cName; and company_email textbox value to be set to $cWeb;

appreciate any help

Luke

copy response from request found in browser console and see if it validates in jsonlint.com...might have some unexpected output from php — charlietfl, Nov 13 '13 at 15:34
hi yeah the response validates ok, an example of the resonse is `code`[{"id":"5","name":"My Company 5","web":"www.samplewebsite.com"}]`code` thanks — user2886669, Nov 13 '13 at 15:39
if `textboxes` means form input , or textarea need to set value, not innerHTML — charlietfl, Nov 13 '13 at 15:57
hi charlietfl yeah sorry meant form input :) thankyou changing innerHTML to value worked a treat :) thanks — user2886669, Nov 13 '13 at 18:22

score 6 · Accepted Answer · answered Nov 14 '13 at 13:41

ok the solution that i used, for anyone else wanting to know how i solved it is

my index.php which contains the javascript and the form code

javascript code

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.10.1/jquery.min.js" type="text/javascript"></script>

<script>
function showUser(str)
{
if (str=="")
{
    document.getElementById("company_name").value="";
    return;
}
if (window.XMLHttpRequest)
{// code for IE7+, Firefox, Chrome, Opera, Safari
    xmlhttp=new XMLHttpRequest();
}
else
{// code for IE6, IE5
    xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.onreadystatechange=function()
{
    if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
        var data = JSON.parse(xmlhttp.responseText);
        for(var i=0;i<data.length;i++) 
        {
          document.getElementById("company_name").value = data[i].name;
          document.getElementById("company_email").value = data[i].web;
        }
    }
}
xmlhttp.open("GET","formdata.php?q="+str,true);
xmlhttp.send();
}
</script>

and the form code

    <select style="width:100%;" class="quform-tooltip chosen-select" id="company_select" name="company_select" title="Company Select" onChange="showUser(this.value)">
<option value="">Please select</option>
<?php
$userID = $user->getUserID();
$query = $user->database->query("SELECT * FROM tbl_businesses as business LEFT JOIN tbl_user_businesses as biz_user ON business.businessID = biz_user.businessID WHERE biz_user.userID ='$userID'");

while($row=$user->database->fetchArray($query))
{
    $bizID = $row['businessID'];
    $bizName = $row['businessName'];
    echo "<option value='$bizID'>$bizName</option>";
}?>
</select>

<input id="company_name" type="text" name="company_name" value="" />
<input id="company_email" type="text" name="company_name" value="" />

then my formdata.php

    $q = intval($_GET['q']);

$sql="SELECT * FROM tbl_businesses WHERE businessID = '".$q."'";

$result = $user->database->query($sql);
$info = array();
while($row=$user->database->fetchArray($result))
{
    $cID = $row['businessID'];
    $cName = $row['businessName'];
    $cWeb = $row['businessWebsite'];
    $info[] = array( 'id' => $cID, 'name' => $cName, 'web' => $cWeb );
}
echo json_encode($info);?>

thats it, thanks to charlietfl for your help!

hope this helps someone :)

score 0 · Answer 2 · answered Nov 13 '13 at 15:48

Here's an example with PHP and JQuery. If you are not familiar with JQuery, I suggest you take some time to digg into that before going on with your ajax, it's definitely gonna worth it. JQuery have methods like get and ajax to do async request to the server.

Now, heres some jquery we used to get JSON data from the server.

var title = '.....'
$.getJSON('getActivite.php?title=' + title, null,
        function(data){
            $("#currentId").val(data.ID);
            $("#nomActivite").val(data.Nom);
            $("#Description").val(data.Description);
            $("#DateEvent").val(data.Date);
});

$("#currentId").val(data.ID); , this says : find the element with the id currentId in the DOM, and change it's value to the property ID of the data received from the ajax call.

On the PHP side, they had

<?php
    header('Content-Type: application/json');

    mysql_connect("localhost","root") or die (" NOPE . [" . mysql_error() . "]");
    mysql_select_db("garderie");


    $title  = $_GET["title"]; // we received this from the json call


    $query = "  select a.ActiviteID as ActiviteID  , rtrim(a.Nom) as Nom, a.Description from activites a inner join .....' ";


    $result = mysql_query($query);       
    $ligne  = mysql_fetch_array($result);

    $data = array(
        'ID'            => $ligne["ActiviteID"],
        'Nom'           => $ligne["Nom"],
        'Description'   => $ligne["Description"],
        'Date'          => $date
    );

    mysql_close();

    echo (json_encode($data));
?>

ajax call to populate form fields from database query when select value changes

2 Answers2

Linked