Interactive Database Access

VI.1.1 Intro

This section covers how to perform four basic MySQL database tasks interactively from a webpage. These four tasks are querying, writing, editing and deleting database records. For each of these tasks, a JavaScript function is called by user input. The JavaScript function uses an AJAX object to call a PHP script. The PHP script accesses the MySQL database and retrieves or rewrites its data. If required the information from the database query is passed back from the PHP script through the calling JavaScript code and written to the webpage.

The topics covered in this section are:

• querying the MySQL database to retrieve records;
• writing a new line to the database;
• deleting a line from the database;
• and editing and existing line in the database.

VI.1.2 Prerequisites

This section implements concepts from the first five sections listed in the index. In particular, this section combines the techniques covered by Section III., "Accessing MySQL with PHP" and Section V., "Calling PHP from JavaScript". In addition to what is explicity covered in this document a basic understanding of HTML is expected and some familiarity with object based programming languages such as C++, Java, or JavaScript.

The example code for this section is complete in itself. Therfore, for experienced programmers this section could be used as a standalone "quick start" to interfacing a webpage with a MySQL database using JavaScript.

For the example scripts in this section to run, Apache, PHP, and MySQL must be installed. Installing these packages for a Ubuntu system is covered by "Setting up a Local Linux Server with MySQL Support".

Furthermore Apache and MySQL must be running. Apache usually starts with system bootup by default. MySQL can be started by:

VI.1.3 Contents

VI.2.1 Intro

This section covers how to use a query defined by user input to read a table from a MySQL database.

The following scripts query the database using a string input by the user. There are four files which accomplish this query:

• The PHP script which queries MySQL: "queryMySQL.php";
• the short PHP script that contains the database configuration info: "openMySQL.php";
• the JavaScript file which uses AJAX to call "queryMySQL.php": "GetQueryAJAX.js";
• and the HTML page which interfaces with "GetQueryAJAX.js": "queryMySQL.html"

VI.2.2 The PHP Scripts

The PHP script, "queryMySQL.php", is written as follows:

The lines:

$queryWord="NULL";
if(isset($_GET["qWord"])){
    $queryWord=$_GET["qWord"];
}

read the variables sent to the PHP script by the AJAX object, defined in "GetQueryAJAX.js". This syntax was demonstrated by passVarsGETAJAX.php.

After the input variable is read the connection with the MySQL database is established. In this script the connection information for the database including the server name, user name, password and database name are no longer written. Instead the script includes another script, "openMySQL.php" which contains:

The reason behind this will be discussed further in the comments at the end of this section.

With the lines from "openMySQL.php" included the beginning of "queryMySQL.php" would be essentially "test_connect.php". The next lines are:

$nameStr="%".$queryWord."%";
$queryStr="SELECT species,name,height FROM trees WHERE name LIKE '$nameStr'"; 
                            
$result = $mysqli->query($queryStr);
if(!($result)){ 
    $mysqli->close(); 
    print "FAIL ERROR: NULL result returned for query: ".$queryStr; 
    exit();
} 
$num_rows_returned = $result->num_rows;
if($num_rows_returned<1){
    $result->close();
    $mysqli->close(); 
    printf("FAIL: num rows returned: %s for query: %s", $num_rows_returned, $queryStr);
                      
    exit(); 
}

These lines query the database and read the result. This is essentially what is done in "test_query.php". Specifically a query is sent to the database requesting the data in the "trees" table for the three fields, "species", "name", and "height". Only rows with a "name" that contains the string from the passed string variable "$queryWord" are returned.

If the query returned a good result then the next lines in "queryMySQL.php" are executed:

$ret_array = "{\"trees\":["; 
for($i=0; $i<($num_rows_returned); $i++){ 
    $row = $result->fetch_array(MYSQLI_ASSOC);      
    $ret_array = $ret_array.json_encode($row); 
    if($i<($num_rows_returned-1)){ 
        $ret_array=$ret_array.",";
    } 
}
$ret_array = $ret_array."]}";
printf("%03u%s",$num_rows_returned,$ret_array); 

Here the JSON-formated string, "$ret_array" is constructed. The syntax for JSON-formatted strings is discussed in the Section II.2 Basic JavaScript. The code "{\"trees\":[" sets up a JavaScript object with the label "trees". The loop converts the PHP arrays into appropriately formated strings for conversion into JavaScript arrays as was done with jsonDemo.php. These array formated strings are added to "$ret_array" with commas, ",", in between them. At the end of the loop the pre-object string is closed with the sequence, "]}". This pre-object string, if printed, would display the same format as the pre-JSON string for a two dimensional array, "test2_obj_prestring", discussed at the end of Section II.2.4. Finally the "object-string" is returned to the calling object, with the number of rows in the same fashion as discussed in Section V.3.2.

The last lines of the file simply close open objects and terminate the PHP script:

    $result->close(); 
    $mysqli->close(); 
?>

VI.2.3 The JavaScript

The JavaScript file,"GetQueryAJAX.js", which calls the PHP to access the database is as follows:

The function "testGet(search_str)" is the entry point to this file. It is essentially the same as the "testGet(search_str)" function of passVarsGETAJAX.js.

The lines:

var req=false; 
try{ //try ... catch(e) is used for error handling 
    req=new XMLHttpRequest(); 
}catch(e){// if the browser does not support the XMLHttpRequest function then return false. 
    return false;
}

create a new XMLHttpRequest() object catching the error if this object cannot be created.

The lines:

instruct the XMLHttpRequest object to open the PHP script "queryMySQL.php" sending it the input variable, "qWord" which is defined as equal to the string "search_str".

Once the PHP script finishes the XMLHttpRequest object is set to perform the tasks set by the lines:

req.onreadystatechange = function(){ 
    if(req.readyState==4){ 
        if(req.status==200){ 
            dumpMySQLQuery(req.responseText); 
        }else{ 
            alert("Failed to open script"); 
        } 
    } 
}; 

These lines function the same as the equivalent lines in testAJAX.js. When the "readystate" of the XMLHttpRequest object changes, which occurs when the PHP script queryMySQL.php finishes, the code inside function(){...} is executed. This code either displays an error message, if the status of the XMLHttpRequest object indicates failure, or calls the function, dumpMySQLQuery passing it the string returned by the PHP script. The function, dumpMySQLQuery(retText) takes the returned string, reads it and dumps it to the webpage, "queryMySQL.html" (See: Section VI.2.4).

In dumpMySQLQuery the lines:

var err_n=retText.search("FAIL"); 
if(err_n>=0 && err_n<3){ 
    // Print the error string 
    document.getElementById("forDump").innerHTML = retText; 
    return; 
} 

check if the query to the database was successful; if it was not the error message returned from the PHP script and passed to "retText" is printed. If the query was successful then the lines:

extract the two objects passed back by the PHP script: a numeric variable containing the number of rows found, and a JavaScript object containing the results returned from the database by the query. The method used to extract these variables is essentially the same as the code employed in the second version of: testAJAXJSON.js.

The rest of the function:

var dumpText = "The following lines were found in the database that contained
    the input string in their \"name\" field:</br>"; 
dumpText = dumpText+"    species    |     name    |     height    </br>"; 
for(i=0; i<num_rows_ret; i++){ 
    dumpText = dumpText + "   "+array_ret.trees[i].species+"   |"; 
    dumpText = dumpText + "   "+array_ret.trees[i].name+"   |"; 
    dumpText = dumpText + "   "+array_ret.trees[i].height+"</br>"; 
} 
document.getElementById("forDump").innerHTML = dumpText;

accesses the "div" element of queryMySQL.html with id "forDump", and dumps the contents of the JavaScript array object returned by "queryMySQL.php":.

VI.2.4 The HTML

The HTML page "queryMySQL.html" queries the database for rows with a name containing the string input by the user. It does this by employing the two scripts discussed above. Specifically "queryMySQL.html" calls "GetQueryAJAX.js" which in turn calls "queryMySQL.php" which passes the data back up to "queryMySQL.html".

The code for "queryMySQL.html" is as follows:

The first few lines:

<head> 
    <script type="text/javascript" src="GetQueryAJAX.js"></script> 
</head> 

include the JavaScript file "GetQueryAJAX.js". After this file is included its functions become available to be called from within "queryMySQL.html".

In the "body" of the page, the lines:

Search database for trees with names that contain:<input type="text" 
    name="tree_name" id="treename_id" size="15"> </br>
<button onclick="queryDB()">Submit</button>

create a form box for text input and a button which calls the "queryDB()" function when selected. These lines are essentially the same as the code from "TestForms.html".

When the “Submit” button is selected the function defined by:

function queryDB(){ 
    var search_str = document.getElementById("treename_id").value;
    testGet(search_str); 
}

is called. This function retrieves the "value", or string, input by user into the input form and passes it to the "testGet" function. The "testGet" function comes from the included file "GetQueryAJAX.js". "testGet" accesses the database and writes the result of the query to the "div" element defined by the line:

which displays the results in the browser.

VI.2.5 Running the Scripts

To run the database query, save all three files to "/var/www" and then enter "http:/localhost/queryMySQL.html" in the browser's address bar. All lines from the "trees" table in the database that contain the user input string in the "name" field will be dumped.

Note: for the AJAX object here the "GET" and "POST" methods are NOT interchangeable. When retrieving data from MySQL "POST" cannot be used.

VI.3.1 Intro

There are three ways to edit the database which are covered here; the first is to simply delete a row in a table; the second is to add a row to an existing table and the third is to edit the values in an existing row.

The following scripts edit the database using a string input by the user. For each of the thee methods of editing the database are four files which work together to perform the task. These are:

• the PHP script which queries MySQL, "deleteMySQL.php", "writeMySQL.php", or "editMySQL.php" for the delete, write, or edit sequences respectively;
• the short PHP script that contains the database configuration info, "openMySQL.php", which is the same for all sequences;
• the JavaScript file which uses AJAX to call the PHP script which makes the query, "deleteQueryAJAX.js", "writeQueryAJAX.js", or "editQueryAJAX.js" for the delete, write or edit sequences respectively;
• and the HTML page which interfaces with the JavaScript, "deleteMySQL.html", "writeMySQL.html" or "editMySQL.html" for the delete, write, or edit sequences respectively.

VI.3.2 The PHP Script

The PHP scripts for the delete, write, and edit operations resemble the PHP scripts: test_delete.php, test_write.php, and test_edit.php respectively. For all scripts, the initial lines after the include statement accept the variables passed from the JavaScript which contain the user input. For instance the lines:

    $queryWord="NULL";
    if(isset($_POST["qWord"])){ 
        $queryWord=$_POST["qWord"]; 
    }

read the user input value for the tree name.

Note: that "POST" is being used by the PHP script to read inputs instead of "GET" .

The variable "$queryStr" contains the query which is passed to MySQL. The nature of the query depends upon which operation is being executed.

For the delete operation the query:

instructs MySQL to delete from the table trees all rows with a name of the user input string: "$queryWord".

For the write operation the query:

creates a new line in the table "trees" with the values for the fields, "species", "name", and "height" as specified by the variables "$new_species", "$queryWord", and "new_height" respectively.

For the edit, or update, operation the query:

finds the line in the table "trees" with the name specified by the string, "$queryWord" and updates its "species" and "height" fields according to the values in "$new_species" and "$new_height" respectively.

In all scripts, the line:

sends the command in "$queryStr" to MySQL.

The lines:

$result = $mysqli->query($queryStr);
if(!($result)){
    ...
    $mysqli->close();
    exit();
}else{
    print "..."
}

read the result of the query and print the appropriate message depending on whether the commands in the query executed successfully or not.

For all the php scripts called with the "POST" method the line:"$result->close()" is absent.

For the delete operation the PHP script, deleteMySQL.php, is:

For the write operation the PHP script, writeMySQL.php, is:

For the edit operation the PHP script, editMySQL.php is:

The PHP script "openMySQL.php" is identical to the file of the same name covered by section VI.2.2.

VI.3.3 The JavaScript

The JavaScript file which calls PHP to access the database differs slightly with each operation, delete, write, or edit implemented on the database. The JavaScript code for all operations closely resembles passVarsPOSTAJAX.js. The entry function is "testPost(...)". This function is essentially the same as the "testPost(searchStr)" function of passVarsPOSTAJAX.js.

The lines:

var req=false;
try{ //try ... catch(e) is used for error handling
    req=new XMLHttpRequest();
}catch(e){// if the browser does not support the XMLHttpRequest function 
    then return false.
    return false;
}

are the same as the corresponding lines in GetQueryAJAX.js; they create a new XMLHttpRequest object.

The lines:

instuct the XMLHttpRequest object to open the PHP script for the operation; and send it the string variables included in "pass_str" as input. The PHP script will be either "deleteMySQL.php", "writeMySQL.php", or "editMySQL.php" corresponding to the delete, write, or edit operations respectively. These lines are essentially same as the corresponding lines at the end of passVarsPOSTAJAX.js except that the passed variables are defined by input.

Once the PHP script finishes the XMLHttpRequest object is set to perform the tasks set by the lines:

req.onreadystatechange = function(){
    if(req.readyState==4){
        if(req.status==200){
            dumpMySQLQuery(req.responseText);
        }else{
            alert("Failed to open script");
        }
    }
};

These lines are the same for all operations and also essentially the same for testAJAX.js, passVarsPOSTAJAX.js and GetQueryAJAX.js and are described in more detail in the previous chapter. These lines query the XMLHttpRequest object to determine if the operation performed by the PHP script was a success; and if so, they send the result to the function, "dumpMySQLQuery" for further processing.

The function, "dumpMySQLQuery" is defined at the beginning of the JavaScript file and is the same for all operations. This function:

function dumpMySQLQuery(retText){
    document.getElementById("forDump").innerHTML = retText; 
}

contains one line; this line sends the returned text to the "forDump" "div" element of the HTML for display in the web browser. This function is essentially the same as the dumpScriptOutput(retText) function of passVarsGETAJAX.js.

For the delete operation the JavaScript, deleteQueryAJAX.js, is:

For the write operation the JavaScript, writeQueryAJAX.js, is:

For the edit operation the JavaScript, editQueryAJAX.js, is:

All of the above scripts are almost identical; They differ in the name of the ".php" script called and the variables passed.

VI.3.4 The HTML

The HTML page for each operation performs the coded operation by employing the corresponding JavaScript and PHP files. The script "deleteMySQL.html" calls code from "deleteQueryAJAX.js" which in turn calls "deleteMySQL.php". The script "writeMySQL.html" calls code from "writeQueryAJAX.js" which in turn calls "writeMySQL.php". The script "editMySQL.html" calls code from "editQueryAJAX.js" which in turn calls "editMySQL.php". These HTML scripts all follow the same basic format as queryMySQL.html.

For all HTML scripts the first few lines are:

where "...QueryAJAX.js" is set to either "deleteQueryAJAX.js", "writeQueryAJAX.js", or "editQueryAJAX.js", to include the appropriate JavaScript file.

The lines in the body:

create the needed input forms and a "Submit" button which calls the JavaScript function: "queryDB()". In the case of the write and edit operations, after these lines a second set of input fields is created. These fields are used to add or change values in the row with the selected tree name(s).

When the "Submit" button is selected the function defined by the lines:

is called. This function reads the text input in the forms and sends it to the JavaScript file which performs the delete, write or edit operation on the database.

The line in the "body":

creates a "div" element which is used by the JavaScript routines to write the output of the PHP script.

For the delete operation the HTML, deleteMySQL.html, is:

For the write operation the HTML, writeMySQL.html, is:

For the edit operation the HTML, editMySQL.html, is:

Again for all three operations the code is very similar.

VI.3.5 Running the Scripts

Each operation is run in the same fashion. The browser is pointed to the URL of the HTML file corresponding to the operation, "deleteMySQL.html", "writeMySQL.html", or "editMySQL.html".

For the delete operation the tree name must match exactly the name of the tree to be deleted; if the name does not match anything in the database then no changes are made; in either case the message "Row deleted, or not found" is posted.

For the write operation a new tree is created with the tree name specified by input. The variables input for the species and height are likewise used. The new tree line has only the three fields "name", "species" and "height" filled; the rest are initiated to their default values, usually "NULL". If the new line is added successfully then the message, "New row added" is printed.

For the edit operation, the tree name must match exactly the name of the tree to be edited. The script "editMySQL.html" changes the "species" and "height" fields in the line in the table "trees" with name as specified by input. The new values written to this line are also those specified by user input. If the row to be updated already has the specified values for "species" and "height" then no changes are made. In either case the message, "Row updated, or already up to date", is printed.

To check the results of "deleteMySQL.html", "writeMySQL.html" or "editMySQL.html" either use the MySQL monitor or use "queryMySQL.html".