How to populate Html table using PHP and AJAX

Introduction

Nowadays, AJAX become an important technology in creating dynamic actions in a website page without need to reload the page itself.
AJAX allows web pages to be updated asynchronously by exchanging small amounts of data with the server behind the scenes. It can even extract data from database and display it in the webpage without reloading it
In this tutorial, we will handle the action of extracting data from Mysql database and display it in a html table using AJAX.

Content

1- Mysql database.
2- Connection file to database.
3- Home page (which will call the AJAX action).
4- AJAX Request.
5- Page handling the AJAX request.

1- MySql Database

Create a database named for example stores. Then create a table named items by executing the following SQL query :

--
-- Table structure for table `items`
--
CREATE TABLE `items` (
  `itemId` int(11) NOT NULL,
  `itemBarcode` varchar(50) NOT NULL,
  `itemName` varchar(100) NOT NULL,
  `itemUnitPrice` float NOT NULL,
  `itemSellingPrice` float NOT NULL,
  `storeId` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
--
-- Indexes for table `items`
--
ALTER TABLE `items`
  ADD PRIMARY KEY (`itemId`);

Then insert some data in table items created :

--
-- Dumping data for table `items`
--

INSERT INTO `items` (`itemId`, `itemBarcode`, `itemName`, `itemUnitPrice`, `itemSellingPrice`, `storeId`) VALUES
(1, '1234567896325', 'Laptop HP 4G Ram CPU 2.4', 200, 300, 1),
(2, '1254856932452', 'Laptop HP 2G Ram CPU 1.6', 100, 150, 1),
(3, '1289321785216', 'Laptop Dell 4G Ram CPU 2.0', 225, 315, 1),
(4, '9658321456987', 'Laptop Dell 6G Ram CPU 2.4', 275, 350, 1),
(5, '1452321852364', 'Printer HP lazerjet 11010', 35, 72, 2),
(6, '1289326478215', 'Printer HP lazerjet 1120', 42, 95, 2),
(7, '1235469852162', 'office table 2x3', 115, 225, 3),
(8, '1325732158426', 'office chair TXPU', 22.5, 45.5, 3);

2- Connection file to database.

create a file dbconnect.php to maintain the connection to database :

<?php


//Database Connection file. Update with your Database information .
    define ("DB_HOST", "localhost"); //Databse Host.
    define ("DB_USER", "root"); //Databse User.
    define ("DB_PASS", ""); //database password.
    define ("DB_NAME", "stores"); //database Name.
$db = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);


mysqli_set_charset($db,"utf8");
if($db->connect_errno > 0){
    die('Unable to connect to database ['.$db->connect_error.']');
}
?>

3- Home page (which will call the AJAX action).

Create a page named index.php.
This page will include the visual data. It means it will contain a dropdown list which contains the names of the stores and a HTML table which will dispaly the data extracted from the table items using AJAX.
insert in the index.php page the following code :

<div class="col-sm-12">
                <div class="title-header">
                    <h1 class="title">Populate table with data from MySql using AJAX</h1>
                </div>
                <div class="well">
                    <div class="form-group">
                        <div class="text-center">
                            <label>Select store</label><br>
                            <select name="store" id="store" style="width:50%" class="autofill "  Required >
                                <option value="">Select store</option>
                                <option value="1"> Store 1 </option>
                                <option value="2"> Store 2 </option>
                                <option value="3"> Store 3 </option>
                            </select>
                        </div>
                    </div>
                    <table id="items" class="table table-condensed table-hover table-bordered">
                        <tr>
                            <th>Barcode</th>
                            <th>Name</th>
                            <th >Cost Price</th>
                            <th >Selling Price</th>
                        </tr>
                        <tr class='item-row'>
                            <td colspan="4"></td>
                        </tr>
                        <input type="hidden" class="nbcomp" name="nbcomp" id="nbcomp" value="0"/>
                    </table>
                </div>
        </div>

As you can see, in the dropdown list , we added an id attribute id="stores" which we will use to create the change event when user click on the dropdown list to select the name of the store.
Another id attribute is added to the table <table id="items" class="..."> to identify the container that will receive the data.
And finally, in order to display the data extracted, we added a class attribute to the row of the table <tr class="item-row"> and this class will be used to identify the place where the data received will be displayed.

4- AJAX Request.

As we said before, the ajax event will be handled when the user select an option from the stores list.
So, when selecting an option, the folllowing Jquery event will be handeled :

 //when selecting an item from the dropdown
		$("#store").change(function() {
			$("#items tr>td").remove();
            $.ajax({
					data: {
					  store_id: $("#store").val()
					},
					type: 'POST',
					dataType: 'json',
					url: 'pages/get_items.php',
					success: function(response) {
					   var content = response.content;
					   $(".item-row:first").before(content);
					},
					error: function(xhr, status, error) {
						alert(xhr.responseText);
					}
            });
        });

Let's explain this code :
- $("#items tr>td").remove(); : this will remove any previous data from the row.
- store_id: $("#store").val() : object that will sent to the server with the request, this is the parameter of store identifier than , according to it,data will be retreived from the database .
- url: 'pages/get_items.php', : This is a mandatory parameter in the asynchronous http POST request. It contains the adress to which to send the request. A request will be sent to the page get_items.php which will provide the necessary data and send it back. The returned data will be ignored if no other parameter is specified.
- success: function(response) { .. : A callback function that is executed if the request succeeds. It takes as an argument response the returned data. It is also passed the text status of the response.
- $(".item-row:first").before(content); : Here we see the usefulness of the item-row class. The row will receive the returned data and display it.
- error: function(xhr, status, error) { alert(xhr.responseText);} : If any error occures during the ajax request, it will be displayed in an alert box.

5- Page handling the AJAX request

Now, we will create the page that handles the ajax request and returns the data to be displayed.
Create a file named get_items.php and insert the following code :

$store = $_POST['store_id'];
    if( isset($store)) { 
        $query = "SELECT * from items WHERE storeId='".$store."'";
        $result = $db->query($query) or die($db->error);
        $content = '';
        While ($row = $result->fetch_array()) {
            $barcode = mysqli_real_escape_string($db,$row['itemBarcode']);
            $name = mysqli_real_escape_string($db,$row['itemName']);
            $unitPrice =$row['itemUnitPrice'];
            $sellingPrice =$row['itemSellingPrice'];
            
            $content .='<tr class="item-row"><td><div class="delete-wpr">'.$barcode.'<a class="delme" href="javascript:;" title="Remove row">X</a></div></td>';
            $content .='<td>'.$name.'</td>';
            $content .='<td align="right">'.number_format($unitPrice,2).' $</td>';
            $content .='<td align="right">'.number_format($sellingPrice,2).' $</td>';
        }
        
            $storeData = array(
                        "content" => $content
                        );
        echo json_encode($storeData);
    }

11 items available

Scripts & Templates