How do i get the results of a mysql query into html textbox

Question

Been working on this for 8 hours and haven't gotten anywhere. Made changes only to run into another block. Started over and back to almost square-1. Seeking help in understanding what i'm doing wrong. I did get this working by returning all students in a table but that is not what I need. What I'm trying to do is: 1. Prompt user to enter a student ID. 2. Run the id against a mysql query 3. Output the info about the student (ID, Name, email and GPA) into 4 textboxes. I know that I need an array and then in the js/ajax split the response between the 4 textboxes. How do i call the array in the function and then output the result for that student into the textboxes? Any help or advice is appreciated.

This is what I have so far: html:

<script type="text/javascript">
function queryStudent() {
    var ajaxRequest;
    try {
        ajaxRequest = new XMLHttpRequest;
    }catch(e) {
        //IE Browsers
        try {
            ajaxRequest = new ActiveXObject("Msxml2.XMLHTTP");
            } catch (e) {
                try{
                    ajaxRequest = new ActiveXObject("Microsoft.XMLHTTP");
                } catch (e){
                    // Something went wrong
                    alert("Browser not compatible");
                    return false;
                }
            }
    }
    ajaxRequest.onreadystatechange = function() {
        if(ajaxRequest.readyState == 4 && ajaxRequest.status == 200) {
            //delcare a array
            //use ajax response.split to return the results of query to each textbox here
            var results = ajaxRequest.responseText.split(); //how do i get query results in here
            //output array indexes to html element id's
            document.getElementById('studentID').value = response[0]; //fill text box 1
            document.getElementById('stuName').value = response[1];//text box 2
            document.getElementById('email').value = response[2];
            document.getElementById('GPA').value = response[3];         
}
};
        var stuID = document.getElementByID('stuID').value
        var queryString = "?stuID=" + stuID;
        ajaxRequest.open("POST", "search_single.php"+ queryString, true);
        ajaxRequest.send(stuID);

        }
</script> 
<form name="student" method="post" action="search_single.php">
<label>StudentID:</label>
<input type="text" id="input" name="stuID" value="">
<br>
<br>
<input type="button"  onclick="queryStudent();" value="Search" id="button">
</form>
<h2>Student information will be displayed in the textboxes below:</h2>
<br>
<table id="outuput">
<tr>
    <td><label>Student ID:</label></td>
    <td><input type="text" id="stuID" value="" readonly></td>
</tr>
<tr>
    <td><label>Student Name:</label></td>
    <td><input type="text" id="stuName" value="" readonly></td>
</tr>    
<tr>
    <td><label>Email:</label></td>
    <td><input type="text" id="email" value=" " readonly></td>
</tr>
<tr>
    <td><label>GPA:</label></td>
    <td><input type="text" id="gpa" value=" " readonly></td>
</tr>
<br>
</table></body>

PHP:

$stuID = filter_input(INPUT_POST, 'studentID');
//Code for query
$query = 'SELECT *
          FROM student
          WHERE studentID = :stuID';
$statement = $db->prepare($query); //
$statement->bindValue(':stuID', $stuID); //bind all values (name, email..ect)?
$statement->execute();
$students = $statement->fetch(); //Fetch individual row's

Replace .innerHTML() with .value, and add value="" to each input tag. — Gjermund B. Dahl
– Gjermund B. Dahl, Commented May 1, 2016 at 21:50
It also seems like you try to retrieve the stuID from the wrong input field. — Gjermund B. Dahl
– Gjermund B. Dahl, Commented May 1, 2016 at 21:55
made some changes to the code however text boxes are still blank after running. — allendks45
– allendks45, Commented May 1, 2016 at 22:00
Change var stuID = document.getElementByID('stuID').value to var stuID = document.getElementByID('input').value — Gjermund B. Dahl
– Gjermund B. Dahl, Commented May 1, 2016 at 22:07
The id for input completely escaped me. I changed the other textbox id to maintain my readability. Either my logic is poor or both logic is poor and code is completely wrong. When I hit submit nothing happens where as I want the student id along with other info to populate the textboxes....this is driving me crazy — allendks45
– allendks45, Commented May 1, 2016 at 22:18

Gjermund B. Dahl · Accepted Answer · 2016-05-02 08:10:56Z

I have had a closer look at you code, and found quite a few errors. I recommend you use an IDE for development. IDEs have automated code validation, which makes it easy to discover and correct errors. Also make it a habit to read the PHP docs on functions. Similar docs for javascript is here.

Client code

<!DOCTYPE html>
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <script type="text/javascript">
    function queryStudent() {
        //Retrieve user input, student ID
        var stuID = document.getElementById('input').value

        //Build querystring
        var queryString = "?stuID=" + stuID;

        //Create XMLHttpRequest
        var xmlhttp = new XMLHttpRequest();

        //Define function to process server feedback
        xmlhttp.onreadystatechange = function() {
            if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {

                //Parse reponse
                var obj = JSON.parse(xmlhttp.responseText);

                //Populate form
                document.getElementById('stuID').value = obj[0].studentID;
                document.getElementById('stuName').value = obj[0].stuName;
                document.getElementById('email').value = obj[0].email;
                document.getElementById('gpa').value = obj[0].GPA;
            }
        };
        //Run query
        xmlhttp.open("POST", "search_single.php" + queryString, true);
        xmlhttp.send();
    }
    </script> 
</head>
<body>

    <form name="student" method="post" action="search_single.php">
        <label>StudentID:</label>
        <input type="text" id="input" name="stuID" value="">
        <br>
        <br>
        <input type="button"  onclick="queryStudent();" value="Search" id="button">
    </form>
    <h2>Student information will be displayed in the textboxes below:</h2>

    <table id="output">
        <tr>
            <td><label>Student ID:</label></td>
            <td><input type="text" id="stuID" value="" readonly></td>
        </tr>
        <tr>
            <td><label>Student Name:</label></td>
            <td><input type="text" id="stuName" value="" readonly></td>
        </tr>    
        <tr>
            <td><label>Email:</label></td>
            <td><input type="text" id="email" value=" " readonly></td>
        </tr>
        <tr>
            <td><label>GPA:</label></td>
            <td><input type="text" id="gpa" value=" " readonly></td>
        </tr>
    </table>


</body>
</html>

Server code

<?php

//-------------------------
//Database constants, enter your DB info into these constants :
define(DB_HOST,     "hostname");
define(DBUSER,      "username");
define(DBPASS,      "password");
define(DBSCHEME,    "databasename");
//-------------------------

//Create DB link
$con = new PDO('mysql:host='.DB_HOST.';dbname='.DBSCHEME, DBUSER, DBPASS); 
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

//Retrieve user input 
$stuID = $_REQUEST['stuID'];

//Evaluate user input
if( $stuID == "" )
    die("Invalid Student ID");

//Prepare and execute
$sql = 'SELECT studentID, stuName, email, GPA FROM student WHERE studentID = :stuID';
$query = $con->prepare($sql);
$query->bindValue(':stuID', $stuID, PDO::PARAM_STR);
$query->execute();

//Retrieve results
$students = $query->fetchAll(PDO::FETCH_ASSOC);
$json = json_encode($students);

//Output to ajax
echo $json;

This is outstanding! Much cleaner explanation than the book I currently am self teaching. I am going to give this a go with my db and go from there. any idea why the book was leading me to using an array and then splitting it with a 'responseRequest.split()?' Everything i searched on SO and dr. google led me to JSON which is new to me as well. Much appreciated learning point.
Happy to help :) When it comes to split string versus json, I think the reason is strictly pedagogical.

Collectives™ on Stack Overflow

How do i get the results of a mysql query into html textbox

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related