WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

PHP - AJAX and MySQL


AJAX can be used to interactively communicate with the database.


AJAX database instance

The following example demonstrates how a web page can read information from a database via AJAX:

Examples


Select the corresponding option, and the user information will be displayed here ...



Example explanation-MySQL database

In the above example, the database tables we used are as follows:

mysql> select * from websites;
+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/| 1     | USA     |
| 2  | yahoo       | https://www.yahoo.com/| 11    |  	USA      |
| 3  | welookups Tutorial | http://www.welookups.com/| 4689  | IN      |
| 4  | Yandex.ru       | http://Yandex.com/| 256    | IN      |
| 5  | Facebook     | https://www.facebook.com/| 3     | USA     |
+----+--------------+---------------------------+-------+---------+
5 rows in set (0.01 sec)

Example explanation-HTML page

When a user selects a user from the drop-down list above, a function named "showSite ()" is executed. This function is triggered by the "onchange" event:

test.html file code:

<!DOCTYPE html> <html> <head> <meta charset="utf-8"> <title>welookups Tutorial(welookups.com)</title> <script>
function showSite(str) { if (str=="") { document.getElementById("txtHint").innerHTML=""; return; } if (window.XMLHttpRequest) { // IE7+, Firefox, Chrome, Opera, Safari Browser execution code xmlhttp=new XMLHttpRequest(); } else { // IE6, IE5 Browser execution code xmlhttp=new ActiveXObject("Microsoft.XMLHTTP"); } xmlhttp.onreadystatechange=function() { if (xmlhttp.readyState==4 && xmlhttp.status==200) { document.getElementById("txtHint").innerHTML=xmlhttp.responseText; } } xmlhttp.open("GET","getsite_mysql.php?q="+str,true); xmlhttp.send(); }
</script> </head> <body> <form> <select name="users" onchange="showSite(this.value)"> <option value="">Choose a website:</option> <option value="1">Google</option> <option value="2">yahoo</option> <option value="3">welookups Tutorial</option> <option value="4">Yandex.ru</option> <option value="5">Facebook</option> </select> </form> <br> <div id="txtHint"><b>Website information is displayed here……</b></div> </body> </html>

showSite () function performs the following steps:

  • Check if any website is selected
  • Creating an XMLHttpRequest object
  • Create a function to be executed when the server response is ready
  • Send a request to a file on the server
  • Please note the parameter (q) added to the end of the URL (including the content of the drop-down list)

PHP file

The above server page called via JavaScript is a PHP file named "getsite_mysql.php".

The source code in "getsite_mysql.php" runs a query against the MySQL database and returns the results in an HTML table:

getsite_mysql.php file code:

<?php $q = isset($_GET["q"]) ? intval($_GET["q"]) : ''; if(empty($q)) { echo 'Please select a website'; exit; } $con = mysqli_connect('localhost','root','123456'); if (!$con) { die('Could not connect: ' . mysqli_error($con)); } // Select database mysqli_select_db($con,"test"); // Set encoding to prevent garbled Chinese mysqli_set_charset($con, "utf8"); $sql="SELECT * FROM Websites WHERE id = '".$q."'"; $result = mysqli_query($con,$sql); echo "<table border='1'> <tr> <th>ID</th> <th>Site name</th> <th>website URL</th> <th>Alexa Ranking</th> <th>country</th> </tr>"; while($row = mysqli_fetch_array($result)) { echo "<tr>"; echo "<td>" . $row['id'] . "</td>"; echo "<td>" . $row['name'] . "</td>"; echo "<td>" . $row['url'] . "</td>"; echo "<td>" . $row['alexa'] . "</td>"; echo "<td>" . $row['country'] . "</td>"; echo "</tr>"; } echo "</table>"; mysqli_close($con); ?>

Explanation: When a query is sent from JavaScript to a PHP file, it will happen:

  1. PHP opens a connection to a MySQL database
  2. Find selected users
  3. Create an HTML form, fill it with data, and send it back as a "txtHint" placeholder