Sort dynamic table in PHP

PHP
1

I have a dynamic table in php, and i want to sort specific columns after i run the filtering, below is where i up to, I’m having problem every time i click the headers column, the code has no error but instead to filter my data inside, the data in my table disappeared, only the column headers remains. Can someone tell what am i missing? Here’s my code.

<?php

$post_at = "";
$post_at_to_date = "";


if(isset($_POST['post_at']) && isset($_POST['post_at_to_date']))
{
    $order = (isset($_POST['orderBy']) && strcasecmp($_POST['orderBy'],'desc') == 0) ? 'DESC' : 'ASC';
    $post_at = $_POST['post_at'];
    $post_at_to_date = $_POST['post_at_to_date'];
    $cmbDept = $_POST['cmbDept'];
    $query ="SELECT * FROM daily_data2 WHERE Campaign LIKE '".$cmbDept."' and Checkdate between '{'" . $post_at . "'}' and '{'" . $post_at_to_date . "'}' DESC LIMIT 0,30" . $order ;

    $search_result = filterTable($query);

}
else {
    $query = "SELECT * FROM daily_data2 where Checkdate between '" . $post_at . "' and '" . $post_at_to_date . "'";
    $search_result = filterTable($query);
}

if(isset($_POST['cmbDept']))
{
    if(isset($_POST['post_at']) && isset($_POST['post_at_to_date']) && isset($_POST['cmbDept']))
    {
        $query ="SELECT * FROM daily_data2 WHERE Campaign = '".$cmbDept."' and Checkdate between '" . $post_at . "' and '" . $post_at_to_date . "'";
    }
    else {

        $query ="SELECT * FROM daily_data2 WHERE Campaign LIKE '" . $cmbDept . "'";
    }

    $search_result = filterTable($query);
 }  

 function filterTable($query)
 {
    $connect = mysqli_connect("localhost", "root", "","bio_db");
    $filter_Result = mysqli_query($connect, $query);
    return $filter_Result;
}               
?>


.table-content{border-top:#CCCCCC 4px solid; width:50%;}
.table-content th {padding:5px 20px; background: #F0F0F0;vertical-align:top;} 
.table-content td {padding:5px 20px; border-bottom: #F0F0F0 1px solid;vertical-align:top;} 


body
{
    background-size: 1370px 130px;
    background-repeat: no-repeat;

}

 <form name="frmSearch" method="POST" action="index.php">
 <p class="search_input">

    <form action="index.php" method="POST">
    <select id="cmbDept" name="cmbDept">
        <option value = '' selected="selected">Filter by Department</option>
            <option value = 'TKV'>TKV</option>
            <option value = 'NA'>NA</option>
            <option value = 'PURE-INC'>PURE INC</option>
            <option value = 'DUTY-FREE'>DUTY-FREE</option>
            <option value = 'HQL'>HQL</option>
            <option value = 'PRO-XPN'>PRO-XPN</option>
            <option value = 'Mate1'>Mate1</option>
            <option value = 'STUDENT-rUS'>STUDENT-rUS</option>
            <option value = 'COLLECTIONS'>COLLECTIONS</option>
            <option value = 'NTD'>NTD</option>
            <option value = 'DATA RESEARCHER'>DATA RESEARCHER</option>
            <option value = 'VA'>VA</option>

        </select>

    <input type="text" placeholder="From Date" id="post_at" name="post_at"  value="<?php echo $post_at; ?>" class="input-control" />
    <input type="text" placeholder="To Date" id="post_at_to_date" name="post_at_to_date" style="margin-left:10px"  value="<?php echo $post_at_to_date; ?>" class="input-control"  />

    <input type="submit" id="search" name="search" value="Search" >
</p>
 </form>

    <form method="POST" action="excel.php">
<input type="hidden" name="cmbDept" value="<?php echo isset($_POST['cmbDept']) ? $_POST['cmbDept'] : ''; ?>"> 
<input type="hidden" name="post_at" value="<?php echo isset($_POST['post_at']) ? $_POST['post_at'] : ''; ?>">
<input type="hidden" name="post_at_to_date" value="<?php echo isset($_POST['post_at_to_date']) ? $_POST['post_at_to_date'] : ''; ?>">
<input type="submit" name="export_excel" class="btn btn-success" value="Export to Excel">

  </form>               

    <form method="POST" action="index.php">             

   </form>  

    </center>

    <table align="center" width="600" border="1" cellpadding="1" cellspacing="1">
        <tr>
            <th><a href='?orderBy=" .($order == 'DESC' ? 'ASC' : 'DESC'). "'>Userid</a></th>
            <th><a href='?orderBy=" .($order == 'DESC' ? 'ASC' : 'DESC'). "'>Name</a></th>
            <th><a href='?orderBy=" .($order == 'DESC' ? 'ASC' : 'DESC'). "'>Campaign</a></th>
            <th><a href='?orderBy=" .($order == 'DESC' ? 'ASC' : 'DESC'). "'>Checkdate</a></th>
            <th><a href='?orderBy=" .($order == 'DESC' ? 'ASC' : 'DESC'). "'>Hoursworked</a></th>
            <th><a href='?orderBy=" .($order == 'DESC' ? 'ASC' : 'DESC'). "'>Overtime</a></th>
        </tr>

    <?php while($row = mysqli_fetch_array($search_result)):?>
        <tr>
            <td style="text-align:center;"><?php echo $row['Userid'];?></td>
            <td width="200"><?php echo $row['Name'];?></td>
            <td style="text-align:center;"><?php echo $row['Campaign'];?> </td>
            <td width="100" style="text-align:center;"><?php echo   $row['Checkdate'];?></td>
            <td style="text-align:center;"><?php echo $row['Hoursworked'];?></td>
            <td style="text-align:center;"><?php echo $row['Overtime'];?> </td>
        </tr>

    <?php endwhile;?>

        </table>
        </form> 
    <script src="http://code.jquery.com/ui/1.10.3/jquery-ui.js"></script>
    <script>
        $.datepicker.setDefaults({
        showOn: "button",
        buttonImage: "datepicker.png",
        buttonText: "Date Picker",
        buttonImageOnly: true,
        dateFormat: 'yy-mm-dd'  
    });
        $(function() {
        $("#post_at").datepicker();
        $("#post_at_to_date").datepicker();
    });
    </script>

   </body>
</html>
2

your trying to concat strings when not in PHP contenxt like <a href='?orderBy=" .($order == 'DESC' - have a look at your echo’s. but i don’t see you checking your statements, mysqli_error should return an error.

3

Don’t you need a space after the “LIMIT 0,30” and your $order value? I would expect a query ending “LIMIT 0,30DESC” would throw a syntax error.

4

One with DESC in it anywhere that doesn’t have a field name preceding the DESC will give a syntax error as it will not know which field to apply the DESC to.

5

Actually now I read it properly it makes even less sense, as there is no ORDER BY at all in the query, and it already contains DESC, then adds either DESC or ASC at the very end:

$query ="SELECT * FROM daily_data2 WHERE Campaign LIKE '".$cmbDept."' and Checkdate between '{'" . $post_at . "'}' and '{'" . $post_at_to_date . "'}' DESC LIMIT 0,30" . $order ;
6

hi, what should i do? should i remove the $order at the very end and replace it by ASC? can you please explain more? sorry i’m just a newbie and i’m eager to learn more in php. Thanks.

7

One thing you should do while developing code (but not on a live site) is to start your PHP files like

<?php
error_reporting(E_ALL);
ini_set('display_errors', 'true');

or have your php.ini file like

...
; Common Values:
;   E_ALL (Show all errors, warnings and notices including coding standards.)
;   E_ALL & ~E_NOTICE  (Show all errors, except for notices)
;   E_ALL & ~E_NOTICE & ~E_STRICT  (Show all errors, except for notices and coding standards warnings.)
;   E_COMPILE_ERROR|E_RECOVERABLE_ERROR|E_ERROR|E_CORE_ERROR  (Show only errors)
; Default Value: E_ALL & ~E_NOTICE & ~E_STRICT & ~E_DEPRECATED
; Development Value: E_ALL
; Production Value: E_ALL & ~E_DEPRECATED & ~E_STRICT
; http://php.net/error-reporting
error_reporting = E_ALL
...
; Default Value: On
; Development Value: On
; Production Value: Off
; http://php.net/display-errors
display_errors = On
...

You don’t want others to see error messages, but you being able to see them will help a lot.

8

No - assuming that contains the ORDER BY statement. You do need to remove the invalid DESC from earlier in the query.

ASC and DESC only go in the ORDER BY clause to indicate which way the field mentioned immediately previous to it in that clause is to be sorted.

Without an ORDER BY clause the data has no particular order. Also without a field name to refer to ASC and DESC are meaningless.

9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.