{"id":4278,"date":"2015-02-09T06:08:04","date_gmt":"2015-02-09T06:08:04","guid":{"rendered":"http:\/\/a1webdesignteam.com\/blog\/?p=4278"},"modified":"2015-02-09T06:29:26","modified_gmt":"2015-02-09T06:29:26","slug":"ajax-database-operations","status":"publish","type":"post","link":"https:\/\/a1webdesignteam.com\/blog\/ajax-database-operations\/","title":{"rendered":"AJAX – Database Operations"},"content":{"rendered":"

To clearly illustrate how easy it is to access information from a database using AJAX, we are going to build MySQL queries on the fly and display the results on “ajax.html”. But before we proceed, let us do the ground work. Create a table using the following command.<\/p>\n

NOTE: We are assuming you have sufficient privilege to perform the following MySQL operations<\/p>\n

CREATE TABLE 'ajax_example' (\r\n   'name' varchar(50) NOT NULL,\r\n   'age' int(11) NOT NULL,\r\n   'sex' varchar(1) NOT NULL,\r\n   'wpm' int(11) NOT NULL,\r\n   PRIMARY KEY  ('name')\r\n) \r\n<\/pre>\n
Now dump the following data into this table using the following SQL statements:<\/p>\n
INSERT INTO 'ajax_example' VALUES ('Jerry', 120, 'm', 20);\r\nINSERT INTO 'ajax_example' VALUES ('Regis', 75, 'm', 44);\r\nINSERT INTO 'ajax_example' VALUES ('Frank', 45, 'm', 87);\r\nINSERT INTO 'ajax_example' VALUES ('Jill', 22, 'f', 72);\r\nINSERT INTO 'ajax_example' VALUES ('Tracy', 27, 'f', 0);\r\nINSERT INTO 'ajax_example' VALUES ('Julie', 35, 'f', 90);\r\n<\/pre>\n
Client Side HTML File<\/h2>\n
Now let us have our client side HTML file, which is ajax.html, and it will have the following code:<\/p>\n
<html>\r\n<body>\r\n<script language=\"javascript\" type=\"text\/javascript\">\r\n<!-- \r\n\/\/Browser Support Code\r\nfunction ajaxFunction(){\r\n   var ajaxRequest;  \/\/ The variable that makes Ajax possible!\r\n   try{\r\n   \r\n      \/\/ Opera 8.0+, Firefox, Safari\r\n      ajaxRequest = new XMLHttpRequest();\r\n   }catch (e){\r\n      \r\n      \/\/ Internet Explorer Browsers\r\n      try{\r\n         ajaxRequest = new ActiveXObject(\"Msxml2.XMLHTTP\");\r\n      }catch (e) {\r\n         \r\n         try{\r\n            ajaxRequest = new ActiveXObject(\"Microsoft.XMLHTTP\");\r\n         }catch (e){\r\n         \r\n            \/\/ Something went wrong\r\n            alert(\"Your browser broke!\");\r\n            return false;\r\n         }\r\n      }\r\n   }\r\n   \r\n   \/\/ Create a function that will receive data\r\n   \/\/ sent from the server and will update\r\n   \/\/ div section in the same page.\r\n   ajaxRequest.onreadystatechange = function(){\r\n   \r\n      if(ajaxRequest.readyState == 4){\r\n         var ajaxDisplay = document.getElementById('ajaxDiv');\r\n         ajaxDisplay.innerHTML = ajaxRequest.responseText;\r\n      }\r\n   }\r\n   \r\n   \/\/ Now get the value from user and pass it to\r\n   \/\/ server script.\r\n   var age = document.getElementById('age').value;\r\n   var wpm = document.getElementById('wpm').value;\r\n   var sex = document.getElementById('sex').value;\r\n   var queryString = \"?age=\" + age ;\r\n   \r\n   queryString +=  \"&wpm=\" + wpm + \"&sex=\" + sex;\r\n   ajaxRequest.open(\"GET\", \"ajax-example.php\" + queryString, true);\r\n   ajaxRequest.send(null); \r\n}\r\n\/\/-->\r\n<\/script>\r\n\r\n<form name='myForm'>\r\n\r\n   Max Age: <input type='text' id='age' \/> <br \/>\r\n   Max WPM: <input type='text' id='wpm' \/> <br \/>\r\n   Sex: \r\n   <select id='sex'>\r\n      <option value=\"m\">m<\/option>\r\n      <option value=\"f\">f<\/option>\r\n   <\/select>\r\n   <input type='button' onclick='ajaxFunction()' value='Query MySQL'\/>\r\n   \r\n<\/form>\r\n<div id='ajaxDiv'>Your result will display here<\/div>\r\n<\/body>\r\n<\/html>\r\n<\/pre>\n
NOTE<\/b>: The way of passing variables in the Query is according to HTTP standard and have formA.<\/p>\n
URL?variable1=value1;&variable2=value2;\r\n<\/pre>\n
The above code will give you a screen as given below:<\/p>\n
NOTE<\/b>: This is dummy screen and would not work<\/p>\n
Max Age:\u00a0 Max WPM: Sex:<\/form>\n<\/form>\n
Your result will display here in this section after you have made your entry.<\/p>\n
NOTE<\/b>: This is a dummy screen.<\/p>\n
Server Side PHP File<\/h2>\n
Your client-side script is ready. Now, we have to write our server-side script, which will fetch age, wpm, and sex from the database and will send it back to the client. Put the following code into the file “ajax-example.php”.<\/p>\n
<?php\r\n$dbhost = \"localhost\";\r\n$dbuser = \"dbusername\";\r\n$dbpass = \"dbpassword\";\r\n$dbname = \"dbname\";\r\n\t\r\n\/\/Connect to MySQL Server\r\nmysql_connect($dbhost, $dbuser, $dbpass);\r\n\t\r\n\/\/Select Database\r\nmysql_select_db($dbname) or die(mysql_error());\r\n\t\r\n\/\/ Retrieve data from Query String\r\n$age = $_GET['age'];\r\n$sex = $_GET['sex'];\r\n$wpm = $_GET['wpm'];\r\n\t\r\n\/\/ Escape User Input to help prevent SQL Injection\r\n$age = mysql_real_escape_string($age);\r\n$sex = mysql_real_escape_string($sex);\r\n$wpm = mysql_real_escape_string($wpm);\r\n\t\r\n\/\/build query\r\n$query = \"SELECT * FROM ajax_example WHERE sex = '$sex'\";\r\n\r\nif(is_numeric($age))\r\n   $query .= \" AND age <= $age\";\r\n\r\nif(is_numeric($wpm))\r\n   $query .= \" AND wpm <= $wpm\";\r\n\t\r\n\/\/Execute query\r\n$qry_result = mysql_query($query) or die(mysql_error());\r\n\r\n\/\/Build Result String\r\n$display_string = \"<table>\";\r\n$display_string .= \"<tr>\";\r\n$display_string .= \"<th>Name<\/th>\";\r\n$display_string .= \"<th>Age<\/th>\";\r\n$display_string .= \"<th>Sex<\/th>\";\r\n$display_string .= \"<th>WPM<\/th>\";\r\n$display_string .= \"<\/tr>\";\r\n\r\n\/\/ Insert a new row in the table for each person returned\r\nwhile($row = mysql_fetch_array($qry_result)){\r\n   $display_string .= \"<tr>\";\r\n   $display_string .= \"<td>$row[name]<\/td>\";\r\n   $display_string .= \"<td>$row[age]<\/td>\";\r\n   $display_string .= \"<td>$row[sex]<\/td>\";\r\n   $display_string .= \"<td>$row[wpm]<\/td>\";\r\n   $display_string .= \"<\/tr>\";\r\n}\r\n\r\necho \"Query: \" . $query . \"<br \/>\";\r\n$display_string .= \"<\/table>\";\r\n\r\necho $display_string;\r\n?>\r\n<\/pre>\n
Now try by entering a valid value (e.g., 120) in Max Age<\/i> or any other box and then click Query MySQL button.<\/p>\nMax Age:\u00a0 Max WPM: Sex:<\/form>\n<\/form>\n
Your result will display here in this section after you have made your entry.<\/div>\n
If you have successfully completed this lesson, then you know how to use MySQL, PHP, HTML, and Javascript in tandem to write AJAX applications.<\/p>\n","protected":false},"excerpt":{"rendered":"
To clearly illustrate how easy it is to access information from a database using AJAX, we are going to build MySQL queries on the fly and display the results on “ajax.html”. But before we proceed, let us do the ground work. Create a table using the following command. NOTE: We are assuming you have sufficient […]<\/p>\n","protected":false},"author":1,"featured_media":4279,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0},"categories":[8],"tags":[51],"_links":{"self":[{"href":"https:\/\/a1webdesignteam.com\/blog\/wp-json\/wp\/v2\/posts\/4278"}],"collection":[{"href":"https:\/\/a1webdesignteam.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/a1webdesignteam.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/a1webdesignteam.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/a1webdesignteam.com\/blog\/wp-json\/wp\/v2\/comments?post=4278"}],"version-history":[{"count":0,"href":"https:\/\/a1webdesignteam.com\/blog\/wp-json\/wp\/v2\/posts\/4278\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/a1webdesignteam.com\/blog\/wp-json\/wp\/v2\/media\/4279"}],"wp:attachment":[{"href":"https:\/\/a1webdesignteam.com\/blog\/wp-json\/wp\/v2\/media?parent=4278"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/a1webdesignteam.com\/blog\/wp-json\/wp\/v2\/categories?post=4278"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/a1webdesignteam.com\/blog\/wp-json\/wp\/v2\/tags?post=4278"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}