How To Apply Sorting And Filtering Using Ajax Json Php & Mysql

Hi i am a beginner i am working on software site. i have built all the pages and layout for the site that was the easy part done using HTML CSS AND JAVASCRIPT alone, only thing lef

Solution 1:

First thing worth noting is, if you are going to display tabular data... Use a table! It will make things a lot easier for you.

Secondly. Build your code and table as if Ajax did not exist. Initially populate the data using PHP on the page your displaying the data. Then, hook up the column header's so they link to your page, but passing which column you want to sort by and also which direction.

i.e.

<?$column = (isset($_GET["column"]) ? $_GET["column"] : 'id'); 
    $direction = (isset($_GET['direction']) ? $_GET['direction'] : 'asc');
    $con = mysql_connect('localhost', 'root', '');
    if (!$con)
    {
        die('Could not connect: ' . mysql_error());
    }

    mysql_select_db("security_software", $con);
    $sql="SELECT * FROM internet_security ORDER by '".$column."' " . $direction;


    $result = mysql_query($sql);
    $response = array();
    $i=0;
    while($row = mysql_fetch_array($result))
    {
        $response[$i]['id']           =$row['id'];
        $response[$i]['title']        = $row['title'];
        $response[$i]['image']        = $row['image'];
        $response[$i]['description']  = $row['description'];
        $response[$i]['rating']       = $row['rating'];
        $response[$i]['download']     = $row['download'];  
        $response[$i]['buy']          = $row['buy'];
        $i++;
    }
    mysql_close($con); 
?><divid="content"><table><thead><tr><td><ahref="table.php?column=id<?= (isset($_GET['column']) && $_GET['column'] == 'id' && !isset($_GET['direction']) ? '&direction=desc' : ''); ?>">ID</a></td><td><ahref="table.php?column=title<?= (isset($_GET['column']) && $_GET['column'] == 'title' && !isset($_GET['direction']) ? '&direction=desc' : ''); ?>">Title</a></td><td><ahref="table.php?column=rating<?= (isset($_GET['column']) && $_GET['column'] == 'rating' && !isset($_GET['direction']) ? '&direction=desc' : ''); ?>">Rating</a></td><td><ahref="table.php?column=download<?= (isset($_GET['column']) && $_GET['column'] == 'download' && !isset($_GET['direction']) ? '&direction=desc' : ''); ?>">Download</a></td></tr></thead><tbody><?foreach($responseas$i => $row) : ?><tr><td><?=$row['id']; ?></td><td><?=$row['title']; ?></td><td><?=$row['rating']; ?></td><td><?=$row['download']; ?></td></tr><?endforeach; ?></tbody></table></div>

The above code would go inside a single PHP file, without any other HTML etc. Then, on the page you want to display this table, you simply <? include('path-to-file.php'); ?> include it.

Finally... At the top of the page you are displaying the table on, you would put:

<?if(!empty($_SERVER['HTTP_X_REQUESTED_WITH']) && strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) == 'xmlhttprequest')
    {
        include('path-to-file.php');
        die();
    }
?>

The above code would then detect an Ajax request and serve only the table with the data in the new order.

You would then need to use Javascript to replace the table with the new HTML via

$('#content table thead a').live('click', function(e)
{
    e.preventDefault();
    $.ajax(
    {
        url : $(this).attr('href'),
        success : function(resp)
        {
            $('#content').html($(resp).html());
        },
        error : function()
        {
            alert('There was a problem sorting your table...');
        }
    });
});

where resp is the variable that contains your Ajax response.

Note: This is just a very simple and crude (oh, and untested) way to handle the situation. You would need to improve it your self to prevent any security related issues such as SQL Injection.