Display database records in jQuery data tables

Question

I want to fetch records from database and display it in data tables, but when i run this PHP code in a browser, it only shows records in a simple table. I want this result in jquery data tables for searching and sorting.

javascript function for data tables (uses jquery.dataTables.js)

    <script type="text/javascript" language="javascript">
        $(document).ready(function() {
        ('#datatable').DataTable();
        })
    </script>

mysql function for fetching records

    <?php 
        $con=mysql_connect("localhost","root","");
        mysql_select_db("runindia",$con);
        $query="select *from admin_login";
        $rs=mysql_query($query,$con);
   ?>
<div class="container">
   <table class="table table-bordered table-responsive table-hover display" id="datatable">
         <thead>
             <th> Admin ID</th>
             <th> User Name</th>
             <th> First Name</th>
             <th> Last Name</th>
             <th> Email</th>
        </thead>
       <?php 
            while($r=mysql_fetch_array($rs))
       { ?>

       <tbody>
           <tr>
                <td><?php echo $r['admin_id'];?></td>
                <td><?php echo $r['username'];?></td>
                <td><?php echo $r['first_name'];?></td>
                <td><?php echo $r['last_name'];?></td>
                <td><?php echo $r['email'];?></td>
           </tr>
      </tbody>
   <?php  
   } //closing while
   mysql_close($con);//mysql connection close
   ?>
 </table>

mysql_* functions are deprecated. use mysqli_* or PDO instead. — fsperrle
– fsperrle, Commented Oct 5, 2014 at 9:20
@Fabian, mysql_* functions was deprecated in PHP 5.5.0, which is still only used by about 4% of all PHP installations. The chance for OP are using a PHP less than 5.5.0, and therefore are using a PHP where mysql_* is not deprecated is extremely high. — davidkonrad
– davidkonrad, Commented Oct 6, 2014 at 7:59
It's still deprecated, i.e. the usage is discouraged. When starting a new project that might be around for a while it's good not to rely on deprecated methods. Especially in this case where adding a simple i to your method call is enough. — fsperrle
– fsperrle, Commented Oct 6, 2014 at 8:27

Sameer Shemna · Accepted Answer · 2014-10-05 09:59:57Z

1

Try keeping 'tbody' out of the loop:

<table class="table table-bordered table-responsive table-hover display" id="datatable">
         <thead>
             <th> Admin ID</th>
             <th> User Name</th>
             <th> First Name</th>
             <th> Last Name</th>
             <th> Email</th>
        </thead>
<tbody>
       <?php 
            while($r=mysql_fetch_array($rs))
       { ?>


           <tr>
                <td><?php echo $r['admin_id'];?></td>
                <td><?php echo $r['username'];?></td>
                <td><?php echo $r['first_name'];?></td>
                <td><?php echo $r['last_name'];?></td>
                <td><?php echo $r['email'];?></td>
           </tr>

   <?php  
   } //closing while
   mysql_close($con);//mysql connection close
   ?>
</tbody>
 </table>

Or try something better like getting the data via an AJAX call in a JSON format

answered Oct 5, 2014 at 9:59

Sameer Shemna

88611 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

davidkonrad Over a year ago

Sorry to say, but I cannot see how leaving <tbody> out should make any difference at all.

Sameer Shemna Over a year ago

its because there is supposed to be just one 'tbody' inside a 'table', and your loop will cause it to have multiple 'tbody's

davidkonrad Over a year ago

Oh yes - you are completely right! Thanks for clearing the reason for your answer out!

Sameer Shemna Over a year ago

Did you also use @davidkonrad 's suggestion to change ('#datatable').DataTable(); to $('#datatable').DataTable(); ?

Sameer Shemna Over a year ago

Also check your SQL query: $query="select *from admin_login"; you are missing a space after *

|

Hendra Nucleo · Accepted Answer · 2014-10-05 10:14:16Z

<?php 
        $con=mysqli_connect("localhost","root","", "runindia");
        $query="select * from admin_login";
        $rs=mysqli_query($con, $query);
   ?>
<div class="container">
   <table class="table table-bordered table-responsive table-hover display" id="datatable">
         <thead>
             <th> Admin ID</th>
             <th> User Name</th>
             <th> First Name</th>
             <th> Last Name</th>
             <th> Email</th>
        </thead>
       <?php 
            while($r = mysqli_fetch_array($rs, MYSQLI_ASSOC))
       { ?>

       <tbody>
           <tr>
                <td><?php echo $r['admin_id'];?></td>
                <td><?php echo $r['username'];?></td>
                <td><?php echo $r['first_name'];?></td>
                <td><?php echo $r['last_name'];?></td>
                <td><?php echo $r['email'];?></td>
           </tr>
      </tbody>
   <?php  
   } //closing while
   mysqli_close($con);//mysqli connection close
   ?>
 </table>

Collectives™ on Stack Overflow

Display database records in jQuery data tables

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related