"Utilizing an AJAX request to dynamically fill form fields based on a database query as the selected value

Question

"Utilizing an AJAX request to dynamically fill form fields based on a database query as the selected value

I've searched through the questions here but haven't found a precise answer to my query :( However, I have managed to find something.

I have a form select field that I populate from a database 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>

Currently, there are 2 other textboxes (which may increase in the future) that I want to populate when the value of the select box above 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 this:

<?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);?>

The ajax call is functioning correctly and returning the expected data, but now I need help populating the textbox values.

Could someone please assist me with this? I've spent a lot of time trying to figure it out. I'm not familiar with JavaScript/JSON, so I don't know where to start.

I'd like the company_name textbox value to be set to $cName and the company_email textbox value to be set to $cWeb.

Appreciate any assistance.

Luke

javascript ajax database json

Answer 1

Answer №1

Here is the solution I used to solve the issue:

In my index.php file, I included both the JavaScript and 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>

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="" />

Lastly, in my formdata.php file:

    $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);?>

This solution worked for me, thanks to charlietfl for the assistance!

I hope this explanation can help someone else too :)

Answer 2

Here is the solution I used to solve the issue:

In my index.php file, I included both the JavaScript and 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>

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="" />

Lastly, in my formdata.php file:

    $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);?>

This solution worked for me, thanks to charlietfl for the assistance!

I hope this explanation can help someone else too :)

Answer 3

Answer №2

If you're new to PHP and JQuery, it's essential to understand how they work together. Before diving into AJAX, take some time to familiarize yourself with JQuery, as it will greatly benefit your development process. JQuery offers handy methods like get and ajax for making asynchronous requests to the server.

Below is an example of using JQuery to retrieve JSON data from a 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);
});

The line `$("#currentId").val(data.ID);` essentially means finding the element with the ID 'currentId' in the Document Object Model (DOM) and updating its value with the ID property obtained from the AJAX response.

On the PHP side of things, the script looked something like this:

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

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

    $title  = $_GET["title"]; // receiving input from 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));
?>

Answer 4

If you're new to PHP and JQuery, it's essential to understand how they work together. Before diving into AJAX, take some time to familiarize yourself with JQuery, as it will greatly benefit your development process. JQuery offers handy methods like get and ajax for making asynchronous requests to the server.

Below is an example of using JQuery to retrieve JSON data from a 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);
});

The line `$("#currentId").val(data.ID);` essentially means finding the element with the ID 'currentId' in the Document Object Model (DOM) and updating its value with the ID property obtained from the AJAX response.

On the PHP side of things, the script looked something like this:

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

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

    $title  = $_GET["title"]; // receiving input from 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));
?>

"Utilizing an AJAX request to dynamically fill form fields based on a database query as the selected value

Answer №1

Answer №2

Similar questions

The deletion request using the form in Express is experiencing issues and not functioning properly

exploring methods to prevent flash of unstyled content (fouc)

Transitioning from using a jQuery get request to utilizing Vue.js

What is the method for revealing elements with a click?

Problems with the functionality of the remote feature in Twitter Bootstrap Modal

"Confusion arises when handling undefined arguments within async.apply in the context of async

Displaying Stats.js inside a different canvas using ThreeJS

Employing passport-steam alongside sails-generate-auth

To ensure the next line only runs after the line above has finished executing, remember that the function is invoked in HTML

What are alternative methods for implementing autocomplete search for usernames from a database without relying on jQuery?

Displaying a ReactJS component inside a map function by giving it a unique identifier

Converting a file from a URL to a blob in PHP for use in JavaScript

Using the Trigger Method in a Vue JS Component with Sibling Components

Using interpolation brackets in Angular2 for variables instead of dots

Uploading JSON object to server using Angular

Determine the number of distinct values in objects contained in a large JSON file using Python

A step-by-step guide on initializing and setting a cookie value with expiration

Efficient methods for transferring information between a main and pop-up page within angularjs

The array is giving back null values

Is it possible to obtain the outcome from the MySQL database in JSON format?