How to Sort Rows of HTML Table That Are Called from MySQL

How to sort rows of HTML table that are called from MySQL

The easiest way to do this would be to put a link on your column headers, pointing to the same page. In the query string, put a variable so that you know what they clicked on, and then use ORDER BY in your SQL query to perform the ordering.

The HTML would look like this:

<th><a href="mypage.php?sort=type">Type:</a></th>
<th><a href="mypage.php?sort=desc">Description:</a></th>
<th><a href="mypage.php?sort=recorded">Recorded Date:</a></th>
<th><a href="mypage.php?sort=added">Added Date:</a></th>

And in the php code, do something like this:

<?php

$sql = "SELECT * FROM MyTable";

if ($_GET['sort'] == 'type')
{
    $sql .= " ORDER BY type";
}
elseif ($_GET['sort'] == 'desc')
{
    $sql .= " ORDER BY Description";
}
elseif ($_GET['sort'] == 'recorded')
{
    $sql .= " ORDER BY DateRecorded";
}
elseif($_GET['sort'] == 'added')
{
    $sql .= " ORDER BY DateAdded";
}

$>

Notice that you shouldn't take the $_GET value directly and append it to your query. As some user could got to MyPage.php?sort=; DELETE FROM MyTable;

How to sort a html table column by clicking the header [PHP/MYSQL]

There are various js plugins to help achieve data table sorting such as easyui, datatables etc.

If you only want to achieve it with simple code like you provided, you have to retrieve the sortBy variable and put it in your SQL query:

$connection = mysqli_connect('localhost', 'root', '','nba201819'); //The Blank string is the password

if (isset($_GET['sortBy'])) {
    if ($_GET['sortBy'] !== '') {
        $sortBy = str_replace( "`", "``", $_GET['sortBy']);
    } else {
        $sortBy = 'WIN%';
    }
} else {
    $sortBy = 'WIN%';
}

$result = mysqli_query($connection,"SELECT * FROM `teamstats` ORDER BY `teamstats`.`$sortBy` DESC");

Sort table and color column

The following code is not optimized but does what you want to achieve.

$number = 1;

foreach($results as $row){
    echo '
        <tr class="statistikker">
            <td style="background: #666; text-align: center; color: white; font-weight: bold;">'.$number.'</td>
            <td style="padding-left: 5px">'.$row->name.'</td>
            <td class="center '.($_GET['sort'] == 'matches' ? 'selected' : '').'">'.$row->matches.'</td>
            <td class="center '.((!isset($_GET['sort']) || $_GET['sort'] == 'goal' ? 'selected' : '').'">'.$row->goal.'</td>
            <td class="center '.($_GET['sort'] == 'assist' ? 'selected' : '').'">'.$row->assist.'</td>
            <td class="center '.($_GET['sort'] == 'cards' ? 'selected' : '').'">'.$row->cards.'</td>
        </tr>';
    $number++;
};

Update: I added the !isset($_GET['sort']) on the goal td elements to reflect the OP comment below stating goal was the standard sort parameter.

It adds the class selected to the relevant td elements. You can then create a CSS rule for this selected class that changes the background-color property like so:

.selected {
    background-color: red;
}

HTML table sort

Check if you could go with any of the below mentioned JQuery plugins. Simply awesome and provide wide range of options to work through, and less pains to integrate. :)

https://github.com/paulopmx/Flexigrid - Flexgrid

http://datatables.net/index - Data tables.

https://github.com/tonytomov/jqGrid

If not, you need to have a link to those table headers that calls a server-side script to invoke the sort.

How to sort MySql rows of HTML table headers

here the solution hope this will help you

<?php

$sql = "SELECT log_date, log_id, log_time, network_protocal, client_name, client_ip
 FROM log_table $sort";

$sort = "";
    if(isset($_GET['sort'])) {
        switch ($_GET['sort'] ) {
        case 0: 
                    $sort = " ORDER BY log_date ASC"; 
                    break;
                     case 1: 
                    $sort = " ORDER BY log_date DESC"; 
                    break;
                     case 2: 
                    $sort = " ORDER BY log_time ASC"; 
                    break;
                     case 3: 
                    $sort = " ORDER BY log_time DESC"; 
                    break;
                     case 4: 
                    $sort = " ORDER BY network_protocal ASC"; 
                    break;
                    case 5: 
                    $sort = " ORDER BY network_protocal DESC"; 
                    break;
                    case 6: 
                    $sort = " ORDER BY client_name ASC"; 
                    break;
                    case 7: 
                    $sort = " ORDER BY client_name DESC"; 
                    break;
                    case 8: 
                    $sort = " ORDER BY client_ip ASC"; 
                    break;
                    case 9: 
                    $sort = " ORDER BY client_ip DESC"; 
                    break;

        }
    }

    ?>

    <div class="container">

    <table class="table table-striped">
    <tr>
    <td>Date</td>
    <td>Time</a></td>
    <td>Network Protocol</td>
    <td>Client Name</td>
    <td>Client IP</td>
    </tr>
    <?php foreach ($logs as $log) { ?>
    <tr>
        <td>
    <a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=0";?>" >▲</a>
    <?php if (isset($log->log_date)) echo (string)$log->log_date; ?>
        <a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=1";?>" >▼</a>
        </td>
        <td>
        <a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=2";?>" >▲</a>
    <?php if (isset($log->log_time)) echo (string)$log->log_time; ?>
        <a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=3";?>" >▼</a>
        </td>
        <td>
        <a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=4";?>" >▲</a>
    <?php if (isset($log->network_protocal)) echo (string)$log->network_protocal; ?>
        <a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=5";?>" >▼</a>
        </td>
        <td>
        <a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=6";?>" >▲</a>
    <?php if (isset($log->client_name)) echo (string)$log->client_name; ?>
        <a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=7";?>" >▼</a>
        </td>
        <td>
        <a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=8";?>" >▲</a>
        <?php if (isset($log->client_ip)) echo (string)$log->client_ip; ?>
        <a href="<?php echo $_SERVER['PHP_SELF'] . "?sort=9";?>" >▼</a>
        </td>
        </tr>
        <?php } ?>
        </table>

HTML TABLE PHP MySQL toggle MySQL sorting order ASC DESC on column header click

If you are already sending the
sortby
via GET, why wouldn't you send the
ASC,DESC
Option too, I mean, you could use 1 and 0 instead of the actual ASC/DESC and toggle it inside your code,for example:

$ascdesc = ($_GET['ad'])? '0' : '1';

And just add the var to the link

echo "<th>".'<a href="showdbtable.php?sortby=negozio&ad='.$ascdesc.'">' . "Negozio</th>";

And in you query, something like

 $ascdesc = ($_GET['ad'])? 'asc' : 'desc';

Something very important here is that if you are accepting user input via GET, you have to sanitize the var to avoid SQL injections, don't forget this.

UPDATE:

Possible implementation with your own code:

$sortby = (isset($_GET['sortby']))? $_GET['sortby'] : "id_ord";
$ascdesc = ($_GET['ad']=='asc')? 'ASC' : 'DESC';

The Query:

$query = "SELECT id_ord, fornitore, negozio, data_insord, data_prevcons 
          FROM tesord 
          ORDER BY ".$sortby." ".$ascdesc." 
          LIMIT :from_record_num, :records_per_page";

The link:

echo "<th>".'<a href="showdbtable.php?sortby=fornitore&ad=<?=(($_GET['ad']=='asc')? 'desc' : 'asc';)?>">' . "Fornitore</th>";

And if you need to add the page, just add the current page and change sort, you can add a var also to the link

echo "<th>".'<a href="showdbtable.php?sortby=fornitore&ad=<?php echo (($_GET['ad']=='asc')? 'desc' : 'asc';)?>&page=<?php echo $_GET['page]?>">' . "Fornitore</th>";

There are many other ways to handle the pagination and sorting, but I think that, without getting into a lot of trouble, this could be a way, however, about the security, you can use mysql_real_escape

You could also leave everything to javascript/jQuery by implementing something like this

Hope this can give you a better understanding, happy coding.

PHP/HTML: How to sort an array by clicking on the headers of a table?

If you want to do this all in PHP, the first step would be to convert the text in the column headers to links, specifying the column in a query string.

<td style="color:white">
    <a href="thispage.php?sort=projectname">Project Name</a>
</td>

Then in your controller, (or whatever script you have that generates the table), you'll have access to the column you clicked in $_GET['sort'], and you can use that in an ORDER BY clause in your query.

You won't be able to pass a column name as a parameter to a prepared statement, so you'll have to add that dynamically to your SQL. It's a potential SQL injection vulnerability, so be sure you verify that $_GET['sort'] contains a valid column name before just using it in the query.

---

Related Topics