0

i have a table fieldname like companyname and Created Date shown in below image. in that i want to display data's date wise ASC or DESC, like that if i click companyname the data's will show ASC and again if click will show DESC order format.

and here i added pagination coding also

please anyone know about this help me

echo "<table border='1' align='center'>";
echo '<tr><th>Company Name</th><th>Main Category</th><th>City</th><th>Place</th><th>Created Date <a href="javascript:function orderbydate();">d</a></th><th>Download</th><th>Edit</th><th>Delete</th></tr>';

    include("db.php");  
    $tbl_name="spa";        //your table name
    // How many adjacent pages should be shown on each side?
    $adjacents = 3;

    /* 
       First get total number of rows in data table. 
       If you have a WHERE clause in your query, make sure you mirror it here.
    */

    $query = "SELECT COUNT(*) as num FROM spa WHERE Publish=1 ORDER BY id DESC";
    $total_pages = mysql_fetch_array(mysql_query($query));
    $total_pages = $total_pages[num];

    /* Setup vars for query. */
    $targetpage = "edit.php";   //your file name  (the name of this file)
    $limit = 30;                                //how many items to show per page
    $page = $_GET['page'];
    if($page) 
        $start = ($page - 1) * $limit;          //first item to display on this page
    else
        $start = 0;                             //if no page var is given, set start to 0

    /* Get data. */
    $sql = "select DISTINCT s.Companyname,s.id,s.CreatedDate,ck.MainCatagory,c.city,st.state,cp.state_id,cp.city_id,rc.mainCatagoryid from spa s 
left join relatedcatagories rc on rc.spaid=s.id
left join catagorykeywords ck on ck.id=rc.mainCatagoryid
left join subcatagorykeywords sub on sub.id=rc.RelatedCatagory
left join city_place cp on cp.spaid=s.id
left join states st on st.state_id=cp.state_id
left join cities c on c.city_id=cp.city_id 
 WHERE s.Publish=1 ORDER BY s.id DESC LIMIT $start,$limit";

    $res = mysql_query ($sql);
    /* Setup page vars for display. */
    if ($page == 0) $page = 1;                  //if no page var is given, default to 1.
    $prev = $page - 1;                          //previous page is page - 1
    $next = $page + 1;                          //next page is page + 1
    $lastpage = ceil($total_pages/$limit);      //lastpage is = total pages / items per page, rounded up.
    $lpm1 = $lastpage - 1;                      //last page minus 1

    /* 
        Now we apply our rules and draw the pagination object. 
        We're actually saving the code to a variable in case we want to draw it more than once.
    */
    $pagination = "";
    if($lastpage > 1)
    {   
        $pagination .= "<div class=\"pagination\">";
        //previous button
        if ($page > 1) 
            $pagination.= "<a href=\"$targetpage?page=$prev\"> Previous</a>";
        else
            $pagination.= "<span class=\"disabled\"> Previous</span>";    

        //pages 
        if ($lastpage < 7 + ($adjacents * 2))    //not enough pages to bother breaking it up
        {   
            for ($counter = 1; $counter <= $lastpage; $counter++)
            {
                if ($counter == $page)
                    $pagination.= "<span class=\"current\">$counter</span>";
                else
                    $pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";                   
            }
        }
        elseif($lastpage > 5 + ($adjacents * 2))    //enough pages to hide some
        {
            //close to beginning; only hide later pages
            if($page < 1 + ($adjacents * 2))     
            {
                for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
                {
                    if ($counter == $page)
                        $pagination.= "<span class=\"current\">$counter</span>";
                    else
                        $pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";                   
                }
                $pagination.= "...";
                $pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
                $pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";     
            }
            //in middle; hide some front and some back
            elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
            {
                $pagination.= "<a href=\"$targetpage?page=1\">1</a>";
                $pagination.= "<a href=\"$targetpage?page=2\">2</a>";
                $pagination.= "...";
                for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
                {
                    if ($counter == $page)
                        $pagination.= "<span class=\"current\">$counter</span>";
                    else
                        $pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";                   
                }
                $pagination.= "...";
                $pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
                $pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";     
            }
            //close to end; only hide early pages
            else
            {
                $pagination.= "<a href=\"$targetpage?page=1\">1</a>";
                $pagination.= "<a href=\"$targetpage?page=2\">2</a>";
                $pagination.= "...";
                for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
                {
                    if ($counter == $page)
                        $pagination.= "<span class=\"current\">$counter</span>";
                    else
                        $pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";                   
                }
            }
        }

        //next button
        if ($page < $counter - 1) 
            $pagination.= "<a href=\"$targetpage?page=$next\">Next </a>";
        else
            $pagination.= "<span class=\"disabled\">Next </span>";
        $pagination.= "</div>\n";        
    }





              while($row = mysql_fetch_array($res))
                   {
                      $spaid = $row['id'];
                      $spaname= $row['Companyname'];
                      $catagory = $row['Catagory'];
                      $MainCatagory = $row['MainCatagory'];
                      $CreatedDate = $row['CreatedDate'];
                      $place = $row['city_id'];
        $arr = array($place);
        foreach($arr as $place1) 
        //echo $place1;
                      $state = $row['state'];


                      echo '<tr><td>'.$spaname.'</td><td>'.$MainCatagory.'</td><td>'.$state.'</td><td>'.$place1.'</td><td>'.$CreatedDate.'</td>
                      <td><a href="download1.php?id='.$spaid.'">Download</a></td>
                      <td align="center"><a href="editdb.php?id='.$row['id'].'&stateid='.$row['state_id'].'&cityid='.$row['city_id'].'+,'.$row['city_id'].'&categorid='.$row['mainCatagoryid'].' ">EDIT</a></td><td align="center"><a href="delete.php?id='.$row['id'].'" onclick="return confirm(\'Are you sure you wish to delete this record\');">DELETE</a></td></tr>';  



                    }


        echo "</table>";  ?>
        <div class="paging">
    <?=$pagination?>
     </div>

Edited by malatamil: gh

Attachments ff.png 32.78 KB
2
Contributors
2
Replies
24
Views
3 Years
Discussion Span
Last Post by malatamil
0

I personally think sortable tables took best if done with ajax so there are no page refreshes, but in your case, the easiest way would be to pass a $_GET variable to your url everytime someone clicks on a table header, then put your $query in a conditional statement.

For example:

// Test URL:  www.yoursite.com/somepage.php?orderby=company_name&order=DESC

if($_GET['orderby'] == "company_name" && $_GET['order'] == "DESC") {
    $query = query_to_get_DESC_company_names
} elseif .... and so on
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.