How to display MySQL database entries in HTML table using JavaScript?

display mysql data in html table using node js
how to retrieve data from database using javascript in html
how to fetch data from database in php and display in html table
how to retrieve data from database in html table using jquery
how to display data from database in php in table
how to retrieve data from database in html form
how to fetch data from database in php using ajax with example
displaying real data with table in html

I have successfully displayed the MySQL table in HTML data table, however, there is a bug that makes the display incorrect.

see image:

this is the code behind the display.

JAVASCRIPT:

 $(document).ready(function(){
                loadAccountList();
        });

        function loadAccountList() {

            $.ajax({
                /*url: "../srtdash/php_functions/account_list.php",
                type: "POST", 
                dataType: "JSON",
                data: {}, //this is data you send to your server*/
                    type: 'POST',
                    url: '../srtdash/php_functions/account_list.php',
                    dataType: 'json',
                    data: {},
                    contentType: 'application/json; charset=utf-8',
                success: function(res)
                {   
                        for (var i = 0; i < res.length; i++) {

                                var lst;

                                if (res[i]['status'] == 1 ){
                                    lst = '<h4><a href="#" class="badge badge-primary">Pending</a></h4>';
                                }else if (res[i]['status'] == 2 ){
                                    lst = '<h4><a href="#" class="badge badge-secondary">For Approval</a></h4>';
                                }else if (res[i]['status'] == 3 ) {
                                    lst = '<h4><a href="#" class="badge badge-success">For CAD</a></h4>';
                                }else if (res[i]['status'] == 4 ){
                                    lst = '<h4><a href="#" class="badge badge-danger">For Appraisal</a></h4>';
                                }else if (res[i]['status'] == 5 ){
                                    lst = '<h4><a href="#" class="badge badge-info">Release</a></h4>';
                                }

                          $('#tableBody').append('<tr><td hidden>' + res[i]['account_id'] 
                            + '</td><td>' + res[i]['bvcsi_control'] 
                            + '</td><td>' + res[i]['account_name'] 
                            + '</td><td>' + res[i]['date_inspection'] 
                            + '</td><td>' + res[i]['date_report'] 
                            + '</td><td>' + res[i]['act_useof_prop'] 
                            + '</td><td>' + res[i]['landmark']
                            + '</td><td>' + res[i]['reg_owner']     
                            + '</td><td>' + lst
                            + '</td></tr>')
                        }
                }       
            });
    }

HTML:

                    <div class="data-tables">
                        <table id="dataTable" class="text-center" >
                            <thead class="bg-light text-capitalize">
                                <tr>
                                    <th hidden>User ID</th>
                                    <th>Control No.</th>
                                    <th>Account Name</th>
                                    <th>Date Inspection</th>
                                    <th>Date of Report</th>
                                    <th>Actual Use</th>
                                    <th>Landmark</th>
                                    <th>Registered Owner</th>
                                    <th>Status</th>
                                </tr>
                            </thead>
                            <tbody id="tableBody">
                            </tbody>
                        </table>
                    </div>

And also the error, when I search using the textbox in datatable is not filtering the table it only states that No data available in table

you need to check several things.

  1. make sure your Search textfield working properly each time you type filter text and enter on it. i see your code pass nothing to server. ( data: {} here.)

  2. check whether the you receiving filtered data from account_list.php ajax call.

  3. try check on your query in account_list.php. seems your Search data should query with Account Name column. like.. ACOUNT_NAME like '%filter%'

you need to check the results on success block.

success: function(res) <- here. the "res"

additionally,

it would be better to add below success block for tracking errors. i assume that this case can be a result error or something unexpected processes on your query.

example :

error:function(e)
{  
              alert(e.responseText);  
}

Secondary, each time you success the ajax call you probably needs clearing "tableBody" table above. otherwise, it will stack previous data you searched.

How to Display Data from MySQL Database into HTML Table using , Do you want to learn, How to show data from MySQL database on HTML Table using PHP Duration: 3:11 Posted: Oct 10, 2017 5. Create a Main Index Page. Now create a main index. php page which contain the actual script of PHP which will show data from database into HTML table and also it will store all values in HTML array and on form submission, it will catch all selected values and print them on web page, you can also store it in your database.

What you're doing wrong is that you don't APPEND to the table. You're using a datatable. You want to do $("#dataTable").dataTable().rows.add(["col1","col2",..., "colLast"]).draw(); and then you can search the table. DataTable doesn't search the rows IN the table, it searches the rows that you ADD to the table with its own method.

The draw is important. If you don't add the .draw() at the end, it won't actually show the changes that were done.

$('#dataTable').dataTable().rows.add([res[i]['account_id'],
                            res[i]['bvcsi_control'],
                            res[i]['account_name'],
                            res[i]['date_inspection'],
                            res[i]['date_report'],
                            res[i]['act_useof_prop'],
                            res[i]['landmark'],
                            res[i]['reg_owner'],     
                            lst]).draw();

This assumes your table has an id of "table"

Select in MySQL, Output results in HTML Table, To select data from a table in MySQL database, use the "SELECT" statement. example we select all records with ID between 1 and 4 from the "friends"table, and display Save the code above in a file called "mysql_select.js" and run the file: This post is about data retrieving from MySQL database table using PHP. The bellow PHP script will give you HTML table output using MySQL table data. Id First Name Last Name Contact Number Created No Results found.

Datatables is extremely powerful - if you use the features it has, and don't try to build your own (which is sort of what you were trying to do.....)

Your HTML is OK (you don't need the 'id' in the tbody for datatables).

Your JS needs a lot of cleanup (and letting datatables do its job!)

$(document).ready(function(){
    var theTable = $("dataTable").dataTable({
        "ajax": {
            url: "../srtdash/php_functions/account_list.php"
            // this presumes you are returning a json list of info you want 
            // (since you see it in the first example, it looks like it is OK)
            // see https://datatables.net/examples/data_sources/ajax.html
        },
        responsive: true,  // if you want a responsive table
        "order": [0, 'desc'],  // make the order as you like (count starts at column 0!)
        "columns": [
            {data: 'account_id'},
            {data: "bvcsi_control"},
            {data: 'account_name'},
            {data: "date_inspection"},
            {data: 'date_report'},
            {data: 'act_useof_prop'},
            {data: 'landmark'},
            {data: 'reg_owner'},
            {data: 'status'}
        ],
        columnDefs: [
            {
                // here is where you do your 'button' stuff (or anything you want to change in the table....)
                // 'data' is the info of the column
                render: function (data, type, row) {
                    var lst;
                    if (data === 1 ){
                       lst = '<h4><a href="#" class="badge badge-primary">Pending</a></h4>';
                    }else if (data === 2 ){
                        lst = '<h4><a href="#" class="badge badge-secondary">For Approval</a></h4>';
                    }else if (data === 3 ) {
                         lst = '<h4><a href="#" class="badge badge-success">For CAD</a></h4>';
                    }else if (data === 4 ){
                         lst = '<h4><a href="#" class="badge badge-danger">For Appraisal</a></h4>';
                    }else if (data === 5 ){
                         lst = '<h4><a href="#" class="badge badge-info">Release</a></h4>';
                    }                        
                    return data;
                },
                targets: [8] // changes Status to a button
            }
        ]
    })
});

There's some other cleanup I would do (for example, not duplicating the HTML in the 'lst' lines), though this should get you going - and using datatables like it 'should' be used (utilizing its power to do the work for you!)

How to Fetch Data from Database in PHP and display in HTML Table?, Make a connection between the HTML Table and MySQL database using PHP. If that result contains more than 0 rows then display that data in an HTML table. We will see here how to display all tables inside a MySQL database using Java. You can use show command from MySQL to get all tables inside a MySQL database. Let’s say our database is ‘test’. The Java code is as follows to show all table names inside a database ‘test’. The Java code is as follows.

PHP AJAX and MySQL, The database table we use in the example above looks like this: runs a query against a MySQL database, and returns the result in an HTML table: <! - The Result Object - Output data from Select result in HTML Table Selecting From a MySQL Table To select data from a table in MySQL database, use the "SELECT" statement. - In this example we select all records with ID between 1 and 4 from the "friends"table, and display the return object:

AJAX Database, AJAX can be used for interactive communication with a database. The page on the server called by the JavaScript above is a PHP file called "getcustomer.php". The source code in "getcustomer.php" runs a query against a database, and returns the result in an HTML table: <?php echo "<th>CompanyName</th>"; The basic unit of structure in SQL is called a table because it’s usually displayed in a tabular format. HTML5 also has a table structure, which is ideal for outputting SQL data. ContactTable.php, displays the contact information inside an HTML table. Tables are a very common way to output SQL results. There’s one big difference …

Displaying Database table data on Html table tag, In this… don't want to call it a tutorial, let me just say a heads-up-ish, we'll build a simple webapp using golang and MySql and a new-gaining� The page on the server called by the JavaScript above is a PHP file called "getcustomer.php". The source code in "getcustomer.php" runs a query against a database, and returns the result in an HTML table:

Comments
  • @Emma Ok Ma'am. I'll do it.
  • @Emma Hi Ma'am. I dont have enough reputations that's why i cannot insert an image.
  • @Emma Hi again Ma'am. I just did it