i tried to fetch data to a mysql table using this

$query=$conn->query("SELECT * FROM users ORDER BY id_user ASC");

this is my table structure

users

id_user  | INT auto-increment
username | varchar
password | varchar
nama     | varchar
role     | varchar
status   | SET

but the displayed data order is ordered like this 1,10,11,12,13,2,3,4,5,6,7,8,9 (13 data in database)

Edit: full code to display

<table cellpadding="0" cellspacing="0" border="0" class="table table-striped table-bordered" id="example">
        <thead>
            <tr>
                <th align="center">ID</th>
                <th align="center">Username</th>
                <th align="center">Nama</th>
                <th align="center">Role</th>
                <th width="13%" align="center">Status</th>      
                <th width="7%" align="center">Edit</th> 
                <th width="8%" align="center">Hapus</th>
            </tr>
        </thead>
        <?php
        $query=$conn->query("SELECT * FROM users ORDER BY id_user ASC");
        while($row=$query->fetch()){
            $id=$row['id_user'];
            $name=$row['username'];
            $nama=$row['nama'];
            $role=$row['role'];
            $status=$row['status'];
        ?>  
        <tr>
            <td>
                &nbsp;<?php echo $id ;?>
            </td>
            <td>
                &nbsp;<?php echo $name ;?>
            </td>
            <td>
                &nbsp;<?php echo $nama ;?>
            </td>
            <td>
                &nbsp;<?php echo $role ;?>
            </td>
            <td>
                &nbsp;<?php echo $status ;?>
            </td>
            <td>
                <a href="edituser.php?id=<?php echo $id;?>"><button class="alert-success">Edit</button></a>
            </td>
            <td>
              <a href="deleteuser.php?id=<?php echo $id;?>&role=<?php echo $role;?>" onclick="return confirm('Apa anda yakin akan menghapus user ini?');"><button class="alert-success">Delete</button></a></td>
            </td>
        </tr>
        <?php }?>
    </table>

added script for datatable but it return error cannon reinitialize data table

    $(document).ready(function (){
    var table = $('#example').dataTable({
   "order": [[ 0, 'asc' ]]
   });    
  });

i tried to fetch data to a mysql table using this

$query=$conn->query("SELECT * FROM users ORDER BY id_user ASC");

this is my table structure

users

id_user  | INT auto-increment
username | varchar
password | varchar
nama     | varchar
role     | varchar
status   | SET

but the displayed data order is ordered like this 1,10,11,12,13,2,3,4,5,6,7,8,9 (13 data in database)

Edit: full code to display

<table cellpadding="0" cellspacing="0" border="0" class="table table-striped table-bordered" id="example">
        <thead>
            <tr>
                <th align="center">ID</th>
                <th align="center">Username</th>
                <th align="center">Nama</th>
                <th align="center">Role</th>
                <th width="13%" align="center">Status</th>      
                <th width="7%" align="center">Edit</th> 
                <th width="8%" align="center">Hapus</th>
            </tr>
        </thead>
        <?php
        $query=$conn->query("SELECT * FROM users ORDER BY id_user ASC");
        while($row=$query->fetch()){
            $id=$row['id_user'];
            $name=$row['username'];
            $nama=$row['nama'];
            $role=$row['role'];
            $status=$row['status'];
        ?>  
        <tr>
            <td>
                &nbsp;<?php echo $id ;?>
            </td>
            <td>
                &nbsp;<?php echo $name ;?>
            </td>
            <td>
                &nbsp;<?php echo $nama ;?>
            </td>
            <td>
                &nbsp;<?php echo $role ;?>
            </td>
            <td>
                &nbsp;<?php echo $status ;?>
            </td>
            <td>
                <a href="edituser.php?id=<?php echo $id;?>"><button class="alert-success">Edit</button></a>
            </td>
            <td>
              <a href="deleteuser.php?id=<?php echo $id;?>&role=<?php echo $role;?>" onclick="return confirm('Apa anda yakin akan menghapus user ini?');"><button class="alert-success">Delete</button></a></td>
            </td>
        </tr>
        <?php }?>
    </table>

added script for datatable but it return error cannon reinitialize data table

    $(document).ready(function (){
    var table = $('#example').dataTable({
   "order": [[ 0, 'asc' ]]
   });    
  });

• javascript
• jquery
• datatables

• 1 id_user data type? – Devsi Odedra Commented May 1, 2018 at 10:41
• 2 Is id_user a string? – ggdx Commented May 1, 2018 at 10:41
• 4 If it is int it should print the users in correct order. Show us your entire code. @BillyAnthony – Alok Patel Commented May 1, 2018 at 10:44
• 1 @BillyAnthony, if you execute the same query SELECT * FROM users ORDER BY id_user ASC in phpmyadmin, do you get the same order of records? – Himanshu Upadhyay Commented May 1, 2018 at 10:45
• 1 with print_r($row['id_user'] its giving proper order output.. maybe jquery.datatable.js is messing with the order – Billy Anthony Commented May 1, 2018 at 11:14

2 Answers 2

Male sure "id_user" field is of "int" data type in DB Table.

And after that try using "aaSorting". Ref: http://legacy.datatables/release-datatables/examples/basic_init/table_sorting.html

$(document).ready(function() {
    $('#example').dataTable( {
        "aaSorting": [[ 0, "asc" ]]
    } );
} );

try this :

 <td>
   <?php echo $id ;?>
  </td>

Just remove   from display record

for centered data you can use the text-center bootstrap class